LosaDev/ls-2014-source
1
0 Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
ls-2014-source/database/10_features_from_2021.sql
T

5808 lines
182 KiB
Transact-SQL
Raw Permalink Blame History

-- Generated. Real tables and procedures from the 2021 release for features that were stubs in the 2014 testbed.
USE [LosaGame]
GO
-- Columns the 2021 tables have and the 2012 tables lack.
ALTER TABLE [dbo].[userItemExtraDB] ADD [item1_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item1_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item2_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item2_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item3_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item3_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item4_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item4_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item5_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item5_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item6_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item6_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item7_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item7_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item8_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item8_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item9_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item9_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userItemExtraDB] ADD [item10_failexp] [smallint] NOT NULL CONSTRAINT [DF_ls14_userItemExtraDB_item10_failexp] DEFAULT 0
GO
ALTER TABLE [dbo].[userMemberDB] ADD [trackingcode] [int] NOT NULL CONSTRAINT [DF_ls14_userMemberDB_trackingcode] DEFAULT 0
GO
ALTER TABLE [dbo].[userMemberDB] ADD [makeType] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userMemberDB_makeType] DEFAULT 0
GO
ALTER TABLE [dbo].[userGuildInfoDB] ADD [AttendDate] [datetime] NOT NULL CONSTRAINT [DF_ls14_userGuildInfoDB_AttendDate] DEFAULT GETDATE()
GO
ALTER TABLE [dbo].[userGuildInfoDB] ADD [GuildRankDate] [datetime] NOT NULL CONSTRAINT [DF_ls14_userGuildInfoDB_GuildRankDate] DEFAULT GETDATE()
GO
ALTER TABLE [dbo].[userFriendDB] ADD [sendCloverCnt] [int] NOT NULL CONSTRAINT [DF_ls14_userFriendDB_sendCloverCnt] DEFAULT 0
GO
ALTER TABLE [dbo].[userFriendDB] ADD [sendCloverDate] [int] NOT NULL CONSTRAINT [DF_ls14_userFriendDB_sendCloverDate] DEFAULT 0
GO
ALTER TABLE [dbo].[userFriendDB] ADD [receiveCloverCnt] [int] NOT NULL CONSTRAINT [DF_ls14_userFriendDB_receiveCloverCnt] DEFAULT 0
GO
ALTER TABLE [dbo].[userFriendDB] ADD [receiveCloverDate] [int] NOT NULL CONSTRAINT [DF_ls14_userFriendDB_receiveCloverDate] DEFAULT 0
GO
ALTER TABLE [dbo].[userFriendDB] ADD [receiveBCloverCnt] [int] NOT NULL CONSTRAINT [DF_ls14_userFriendDB_receiveBCloverCnt] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class101_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class101_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class102_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class102_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class103_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class103_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class104_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class104_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class105_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class105_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class106_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class106_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class107_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class107_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class108_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class108_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class109_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class109_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class110_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class110_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class111_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class111_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class112_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class112_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class113_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class113_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class114_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class114_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class115_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class115_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class116_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class116_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class117_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class117_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class118_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class118_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class119_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class119_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class120_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class120_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class121_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class121_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class122_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class122_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class123_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class123_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class124_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class124_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class125_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class125_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class126_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class126_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class127_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class127_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class128_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class128_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class129_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class129_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class130_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class130_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class131_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class131_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class132_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class132_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class133_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class133_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class134_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class134_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class135_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class135_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class136_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class136_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class137_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class137_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class138_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class138_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class139_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class139_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class140_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class140_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class141_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class141_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class142_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class142_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class143_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class143_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class144_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class144_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class145_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class145_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class146_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class146_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class147_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class147_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class148_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class148_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class149_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class149_price] DEFAULT 0
GO
ALTER TABLE [dbo].[define_class_price] ADD [class150_price] [int] NOT NULL CONSTRAINT [DF_ls14_define_class_price_class150_price] DEFAULT 0
GO
ALTER TABLE [dbo].[event_chuchun] ADD [userType] [tinyint] NOT NULL CONSTRAINT [DF_ls14_event_chuchun_userType] DEFAULT 0
GO
ALTER TABLE [dbo].[event_chuchun] ADD [itemFlag] [tinyint] NOT NULL CONSTRAINT [DF_ls14_event_chuchun_itemFlag] DEFAULT 0
GO
ALTER TABLE [dbo].[event_chuchun] ADD [cpID] [nvarchar](32) NOT NULL CONSTRAINT [DF_ls14_event_chuchun_cpID] DEFAULT ''
GO
ALTER TABLE [dbo].[event_chuchun] ADD [cpType] [int] NOT NULL CONSTRAINT [DF_ls14_event_chuchun_cpType] DEFAULT 0
GO
ALTER TABLE [dbo].[userCharacterDB] ADD [awakeType] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userCharacterDB_awakeType] DEFAULT 0
GO
ALTER TABLE [dbo].[userCharacterDB] ADD [awakeTime] [int] NOT NULL CONSTRAINT [DF_ls14_userCharacterDB_awakeTime] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class1_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class1_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class2_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class2_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class3_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class3_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class4_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class4_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class5_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class5_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class6_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class6_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class7_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class7_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class8_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class8_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class9_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class9_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userClassDB] ADD [class10_stat] [tinyint] NOT NULL CONSTRAINT [DF_ls14_userClassDB_class10_stat] DEFAULT 0
GO
ALTER TABLE [dbo].[userGameDB] ADD [Practice] [smallint] NOT NULL CONSTRAINT [DF_ls14_userGameDB_Practice] DEFAULT 0
GO
ALTER TABLE [dbo].[userGameDB] ADD [ladderpoint] [int] NOT NULL CONSTRAINT [DF_ls14_userGameDB_ladderpoint] DEFAULT 0
GO
ALTER TABLE [dbo].[userNameDB] ADD [virtualJumin] [varchar](13) NOT NULL CONSTRAINT [DF_ls14_userNameDB_virtualJumin] DEFAULT ''
GO
ALTER TABLE [dbo].[userNameDB] ADD [ipinCI] [varchar](88) NOT NULL CONSTRAINT [DF_ls14_userNameDB_ipinCI] DEFAULT ''
GO
ALTER TABLE [dbo].[userNameDB] ADD [ipinDI] [varchar](64) NOT NULL CONSTRAINT [DF_ls14_userNameDB_ipinDI] DEFAULT ''
GO
ALTER TABLE [dbo].[userNameDB] ADD [certType] [char](1) NOT NULL CONSTRAINT [DF_ls14_userNameDB_certType] DEFAULT ''
GO
ALTER TABLE [dbo].[userPresentDB] ADD [TranIDX] [bigint] NOT NULL CONSTRAINT [DF_ls14_userPresentDB_TranIDX] DEFAULT 0
GO
CREATE TABLE [dbo].[define_hero_price](
[HeroCode] [int] NOT NULL,
[HeroPrice] [int] NOT NULL,
CONSTRAINT [PK_define_hero_price] PRIMARY KEY CLUSTERED
(
[HeroCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[define_hero_price] ADD DEFAULT ((0)) FOR [HeroCode]
GO
ALTER TABLE [dbo].[define_hero_price] ADD DEFAULT ((0)) FOR [HeroPrice]
GO
CREATE TABLE [dbo].[userPieceDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[piece1_type] [int] NOT NULL,
[piece1_value1] [int] NOT NULL,
[piece1_value2] [int] NOT NULL,
[piece1_cnt] [int] NOT NULL,
[piece2_type] [int] NOT NULL,
[piece2_value1] [int] NOT NULL,
[piece2_value2] [int] NOT NULL,
[piece2_cnt] [int] NOT NULL,
[piece3_type] [int] NOT NULL,
[piece3_value1] [int] NOT NULL,
[piece3_value2] [int] NOT NULL,
[piece3_cnt] [int] NOT NULL,
[piece4_type] [int] NOT NULL,
[piece4_value1] [int] NOT NULL,
[piece4_value2] [int] NOT NULL,
[piece4_cnt] [int] NOT NULL,
[piece5_type] [int] NOT NULL,
[piece5_value1] [int] NOT NULL,
[piece5_value2] [int] NOT NULL,
[piece5_cnt] [int] NOT NULL,
[piece6_type] [int] NOT NULL,
[piece6_value1] [int] NOT NULL,
[piece6_value2] [int] NOT NULL,
[piece6_cnt] [int] NOT NULL,
[piece7_type] [int] NOT NULL,
[piece7_value1] [int] NOT NULL,
[piece7_value2] [int] NOT NULL,
[piece7_cnt] [int] NOT NULL,
[piece8_type] [int] NOT NULL,
[piece8_value1] [int] NOT NULL,
[piece8_value2] [int] NOT NULL,
[piece8_cnt] [int] NOT NULL,
[piece9_type] [int] NOT NULL,
[piece9_value1] [int] NOT NULL,
[piece9_value2] [int] NOT NULL,
[piece9_cnt] [int] NOT NULL,
[piece10_type] [int] NOT NULL,
[piece10_value1] [int] NOT NULL,
[piece10_value2] [int] NOT NULL,
[piece10_cnt] [int] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userPieceDB] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece1_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece1_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece1_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece1_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece2_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece2_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece2_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece2_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece3_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece3_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece3_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece3_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece4_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece4_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece4_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece4_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece5_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece5_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece5_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece5_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece6_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece6_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece6_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece6_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece7_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece7_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece7_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece7_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece8_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece8_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece8_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece8_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece9_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece9_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece9_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece9_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece10_type]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece10_value1]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece10_value2]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT ((0)) FOR [piece10_cnt]
GO
ALTER TABLE [dbo].[userPieceDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userSBoxDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[presentType] [smallint] NOT NULL,
[value1] [int] NOT NULL,
[value2] [int] NOT NULL,
[amount] [int] NOT NULL,
[bonusAmount] [int] NOT NULL,
[flag] [smallint] NOT NULL,
[billingID] [varchar](64) NOT NULL,
[limitDate] [datetime] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_presentType] DEFAULT ((0)) FOR [presentType]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_value1] DEFAULT ((0)) FOR [value1]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_value2] DEFAULT ((0)) FOR [value2]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_amount] DEFAULT ((0)) FOR [amount]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_bonusAmount] DEFAULT ((0)) FOR [bonusAmount]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_flag] DEFAULT ((0)) FOR [flag]
GO
ALTER TABLE [dbo].[userSBoxDB] ADD CONSTRAINT [DF_userSBoxDB_regDate] DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userPetDB](
[accountIDX] [int] NOT NULL,
[petIDX] [int] IDENTITY(1,1) NOT NULL,
[petCode] [int] NOT NULL,
[petRank] [tinyint] NOT NULL,
[petLevel] [int] NOT NULL,
[petExp] [int] NOT NULL,
[petEquip] [tinyint] NOT NULL,
[petStatus] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userPetDB_accountIDX_PetIDX] PRIMARY KEY CLUSTERED
(
[accountIDX] ASC,
[petIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT ((0)) FOR [petCode]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT ((0)) FOR [petRank]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT ((0)) FOR [petLevel]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT ((0)) FOR [petExp]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT ((0)) FOR [petEquip]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT ((1)) FOR [petStatus]
GO
ALTER TABLE [dbo].[userPetDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[itemCode](
[idx] [int] IDENTITY(1,1) NOT NULL,
[itemType] [float] NULL,
[itemCode] [float] NULL,
[itemValue] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userCostumeDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[itemCode] [int] NOT NULL,
[mcustom] [int] NOT NULL,
[fcustom] [int] NOT NULL,
[periodType] [tinyint] NOT NULL,
[periodDate] [datetime] NOT NULL,
[classType] [int] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userCostumeDB] PRIMARY KEY CLUSTERED
(
[idx] ASC,
[accountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT ((0)) FOR [itemCode]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT ((0)) FOR [mcustom]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT ((0)) FOR [fcustom]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT ((0)) FOR [periodType]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT ('1999-01-01') FOR [periodDate]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT ((0)) FOR [classType]
GO
ALTER TABLE [dbo].[userCostumeDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userMissionDB](
[AccountIDX] [int] NOT NULL,
[MissionType] [tinyint] NOT NULL,
[MissionCode] [int] NOT NULL,
[MissionValue] [int] NOT NULL,
[MissionStatus] [tinyint] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userMissionDB_AccountIDX] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[MissionType] ASC,
[MissionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userMissionDB] ADD DEFAULT ((1)) FOR [MissionStatus]
GO
ALTER TABLE [dbo].[userMissionDB] ADD DEFAULT (getdate()) FOR [RegDate]
GO
CREATE TABLE [dbo].[event_item_limited](
[AccountIDX] [int] NOT NULL,
[EventType] [tinyint] NOT NULL,
[GoodsIndex] [int] NOT NULL,
[GoodsCount] [tinyint] NOT NULL,
CONSTRAINT [PK_event_item_limited_AccountIDX] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[EventType] ASC,
[GoodsIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userAttendDB](
[AccountIDX] [int] NOT NULL,
[AttendCount] [int] NOT NULL,
[RewardTable] [int] NOT NULL,
[ConnectDate] [datetime] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_GAME_LOG_LogIndex] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userGuildAttendDB](
[InitDate] [datetime] NOT NULL,
[AccountIDX] [int] NOT NULL,
[GuildIDX] [int] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userGuildAttendDB_RegDate_AccountIDX] PRIMARY KEY NONCLUSTERED
(
[InitDate] ASC,
[AccountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userGuildHQDB](
[GuildIDX] [int] NOT NULL,
[ItemSerial] [bigint] IDENTITY(1,1) NOT NULL,
[ItemCode] [int] NOT NULL,
[ItemXZ] [int] NOT NULL,
[ItemY] [int] NOT NULL,
[ItemRotate] [tinyint] NOT NULL,
[ItemDecoScore] [int] NOT NULL,
[ItemStatus] [tinyint] NOT NULL,
CONSTRAINT [PK_userGuildHQDB] PRIMARY KEY CLUSTERED
(
[GuildIDX] ASC,
[ItemSerial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userGuildHQDB] ADD DEFAULT ((0)) FOR [ItemXZ]
GO
ALTER TABLE [dbo].[userGuildHQDB] ADD DEFAULT ((0)) FOR [ItemY]
GO
ALTER TABLE [dbo].[userGuildHQDB] ADD DEFAULT ((0)) FOR [ItemRotate]
GO
ALTER TABLE [dbo].[userGuildHQDB] ADD DEFAULT ((0)) FOR [ItemDecoScore]
GO
ALTER TABLE [dbo].[userGuildHQDB] ADD DEFAULT ((0)) FOR [ItemStatus]
GO
CREATE TABLE [dbo].[userGuildHQVerDB](
[GuildIDX] [int] NOT NULL,
[VerStatus] [bigint] NOT NULL,
[UptDate] [datetime] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userGuildHQVerDB] PRIMARY KEY CLUSTERED
(
[GuildIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userGuildHQVerDB] ADD DEFAULT ((0)) FOR [VerStatus]
GO
ALTER TABLE [dbo].[userGuildHQVerDB] ADD DEFAULT ((0)) FOR [UptDate]
GO
ALTER TABLE [dbo].[userGuildHQVerDB] ADD DEFAULT (getdate()) FOR [RegDate]
GO
CREATE TABLE [dbo].[userPersonalHQDB](
[AccountIDX] [int] NOT NULL,
[ItemSerial] [bigint] IDENTITY(1,1) NOT NULL,
[ItemCode] [int] NOT NULL,
[ItemXZ] [int] NOT NULL,
[ItemY] [int] NOT NULL,
[ItemRotate] [tinyint] NOT NULL,
[ItemDecoScore] [int] NOT NULL,
[ItemStatus] [tinyint] NOT NULL,
CONSTRAINT [PK_userPersonalHQDB] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[ItemSerial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userPersonalHQDB] ADD DEFAULT ((0)) FOR [ItemXZ]
GO
ALTER TABLE [dbo].[userPersonalHQDB] ADD DEFAULT ((0)) FOR [ItemY]
GO
ALTER TABLE [dbo].[userPersonalHQDB] ADD DEFAULT ((0)) FOR [ItemRotate]
GO
ALTER TABLE [dbo].[userPersonalHQDB] ADD DEFAULT ((0)) FOR [ItemDecoScore]
GO
ALTER TABLE [dbo].[userPersonalHQDB] ADD DEFAULT ((0)) FOR [ItemStatus]
GO
CREATE TABLE [dbo].[userTitleDB](
[AccountIDX] [int] NOT NULL,
[TitleCode] [int] NOT NULL,
[TitleValue] [bigint] NOT NULL,
[TitleLevel] [int] NOT NULL,
[TitlePremium] [tinyint] NOT NULL,
[TitleEquip] [tinyint] NOT NULL,
[TitleStatus] [tinyint] NOT NULL,
[UpdDate] [datetime] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userTitleDB_AccountIDX] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[TitleCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userTitleDB] ADD DEFAULT ((1)) FOR [TitleLevel]
GO
ALTER TABLE [dbo].[userTitleDB] ADD DEFAULT ((0)) FOR [TitlePremium]
GO
ALTER TABLE [dbo].[userTitleDB] ADD DEFAULT ((0)) FOR [TitleEquip]
GO
ALTER TABLE [dbo].[userTitleDB] ADD DEFAULT ((0)) FOR [TitleStatus]
GO
ALTER TABLE [dbo].[userTitleDB] ADD DEFAULT ((0)) FOR [UpdDate]
GO
ALTER TABLE [dbo].[userTitleDB] ADD DEFAULT (getdate()) FOR [RegDate]
GO
CREATE TABLE [dbo].[userTimeCashDB](
[AccountIDX] [int] NOT NULL,
[ItemCode] [int] NOT NULL,
[ItemCount] [int] NOT NULL,
[ItemStatus] [tinyint] NOT NULL,
[RegDate] [datetime] NOT NULL,
[StartDate] [datetime] NOT NULL,
[OverDate] [datetime] NOT NULL,
[UpdDate] [datetime] NOT NULL,
CONSTRAINT [PK_userTimeCashDB_AccountIDX] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[ItemCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userTimeCashDB] ADD DEFAULT ((0)) FOR [ItemCount]
GO
ALTER TABLE [dbo].[userTimeCashDB] ADD DEFAULT ((1)) FOR [ItemStatus]
GO
CREATE TABLE [dbo].[userGoldDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[AccountIDX] [int] NOT NULL,
[OriginalAmount] [int] NOT NULL,
[Amount] [int] NOT NULL,
[GoldType] [tinyint] NOT NULL,
[GoldStatus] [tinyint] NOT NULL,
[ExpirationDate] [datetime] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userGoldDB] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[GoldType] ASC,
[ExpirationDate] ASC,
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userGoldDB] ADD DEFAULT ((0)) FOR [OriginalAmount]
GO
ALTER TABLE [dbo].[userGoldDB] ADD DEFAULT ((0)) FOR [Amount]
GO
ALTER TABLE [dbo].[userGoldDB] ADD DEFAULT ((1)) FOR [GoldType]
GO
ALTER TABLE [dbo].[userGoldDB] ADD DEFAULT ((0)) FOR [GoldStatus]
GO
ALTER TABLE [dbo].[userGoldDB] ADD DEFAULT (getdate()) FOR [ExpirationDate]
GO
ALTER TABLE [dbo].[userGoldDB] ADD DEFAULT (getdate()) FOR [RegDate]
GO
CREATE TABLE [dbo].[userLeagueListDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[startDate] [int] NOT NULL,
[endDate] [int] NOT NULL,
[leagueType] [tinyint] NOT NULL,
[leagueState] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userLeagueListDB] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueListDB] ADD DEFAULT ((0)) FOR [leagueType]
GO
ALTER TABLE [dbo].[userLeagueListDB] ADD DEFAULT ((0)) FOR [leagueState]
GO
ALTER TABLE [dbo].[userLeagueListDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueTeamDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[leagueIDX] [int] NOT NULL,
[teamName] [nvarchar](20) NOT NULL,
[leaderIDX] [int] NOT NULL,
[leagueStartPosition] [smallint] NOT NULL,
[leaguePosition] [smallint] NOT NULL,
[maxcount] [tinyint] NOT NULL,
[cheerPoint] [int] NOT NULL,
[currentRound] [tinyint] NOT NULL,
[factionPoint] [int] NOT NULL,
[factionType] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userLeagueTeamDB] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF_userLeagueTeamDB_leagueStartPosition] DEFAULT ((0)) FOR [leagueStartPosition]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__leagu__16E9CBE7] DEFAULT ((0)) FOR [leaguePosition]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__maxco__17DDF020] DEFAULT ((0)) FOR [maxcount]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__cheer__18D21459] DEFAULT ((0)) FOR [cheerPoint]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__curre__19C63892] DEFAULT ((0)) FOR [currentRound]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__facti__1ABA5CCB] DEFAULT ((0)) FOR [factionPoint]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__facti__1BAE8104] DEFAULT ((0)) FOR [factionType]
GO
ALTER TABLE [dbo].[userLeagueTeamDB] ADD CONSTRAINT [DF__userLeagu__regDa__1CA2A53D] DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueTeamMemberDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[leagueIDX] [int] NOT NULL,
[teamIDX] [int] NOT NULL,
[joinType] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userLeagueTeamMemberDB] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueTeamMemberDB] ADD DEFAULT ((0)) FOR [leagueIDX]
GO
ALTER TABLE [dbo].[userLeagueTeamMemberDB] ADD DEFAULT ((0)) FOR [teamIDX]
GO
ALTER TABLE [dbo].[userLeagueTeamMemberDB] ADD DEFAULT ((0)) FOR [joinType]
GO
ALTER TABLE [dbo].[userLeagueTeamMemberDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueCheerDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[leagueIDX] [int] NOT NULL,
[teamIDX] [int] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userLeagueCheerDB] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userLeagueWinnerHistoryDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[leagueTitle] [nvarchar](20) NOT NULL,
[startDate] [int] NOT NULL,
[endDate] [int] NOT NULL,
[teamIDX] [int] NOT NULL,
[teamName] [nvarchar](20) NULL,
[factionName] [nvarchar](20) NULL,
[factionType] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueWinnerHistoryDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueRewardCheerDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[rewardPeso] [int] NOT NULL,
[startDate] [int] NOT NULL,
[flag] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueRewardCheerDB] ADD DEFAULT ((0)) FOR [flag]
GO
CREATE TABLE [dbo].[userLeagueRewardDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[startDate] [int] NOT NULL,
[currentRound] [tinyint] NOT NULL,
[userFactionType] [int] NOT NULL,
[winnerFactionType] [int] NOT NULL,
[rewardPeso] [int] NOT NULL,
[factionRank] [int] NOT NULL,
[factionPoint] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF__userLeagu__curre__0BCD1EF6] DEFAULT ((0)) FOR [currentRound]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF__userLeagu__userF__0CC1432F] DEFAULT ((0)) FOR [userFactionType]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF__userLeagu__winne__0DB56768] DEFAULT ((0)) FOR [winnerFactionType]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF__userLeagu__rewar__0EA98BA1] DEFAULT ((0)) FOR [rewardPeso]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF_userLeagueRewardDB_factionRank] DEFAULT ((0)) FOR [factionRank]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF_userLeagueRewardDB_factionPoint] DEFAULT ((0)) FOR [factionPoint]
GO
ALTER TABLE [dbo].[userLeagueRewardDB] ADD CONSTRAINT [DF__userLeagu__regDa__0F9DAFDA] DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueWinnerInfoDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[nickName] [nvarchar](20) NOT NULL,
[userLevel] [int] NOT NULL,
[historyIDX] [int] NOT NULL,
[teamIDX] [int] NOT NULL,
[classType] [int] NOT NULL,
[classGender] [int] NOT NULL,
[classFace] [int] NOT NULL,
[classHair] [int] NOT NULL,
[classSkinColor] [int] NOT NULL,
[classHairColor] [int] NOT NULL,
[classUnderwear] [int] NOT NULL,
[classGear1] [int] NOT NULL,
[classGear2] [int] NOT NULL,
[classGear3] [int] NOT NULL,
[classGear4] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [historyIDX]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [teamIDX]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classType]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classGender]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classFace]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classHair]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classSkinColor]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classHairColor]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classUnderwear]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classGear1]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classGear2]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classGear3]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT ((0)) FOR [classGear4]
GO
ALTER TABLE [dbo].[userLeagueWinnerInfoDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueInfoDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[leagueIDX] [int] NOT NULL,
[leagueName] [nvarchar](20) NOT NULL,
[maxRound] [smallint] NOT NULL,
[bannerA] [int] NOT NULL,
[bannerB] [int] NOT NULL,
[modeType] [int] NOT NULL,
[maxcount] [tinyint] NOT NULL,
[autoType] [tinyint] NOT NULL,
[announce] [nvarchar](512) NOT NULL,
[recruitDate] [int] NOT NULL,
[waitDate] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [leagueIDX]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [bannerA]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [bannerB]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [modeType]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [maxcount]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [autoType]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [recruitDate]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT ((0)) FOR [waitDate]
GO
ALTER TABLE [dbo].[userLeagueInfoDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueRoundDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[infoIDX] [int] NOT NULL,
[roundType1_playDate] [int] NOT NULL,
[roundType1_presentCode1] [int] NOT NULL,
[roundType1_presentCode2] [int] NOT NULL,
[roundType1_presentCode3] [int] NOT NULL,
[roundType1_presentCode4] [int] NOT NULL,
[roundType2_playDate] [int] NOT NULL,
[roundType2_presentCode1] [int] NOT NULL,
[roundType2_presentCode2] [int] NOT NULL,
[roundType2_presentCode3] [int] NOT NULL,
[roundType2_presentCode4] [int] NOT NULL,
[roundType3_playDate] [int] NOT NULL,
[roundType3_presentCode1] [int] NOT NULL,
[roundType3_presentCode2] [int] NOT NULL,
[roundType3_presentCode3] [int] NOT NULL,
[roundType3_presentCode4] [int] NOT NULL,
[roundType4_playDate] [int] NOT NULL,
[roundType4_presentCode1] [int] NOT NULL,
[roundType4_presentCode2] [int] NOT NULL,
[roundType4_presentCode3] [int] NOT NULL,
[roundType4_presentCode4] [int] NOT NULL,
[roundType5_playDate] [int] NOT NULL,
[roundType5_presentCode1] [int] NOT NULL,
[roundType5_presentCode2] [int] NOT NULL,
[roundType5_presentCode3] [int] NOT NULL,
[roundType5_presentCode4] [int] NOT NULL,
[roundType6_playDate] [int] NOT NULL,
[roundType6_presentCode1] [int] NOT NULL,
[roundType6_presentCode2] [int] NOT NULL,
[roundType6_presentCode3] [int] NOT NULL,
[roundType6_presentCode4] [int] NOT NULL,
[roundType7_playDate] [int] NOT NULL,
[roundType7_presentCode1] [int] NOT NULL,
[roundType7_presentCode2] [int] NOT NULL,
[roundType7_presentCode3] [int] NOT NULL,
[roundType7_presentCode4] [int] NOT NULL,
[roundType8_playDate] [int] NOT NULL,
[roundType8_presentCode1] [int] NOT NULL,
[roundType8_presentCode2] [int] NOT NULL,
[roundType8_presentCode3] [int] NOT NULL,
[roundType8_presentCode4] [int] NOT NULL,
[roundType9_playDate] [int] NOT NULL,
[roundType9_presentCode1] [int] NOT NULL,
[roundType9_presentCode2] [int] NOT NULL,
[roundType9_presentCode3] [int] NOT NULL,
[roundType9_presentCode4] [int] NOT NULL,
[roundType10_playDate] [int] NOT NULL,
[roundType10_presentCode1] [int] NOT NULL,
[roundType10_presentCode2] [int] NOT NULL,
[roundType10_presentCode3] [int] NOT NULL,
[roundType10_presentCode4] [int] NOT NULL,
[roundType11_playDate] [int] NOT NULL,
[roundType11_presentCode1] [int] NOT NULL,
[roundType11_presentCode2] [int] NOT NULL,
[roundType11_presentCode3] [int] NOT NULL,
[roundType11_presentCode4] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [infoIDX]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType1_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType1_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType1_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType1_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType1_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType2_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType2_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType2_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType2_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType2_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType3_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType3_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType3_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType3_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType3_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType4_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType4_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType4_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType4_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType4_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType5_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType5_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType5_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType5_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType5_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType6_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType6_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType6_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType6_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType6_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType7_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType7_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType7_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType7_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType7_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType8_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType8_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType8_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType8_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType8_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType9_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType9_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType9_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType9_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType9_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType10_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType10_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType10_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType10_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType10_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType11_playDate]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType11_presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType11_presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType11_presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT ((0)) FOR [roundType11_presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRoundDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueAuthDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[leagueIDX] [int] NOT NULL,
[accountIDX] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueAuthDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userCloverDB](
[accountIDX] [int] NOT NULL,
[CloverCnt] [int] NOT NULL,
[LastChargeDate] [int] NOT NULL,
[RemainTime] [smallint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userCloverDB] PRIMARY KEY CLUSTERED
(
[accountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userCloverDB] ADD DEFAULT ((0)) FOR [CloverCnt]
GO
ALTER TABLE [dbo].[userCloverDB] ADD DEFAULT ((0)) FOR [LastChargeDate]
GO
ALTER TABLE [dbo].[userCloverDB] ADD DEFAULT ((0)) FOR [RemainTime]
GO
ALTER TABLE [dbo].[userCloverDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userLeagueRewardUserDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[leagueIDX] [int] NOT NULL,
[sendNickname] [nvarchar](20) NOT NULL,
[leagueName] [nvarchar](20) NOT NULL,
[roundnum] [int] NOT NULL,
[maxRound] [smallint] NOT NULL,
[presentCode1] [int] NOT NULL,
[presentCode2] [int] NOT NULL,
[presentCode3] [int] NOT NULL,
[presentCode4] [int] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [leagueIDX]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [roundnum]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [maxRound]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [presentCode1]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [presentCode2]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [presentCode3]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT ((0)) FOR [presentCode4]
GO
ALTER TABLE [dbo].[userLeagueRewardUserDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userEventBingo_Number](
[accountidx] [int] NOT NULL,
[number1] [tinyint] NOT NULL,
[number2] [tinyint] NOT NULL,
[number3] [tinyint] NOT NULL,
[number4] [tinyint] NOT NULL,
[number5] [tinyint] NOT NULL,
[number6] [tinyint] NOT NULL,
[number7] [tinyint] NOT NULL,
[number8] [tinyint] NOT NULL,
[number9] [tinyint] NOT NULL,
[number10] [tinyint] NOT NULL,
[number11] [tinyint] NOT NULL,
[number12] [tinyint] NOT NULL,
[number13] [tinyint] NOT NULL,
[number14] [tinyint] NOT NULL,
[number15] [tinyint] NOT NULL,
[number16] [tinyint] NOT NULL,
[number17] [tinyint] NOT NULL,
[number18] [tinyint] NOT NULL,
[number19] [tinyint] NOT NULL,
[number20] [tinyint] NOT NULL,
[number21] [tinyint] NOT NULL,
[number22] [tinyint] NOT NULL,
[number23] [tinyint] NOT NULL,
[number24] [tinyint] NOT NULL,
[number25] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userEventBingo_Number] PRIMARY KEY CLUSTERED
(
[accountidx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userEventBingo_Present](
[accountidx] [int] NOT NULL,
[item1] [tinyint] NOT NULL,
[item2] [tinyint] NOT NULL,
[item3] [tinyint] NOT NULL,
[item4] [tinyint] NOT NULL,
[item5] [tinyint] NOT NULL,
[item6] [tinyint] NOT NULL,
[item7] [tinyint] NOT NULL,
[item8] [tinyint] NOT NULL,
[item9] [tinyint] NOT NULL,
[item10] [tinyint] NOT NULL,
[item11] [tinyint] NOT NULL,
[item12] [tinyint] NOT NULL,
[item13] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userEventBingo_Present] PRIMARY KEY CLUSTERED
(
[accountidx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userRelativeLevelDB](
[accountIDX] [int] NOT NULL,
[weekend_BackupLevel] [int] NOT NULL,
[weekend_BackupExp] [int] NOT NULL,
[init_time] [int] NOT NULL,
[reward_State] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userRelativeLevelDB] PRIMARY KEY CLUSTERED
(
[accountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userRelativeLevelDB] ADD CONSTRAINT [DF_userRelativeLevelDB_weekend_Exp] DEFAULT ((0)) FOR [weekend_BackupLevel]
GO
ALTER TABLE [dbo].[userRelativeLevelDB] ADD CONSTRAINT [DF_userRelativeLevelDB_weekend_Backup] DEFAULT ((0)) FOR [weekend_BackupExp]
GO
ALTER TABLE [dbo].[userRelativeLevelDB] ADD CONSTRAINT [DF_userRelativeLevelDB_init_time] DEFAULT ((0)) FOR [init_time]
GO
ALTER TABLE [dbo].[userRelativeLevelDB] ADD CONSTRAINT [DF_userRelativeLevelDB_reward_State] DEFAULT ((0)) FOR [reward_State]
GO
CREATE TABLE [dbo].[userAttendanceDB](
[idx] [int] IDENTITY(1,1) NOT NULL,
[accountIDX] [int] NOT NULL,
[checkDate] [datetime] NOT NULL,
[regDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userAttendanceDB] ADD DEFAULT (getdate()) FOR [regDate]
GO
CREATE TABLE [dbo].[userSpentGoldDB](
[AccountIDX] [int] NOT NULL,
[UserSpentGold] [int] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userSpentGoldDB_AccountIDX] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userSpentGoldDB] ADD DEFAULT (getdate()) FOR [RegDate]
GO
CREATE TABLE [dbo].[userSpentGoldLogDB](
[RegDate] [datetime] NOT NULL,
[AccountIDX] [int] NOT NULL,
[UserSpentGold] [int] NOT NULL,
CONSTRAINT [PK_userSpentGoldLogDB_RegDate_AccountIDX] PRIMARY KEY CLUSTERED
(
[RegDate] ASC,
[AccountIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[userPopStoreDB](
[AccountIDX] [int] NOT NULL,
[PopupSlot] [int] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_userPopStoreDB_AccountIDX] PRIMARY KEY CLUSTERED
(
[AccountIDX] ASC,
[PopupSlot] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userPopStoreDB] ADD DEFAULT (getdate()) FOR [RegDate]
GO
CREATE TABLE [dbo].[userEventPirateRoulette_Number](
[accountidx] [int] NOT NULL,
[HP] [int] NOT NULL,
[slot1] [tinyint] NOT NULL,
[slot2] [tinyint] NOT NULL,
[slot3] [tinyint] NOT NULL,
[slot4] [tinyint] NOT NULL,
[slot5] [tinyint] NOT NULL,
[slot6] [tinyint] NOT NULL,
[slot7] [tinyint] NOT NULL,
[slot8] [tinyint] NOT NULL,
[slot9] [tinyint] NOT NULL,
[slot10] [tinyint] NOT NULL,
[slot11] [tinyint] NOT NULL,
[slot12] [tinyint] NOT NULL,
[slot13] [tinyint] NOT NULL,
[slot14] [tinyint] NOT NULL,
[slot15] [tinyint] NOT NULL,
[slot16] [tinyint] NOT NULL,
[slot17] [tinyint] NOT NULL,
[slot18] [tinyint] NOT NULL,
[slot19] [tinyint] NOT NULL,
[slot20] [tinyint] NOT NULL,
[slot21] [tinyint] NOT NULL,
[slot22] [tinyint] NOT NULL,
[slot23] [tinyint] NOT NULL,
[slot24] [tinyint] NOT NULL,
[slot25] [tinyint] NOT NULL,
[slot26] [tinyint] NOT NULL,
[slot27] [tinyint] NOT NULL,
[slot28] [tinyint] NOT NULL,
[slot29] [tinyint] NOT NULL,
[slot30] [tinyint] NOT NULL,
[slot31] [tinyint] NOT NULL,
[slot32] [tinyint] NOT NULL,
[slot33] [tinyint] NOT NULL,
[slot34] [tinyint] NOT NULL,
[slot35] [tinyint] NOT NULL,
[slot36] [tinyint] NOT NULL,
[slot37] [tinyint] NOT NULL,
[slot38] [tinyint] NOT NULL,
[slot39] [tinyint] NOT NULL,
[slot40] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userEventPirateRoulette_Number] PRIMARY KEY CLUSTERED
(
[accountidx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot1]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot2]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot3]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot4]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot5]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot6]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot7]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot8]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot9]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot10]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot11]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot12]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot13]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot14]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot15]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot16]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot17]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot18]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot19]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot20]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot21]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot22]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot23]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot24]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot25]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot26]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot27]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot28]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot29]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot30]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot31]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot32]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot33]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot34]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot35]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot36]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot37]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot38]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot39]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Number] ADD DEFAULT ((0)) FOR [slot40]
GO
CREATE TABLE [dbo].[userEventPirateRoulette_Present](
[accountidx] [int] NOT NULL,
[reward1] [tinyint] NOT NULL,
[reward2] [tinyint] NOT NULL,
[reward3] [tinyint] NOT NULL,
[reward4] [tinyint] NOT NULL,
[reward5] [tinyint] NOT NULL,
[reward6] [tinyint] NOT NULL,
[reward7] [tinyint] NOT NULL,
[reward8] [tinyint] NOT NULL,
[reward9] [tinyint] NOT NULL,
[reward10] [tinyint] NOT NULL,
[reward11] [tinyint] NOT NULL,
[reward12] [tinyint] NOT NULL,
[reward13] [tinyint] NOT NULL,
[reward14] [tinyint] NOT NULL,
[reward15] [tinyint] NOT NULL,
[regDate] [datetime] NOT NULL,
CONSTRAINT [PK_userEventPirateRoulette_Present] PRIMARY KEY CLUSTERED
(
[accountidx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward1] DEFAULT ((0)) FOR [reward1]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward2] DEFAULT ((0)) FOR [reward2]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward3] DEFAULT ((0)) FOR [reward3]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward4] DEFAULT ((0)) FOR [reward4]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward5] DEFAULT ((0)) FOR [reward5]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward6] DEFAULT ((0)) FOR [reward6]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward7] DEFAULT ((0)) FOR [reward7]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward8] DEFAULT ((0)) FOR [reward8]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward9] DEFAULT ((0)) FOR [reward9]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward10] DEFAULT ((0)) FOR [reward10]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward11] DEFAULT ((0)) FOR [reward11]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward12] DEFAULT ((0)) FOR [reward12]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward13] DEFAULT ((0)) FOR [reward13]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward14] DEFAULT ((0)) FOR [reward14]
GO
ALTER TABLE [dbo].[userEventPirateRoulette_Present] ADD CONSTRAINT [DF_userEventPirateRoulette_Present_reward15] DEFAULT ((0)) FOR [reward15]
GO
IF OBJECT_ID('dbo.game_hero_price_get_data') IS NOT NULL DROP PROCEDURE dbo.game_hero_price_get_data
GO
CREATE PROCEDURE [dbo].[game_hero_price_get_data]
AS
SET NOCOUNT ON;
SET LOCK_TIMEOUT 10000;
SELECT HeroCode, HeroPrice
FROM dbo.define_hero_price WITH (NOLOCK)
ORDER BY HeroCode
GO
IF OBJECT_ID('dbo.game_get_ladderpoint') IS NOT NULL DROP PROCEDURE dbo.game_get_ladderpoint
GO
CREATE PROCEDURE [dbo].[game_get_ladderpoint]
(
@accountIDX int
)
AS
SELECT ladderpoint FROM userGameDB WHERE accountIDX=@accountIDX
GO
IF OBJECT_ID('dbo.game_piece_get_list') IS NOT NULL DROP PROCEDURE dbo.game_piece_get_list
GO
CREATE PROCEDURE [dbo].[game_piece_get_list]
(
@accountIDX int
)
AS
SET NOCOUNT ON
SELECT idx,
piece1_type, piece1_cnt, piece2_type, piece2_cnt, piece3_type, piece3_cnt, piece4_type, piece4_cnt, piece5_type, piece5_cnt,
piece6_type, piece6_cnt, piece7_type, piece7_cnt, piece8_type, piece8_cnt, piece9_type, piece9_cnt, piece10_type, piece10_cnt
FROM dbo.userPieceDB WITH(NOLOCK)
WHERE accountIDX=@accountIDX
GO
IF OBJECT_ID('dbo.game_piece_add') IS NOT NULL DROP PROCEDURE dbo.game_piece_add
GO
CREATE PROCEDURE [dbo].[game_piece_add]
(
@accountIDX int
, @piece1_type int
, @piece1_cnt int
, @piece2_type int
, @piece2_cnt int
, @piece3_type int
, @piece3_cnt int
, @piece4_type int
, @piece4_cnt int
, @piece5_type int
, @piece5_cnt int
, @piece6_type int
, @piece6_cnt int
, @piece7_type int
, @piece7_cnt int
, @piece8_type int
, @piece8_cnt int
, @piece9_type int
, @piece9_cnt int
, @piece10_type int
, @piece10_cnt int
)
AS
SET NOCOUNT ON
INSERT INTO dbo.userPieceDB (
accountIDX,
piece1_type, piece1_value1, piece1_value2, piece1_cnt, piece2_type, piece2_value1, piece2_value2, piece2_cnt,
piece3_type, piece3_value1, piece3_value2, piece3_cnt, piece4_type, piece4_value1, piece4_value2, piece4_cnt,
piece5_type, piece5_value1, piece5_value2, piece5_cnt, piece6_type, piece6_value1, piece6_value2, piece6_cnt,
piece7_type, piece7_value1, piece7_value2, piece7_cnt, piece8_type, piece8_value1, piece8_value2, piece8_cnt,
piece9_type, piece9_value1, piece9_value2, piece9_cnt, piece10_type, piece10_value1, piece10_value2, piece10_cnt,
regDate
)
VALUES (
@accountIDX,
@piece1_type, 0, 0, @piece1_cnt, @piece2_type, 0, 0, @piece2_cnt,
@piece3_type, 0, 0, @piece3_cnt, @piece4_type, 0, 0, @piece4_cnt,
@piece5_type, 0, 0, @piece5_cnt, @piece6_type, 0, 0, @piece6_cnt,
@piece7_type, 0, 0, @piece7_cnt, @piece8_type, 0, 0, @piece8_cnt,
@piece9_type, 0, 0, @piece9_cnt, @piece10_type, 0, 0, @piece10_cnt,
GETDATE()
)
GO
IF OBJECT_ID('dbo.game_piece_self_index') IS NOT NULL DROP PROCEDURE dbo.game_piece_self_index
GO
CREATE PROCEDURE [dbo].[game_piece_self_index]
(
@accountIDX int
)
AS
SET NOCOUNT ON
SELECT TOP 1 idx
FROM dbo.userPieceDB
WHERE accountIDX=@accountIDX
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_piece_save') IS NOT NULL DROP PROCEDURE dbo.game_piece_save
GO
CREATE PROCEDURE [dbo].[game_piece_save]
(
@idx int
, @piece1_type int
, @piece1_cnt int
, @piece2_type int
, @piece2_cnt int
, @piece3_type int
, @piece3_cnt int
, @piece4_type int
, @piece4_cnt int
, @piece5_type int
, @piece5_cnt int
, @piece6_type int
, @piece6_cnt int
, @piece7_type int
, @piece7_cnt int
, @piece8_type int
, @piece8_cnt int
, @piece9_type int
, @piece9_cnt int
, @piece10_type int
, @piece10_cnt int
)
AS
SET NOCOUNT ON
UPDATE dbo.userPieceDB
SET piece1_type = @piece1_type
, piece1_cnt = @piece1_cnt
, piece2_type = @piece2_type
, piece2_cnt = @piece2_cnt
, piece3_type = @piece3_type
, piece3_cnt = @piece3_cnt
, piece4_type = @piece4_type
, piece4_cnt = @piece4_cnt
, piece5_type = @piece5_type
, piece5_cnt = @piece5_cnt
, piece6_type = @piece6_type
, piece6_cnt = @piece6_cnt
, piece7_type = @piece7_type
, piece7_cnt = @piece7_cnt
, piece8_type = @piece8_type
, piece8_cnt = @piece8_cnt
, piece9_type = @piece9_type
, piece9_cnt = @piece9_cnt
, piece10_type = @piece10_type
, piece10_cnt = @piece10_cnt
WHERE idx=@idx
GO
IF OBJECT_ID('dbo.log_event_marble_add') IS NOT NULL DROP PROCEDURE dbo.log_event_marble_add
GO
CREATE PROCEDURE [dbo].[log_event_marble_add]
(
@accountidx int
, @userID varchar(20)
, @cpType int
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [LosaGame_log].[dbo].log_event_marble (accountidx, userID, cpType, regDate)
VALUES (@accountidx, @userID, @cpType, GETDATE())
END
GO
IF OBJECT_ID('dbo.game_sbox_delete') IS NOT NULL DROP PROCEDURE dbo.game_sbox_delete
GO
CREATE PROCEDURE [dbo].[game_sbox_delete]
(
@idx INT
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userSBoxDB
WHERe idx = @idx
GO
IF OBJECT_ID('dbo.game_pet_get_list') IS NOT NULL DROP PROCEDURE dbo.game_pet_get_list
GO
CREATE PROCEDURE [dbo].[game_pet_get_list]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
BEGIN
SELECT petIDX, petCode, petRank, petLevel, petExp, petEquip FROM dbo.userPetDB WITH (READUNCOMMITTED)
WHERE accountIDX=@ACCOUNT_IDX and petStatus=1
END
GO
IF OBJECT_ID('dbo.game_pet_add') IS NOT NULL DROP PROCEDURE dbo.game_pet_add
GO
CREATE PROCEDURE [dbo].[game_pet_add]
@ACCOUNT_IDX INT,
@PET_CODE INT,
@PET_RANK TINYINT,
@PET_LEVEL INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @PET_IDX INT
BEGIN TRAN
-- 펫 생성하기
INSERT dbo.userPetDB WITH (ROWLOCK, XLOCK) (accountIDX, petCode, petRank, petLevel) VALUES (@ACCOUNT_IDX, @PET_CODE, @PET_RANK, @PET_LEVEL)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
END
-- 펫 고유 키값 Return
SET @PET_IDX = @@IDENTITY
COMMIT TRAN
SELECT @PET_IDX as PET_IDX, @PET_CODE as PET_CODE, @PET_RANK as PET_RANK, @PET_LEVEL as PET_LEVEL;
GO
IF OBJECT_ID('dbo.game_pet_delete') IS NOT NULL DROP PROCEDURE dbo.game_pet_delete
GO
CREATE PROCEDURE [dbo].[game_pet_delete]
@ACCOUNT_IDX INT,
@PET_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
BEGIN TRAN
-- 펫 데이터 삭제 처리
UPDATE dbo.userPetDB WITH (ROWLOCK, XLOCK) SET petStatus=0 WHERE accountIDX=@ACCOUNT_IDX and petIDX=@PET_IDX
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN
GO
IF OBJECT_ID('dbo.game_pet_save') IS NOT NULL DROP PROCEDURE dbo.game_pet_save
GO
CREATE PROCEDURE [dbo].[game_pet_save]
@ACCOUNT_IDX INT, -- 사용자 고유키
@PET_IDX INT, -- 펫 고유키
@PET_RANK TINYINT, -- 랭크
@PET_LEVEL INT, -- 레벨
@PET_EXP INT, -- 경험치
@PET_EQUIP TINYINT -- 장착여부
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
BEGIN TRAN
-- 랭크, 레벨, 경험치, 장착 여부 업데이트 (4EA)
UPDATE dbo.userPetDB WITH(ROWLOCK, XLOCK)
SET petRank=@PET_RANK, petLevel=@PET_LEVEL, petExp=@PET_EXP, petEquip=@PET_EQUIP
WHERE accountIDX=@ACCOUNT_IDX and petIDX=@PET_IDX
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN
GO
IF OBJECT_ID('dbo.game_costume_get_list') IS NOT NULL DROP PROCEDURE dbo.game_costume_get_list
GO
CREATE PROCEDURE [dbo].[game_costume_get_list]
(
@idx INT,
@accountIDX INT,
@topNum INT
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP (@topNum) idx, itemCode, periodType, periodDate, classType
FROM dbo.userCostumeDB WITH (INDEX (PK_userCostumeDB))
WHERE accountIDX = @accountIDX
AND idx > @idx
ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_costume_add') IS NOT NULL DROP PROCEDURE dbo.game_costume_add
GO
CREATE PROCEDURE [dbo].[game_costume_add]
(
@accountIDX INT,
@itemCode INT,
@periodType TINYINT,
@periodDate DATETIME,
@classType INT
)
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @itemCodeIDX INT
SET @itemCodeIDX = 0
BEGIN TRAN
INSERT INTO dbo.userCostumeDB (
accountIDX, itemCode, mcustom, fcustom, periodType, periodDate, classType, regDate
)
VALUES (
@accountIDX, @itemCode, 0, 0, @periodType, @periodDate, @classType, GETDATE()
)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
SET @itemCodeIDX = @@IDENTITY
COMMIT TRAN
SELECT @itemCodeIDX AS idx;
GO
IF OBJECT_ID('dbo.game_costume_delete') IS NOT NULL DROP PROCEDURE dbo.game_costume_delete
GO
CREATE PROCEDURE [dbo].[game_costume_delete]
(
@idx INT,
@accountIDX INT
)
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DELETE FROM dbo.userCostumeDB
WHERE accountIDX = @accountIDX
AND idx = @idx
GO
IF OBJECT_ID('dbo.game_mission_get_data') IS NOT NULL DROP PROCEDURE dbo.game_mission_get_data
GO
-- =============================================
-- Subject : dbo.game_mission_get_data
-- DBA : KeuN
-- DATE : 2014-11-06 21:49:00
-- Description : 미션 달성 데이터 조회
-- =============================================
CREATE PROCEDURE [dbo].[game_mission_get_data]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT MissionType, MissionCode, MissionValue, MissionStatus FROM dbo.userMissionDB WITH (READUNCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX and MissionStatus<4
IF (@@ERROR<>0)
BEGIN
SELECT -1
RETURN;
END
RETURN;
GO
IF OBJECT_ID('dbo.game_mission_set_data') IS NOT NULL DROP PROCEDURE dbo.game_mission_set_data
GO
-- =============================================
-- Subject : dbo.game_mission_set_data
-- DBA : KeuN
-- DATE : 2014-11-06 21:09:00
-- Description : 미션 달성 데이터 입력
-- =============================================
CREATE PROCEDURE [dbo].[game_mission_set_data]
@ACCOUNT_IDX INT,
@MISSION_TYPE TINYINT,
@MISSION_CODE INT,
@MISSION_VALUE INT,
@MISSION_STATUS TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
SET @nReturn=0 -- True
BEGIN TRAN
-- MERGE dbo.userMissionDB as TARGET
-- USING (SELECT @ACCOUNT_IDX, @MISSION_TYPE, @MISSION_CODE, @MISSION_VALUE, @MISSION_STATUS) AS SOURCE (AccountIDX, MissionType, MissionCode, MissionValue, MissionStatus)
-- ON (TARGET.AccountIDX=SOURCE.AccountIDX and TARGET.MissionType=SOURCE.MissionType and TARGET.MissionCode=SOURCE.MissionCode)
-- WHEN MATCHED THEN
-- UPDATE SET MissionValue=SOURCE.MissionValue, MissionStatus=SOURCE.MissionStatus
-- WHEN NOT MATCHED THEN
-- INSERT (AccountIDX, MissionType, MissionCode, MissionValue, MissionStatus) VALUES (@ACCOUNT_IDX, SOURCE.MissionType, SOURCE.MissionCode, SOURCE.MissionValue, SOURCE.MissionStatus);
UPDATE dbo.userMissionDB WITH (XLOCK, ROWLOCK) SET MissionValue=@MISSION_VALUE, MissionStatus=@MISSION_STATUS
WHERE AccountIDX=@ACCOUNT_IDX and MissionType=@MISSION_TYPE and MissionCode=@MISSION_CODE
IF (@@ROWCOUNT=0)
BEGIN
INSERT dbo.userMissionDB WITH (XLOCK, ROWLOCK) (AccountIDX, MissionType, MissionCode, MissionValue, MissionStatus)
VALUES (@ACCOUNT_IDX, @MISSION_TYPE, @MISSION_CODE, @MISSION_VALUE, @MISSION_STATUS)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -1
RETURN;
END
END
COMMIT TRAN
SELECT @nReturn
RETURN;
GO
IF OBJECT_ID('dbo.game_mission_set_init') IS NOT NULL DROP PROCEDURE dbo.game_mission_set_init
GO
-- =============================================
-- Subject : dbo.game_mission_set_init
-- DBA : KeuN
-- DATE : 2014-11-06 21:49:00
-- Description : 미션 달성 데이터 초기화
-- =============================================
CREATE PROCEDURE [dbo].[game_mission_set_init]
@ACCOUNT_IDX INT,
@MISSION_TYPE TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
SET @nReturn=0 -- True
BEGIN TRAN
UPDATE dbo.userMissionDB WITH (XLOCK, ROWLOCK) SET MissionStatus=4
WHERE AccountIDX=@ACCOUNT_IDX and MissionType=@MISSION_TYPE
IF (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -1
RETURN;
END
COMMIT TRAN
SELECT @nReturn
RETURN;
GO
IF OBJECT_ID('dbo.event_item_limited_get_data') IS NOT NULL DROP PROCEDURE dbo.event_item_limited_get_data
GO
-- =============================================
-- Subject : dbo.event_item_limited_get_data
-- DBA : KeuN
-- DATE : 2014-12-12 15:07:00
-- Description : 이벤트 아이템 구매 개수 제한
-- =============================================
CREATE PROCEDURE [dbo].[event_item_limited_get_data]
@PAGE_SIZE INT, -- 페이지 당 SELECT 할 갯수
@PAGE INT -- 페이지 번호
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT AccountIDX, EventType, GoodsIndex, GoodsCount FROM (
SELECT ROW_NUMBER() OVER (ORDER BY AccountIDX) as RowNumber, AccountIDX, EventType, GoodsIndex, GoodsCount
FROM dbo.event_item_limited WITH (READUNCOMMITTED)
WHERE AccountIDX>0 and EventType=0
) as A WHERE RowNumber BETWEEN ((@PAGE - 1) * @PAGE_SIZE + 1) And (@PAGE * @PAGE_SIZE)
IF (@@ERROR<>0)
BEGIN
RETURN;
END
RETURN;
GO
IF OBJECT_ID('dbo.event_item_limited_set_data') IS NOT NULL DROP PROCEDURE dbo.event_item_limited_set_data
GO
-- =============================================
-- Subject : dbo.event_item_limited_set_data
-- DBA : KeuN
-- DATE : 2014-12-12 15:09:00
-- Description : 미션 달성 데이터 입력
-- =============================================
CREATE PROCEDURE [dbo].[event_item_limited_set_data]
@ACCOUNT_IDX INT,
@EVENT_TYPE TINYINT,
@GOODS_INDEX INT,
@GOODS_COUNT TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
SET @nReturn=0 -- True
BEGIN TRAN
-- MERGE dbo.event_item_limited as TARGET
-- USING (SELECT @ACCOUNT_IDX, @MISSION_TYPE, @MISSION_CODE, @MISSION_VALUE, @MISSION_STATUS) AS SOURCE (AccountIDX, MissionType, MissionCode, MissionValue, MissionStatus)
-- ON (TARGET.AccountIDX=SOURCE.AccountIDX and TARGET.MissionType=SOURCE.MissionType and TARGET.MissionCode=SOURCE.MissionCode)
-- WHEN MATCHED THEN
-- UPDATE SET MissionValue=SOURCE.MissionValue, MissionStatus=SOURCE.MissionStatus
-- WHEN NOT MATCHED THEN
-- INSERT (AccountIDX, MissionType, MissionCode, MissionValue, MissionStatus) VALUES (@ACCOUNT_IDX, SOURCE.MissionType, SOURCE.MissionCode, SOURCE.MissionValue, SOURCE.MissionStatus);
UPDATE dbo.event_item_limited WITH (XLOCK, ROWLOCK) SET GoodsCount=@GOODS_COUNT
WHERE AccountIDX=@ACCOUNT_IDX and EventType=@EVENT_TYPE and GoodsIndex=@GOODS_INDEX
IF (@@ROWCOUNT=0)
BEGIN
INSERT dbo.event_item_limited WITH (XLOCK, ROWLOCK) (AccountIDX, EventType, GoodsIndex, GoodsCount) VALUES (@ACCOUNT_IDX, @EVENT_TYPE, @GOODS_INDEX, @GOODS_COUNT)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -1
RETURN;
END
END
COMMIT TRAN
SELECT @nReturn
RETURN;
GO
IF OBJECT_ID('dbo.event_item_limited_set_init') IS NOT NULL DROP PROCEDURE dbo.event_item_limited_set_init
GO
-- =============================================
-- Subject : dbo.event_item_limited_set_init
-- DBA : KeuN
-- DATE : 2014-12-12 15:07:00
-- Description : 이벤트 아이템 구매 개수 제한
-- =============================================
CREATE PROCEDURE [dbo].[event_item_limited_set_init]
@EVENT_TYPE TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
IF EXISTS (SELECT * FROM dbo.event_item_limited WHERE AccountIDX>0 and EventType=@EVENT_TYPE)
BEGIN
BEGIN TRAN
DELETE dbo.event_item_limited WHERE AccountIDX>0 and EventType=@EVENT_TYPE
IF (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -1
RETURN;
END
COMMIT TRAN
END
SELECT 0
RETURN;
GO
IF OBJECT_ID('dbo.game_attend_get_data') IS NOT NULL DROP PROCEDURE dbo.game_attend_get_data
GO
-- =============================================
-- Subject : dbo.game_attend_get_data
-- DBA : KeuN
-- DATE : 2014-12-02 15:50
-- Description : 출석 데이터 조회
-- =============================================
create PROCEDURE [dbo].[game_attend_get_data]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT AttendCount, RewardTable, ConnectDate FROM dbo.userAttendDB WITH (READUNCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX
IF (@@ERROR<>0)
BEGIN
SELECT -1
RETURN;
END
RETURN;
GO
IF OBJECT_ID('dbo.game_attend_set_data') IS NOT NULL DROP PROCEDURE dbo.game_attend_set_data
GO
-- =============================================
-- Subject : dbo.game_attend_set_data
-- DBA : KeuN
-- DATE : 2014-12-02 15:09:00
-- Description : 출석 데이터 입력
-- =============================================
CREATE PROCEDURE [dbo].[game_attend_set_data]
@ACCOUNT_IDX INT,
@ATTEND_COUNT INT,
@REWARD_TABLE INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
DECLARE @CONNECT_DATE DATETIME
SET @CONNECT_DATE = GETDATE();
SET @nReturn=0 -- True
BEGIN TRAN
-- MERGE dbo.userAttendDB as TARGET
-- USING (SELECT @ACCOUNT_IDX, @ATTEND_COUNT, @REWARD_TABLE) AS SOURCE (AccountIDX, AttendCount, RewardTable)
-- ON (TARGET.AccountIDX=SOURCE.AccountIDX)
-- WHEN MATCHED THEN
-- UPDATE SET AttendCount=SOURCE.AttendCount, RewardTable=SOURCE.RewardTable, ConnectDate=@CONNECT_DATE
-- WHEN NOT MATCHED THEN
-- INSERT (AccountIDX, AttendCount, RewardTable, ConnectDate, RegDate) VALUES (SOURCE.AccountIDX, SOURCE.AttendCount, SOURCE.RewardTable, @CONNECT_DATE, GETDATE());
UPDATE dbo.userAttendDB WITH (XLOCK, ROWLOCK) SET AttendCount=@ATTEND_COUNT, RewardTable=@REWARD_TABLE, ConnectDate=@CONNECT_DATE
WHERE AccountIDX=@ACCOUNT_IDX
IF (@@ROWCOUNT=0)
BEGIN
INSERT dbo.userAttendDB WITH (XLOCK, ROWLOCK) (AccountIDX, AttendCount, RewardTable, ConnectDate, RegDate)
VALUES (@ACCOUNT_IDX, @ATTEND_COUNT, @REWARD_TABLE, @CONNECT_DATE, GETDATE())
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -1
RETURN;
END
END
COMMIT TRAN
SELECT 0, @CONNECT_DATE as ConnectDate;
RETURN;
GO
IF OBJECT_ID('dbo.game_guild_reward_set_attend') IS NOT NULL DROP PROCEDURE dbo.game_guild_reward_set_attend
GO
-- =============================================
-- Subject : dbo.game_guild_reward_set_attend
-- DBA : KeuN
-- DATE : 2015-01-20 14:18
-- Description : 길드 랭크 보상 로그(시간) 업데이트
-- =============================================
CREATE PROCEDURE [dbo].[game_guild_reward_set_attend]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
BEGIN TRAN
UPDATE dbo.userGuildInfoDB WITH (XLOCK, ROWLOCK) SET AttendDate=GETDATE()
WHERE AccountIDX=@ACCOUNT_IDX
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_guild_reward_set_guildrank') IS NOT NULL DROP PROCEDURE dbo.game_guild_reward_set_guildrank
GO
-- =============================================
-- Subject : dbo.game_guild_reward_set_guildRank
-- DBA : KeuN
-- DATE : 2015-01-20 14:18
-- Description : 길드 랭크 보상 로그(시간) 업데이트
-- =============================================
CREATE PROCEDURE [dbo].[game_guild_reward_set_guildRank]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
BEGIN TRAN
UPDATE dbo.userGuildInfoDB WITH (XLOCK, ROWLOCK) SET GuildRankDate=GETDATE()
WHERE AccountIDX=@ACCOUNT_IDX
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_guild_attend_set_add') IS NOT NULL DROP PROCEDURE dbo.game_guild_attend_set_add
GO
-- =============================================
-- Subject : dbo.game_guild_attend_set_add
-- DBA : KeuN
-- DATE : 2015-01-20 14:18
-- Description : 길드 출석부 출석 체크
-- =============================================
CREATE PROCEDURE [dbo].[game_guild_attend_set_add]
@GUILD_IDX INT,
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
DECLARE @REG_DATE DATETIME
DECLARE @INIT_DATE DATETIME
SET @REG_DATE=GETDATE()
SET @INIT_DATE = 0
-- 날짜 초기화
EXEC @nReturn=dbo.usp_RegDate_get_init @REG_DATE, 5, @INIT_DATE OUTPUT
IF (@nReturn<>0)
BEGIN
RETURN;
END
-- 이미 출석체크한 사용자는 같은날 다시 체크 할 수 없다.
IF EXISTS (SELECT * FROM dbo.userGuildAttendDB WITH (READCOMMITTED) WHERE InitDate=@INIT_DATE and AccountIDX=@ACCOUNT_IDX)
BEGIN
SELECT -1
RETURN;
END
BEGIN TRAN
-- 출석 체크
INSERT dbo.userGuildAttendDB WITH (XLOCK, ROWLOCK) VALUES (@INIT_DATE, @ACCOUNT_IDX, @GUILD_IDX, @REG_DATE);
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -2
RETURN;
END
COMMIT TRAN
SELECT 0
RETURN;
GO
IF OBJECT_ID('dbo.game_guild_attend_get_list') IS NOT NULL DROP PROCEDURE dbo.game_guild_attend_get_list
GO
-- =============================================
-- Subject : dbo.game_guild_attend_get_list
-- DBA : KeuN
-- DATE : 2015-01-20 14:18
-- Description : 길드 출석부 출석 체크 리스트
-- =============================================
CREATE PROCEDURE [dbo].[game_guild_attend_get_list]
@INIT_DATE DATETIME,
@GUILD_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
SELECT AccountIDX FROM dbo.userGuildAttendDB WITH (READCOMMITTED)
WHERE InitDate=@INIT_DATE and GuildIDX=@GUILD_IDX
IF (@@ERROR<>0)
BEGIN
SELECT -1
RETURN;
END
RETURN;
GO
IF OBJECT_ID('dbo.game_channel_get_cp_userid') IS NOT NULL DROP PROCEDURE dbo.game_channel_get_cp_userid
GO
CREATE PROCEDURE [dbo].[game_channel_get_cp_userID]
@USER_ID VARCHAR(20)
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT note FROM dbo.userCooperationDB WITH (READUNCOMMITTED)
WHERE UserID=@USER_ID
GO
IF OBJECT_ID('dbo.game_guildhq_get_data') IS NOT NULL DROP PROCEDURE dbo.game_guildhq_get_data
GO
-- =============================================
-- Subject : dbo.game_guildHQ_get_data
-- DBA : KeuN
-- DATE : 2015-07-08 13:10
-- Description : 길드본부 꾸미기 아이템 SELECT
-- =============================================
CREATE PROC [dbo].[game_guildHQ_get_data]
@GUILD_IDX INT,
@ITEM_STATUS TINYINT, -- 0: 미설치, 1:설치됨, 2:삭제
@PAGE INT,
@PAGE_SIZE INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
IF (@ITEM_STATUS=0)
BEGIN
-- 길드 공용 인벤 아이템 SELECT
SELECT ItemCode, COUNT(*) FROM dbo.userGuildHQDB WITH (READCOMMITTED)
WHERE GuildIDX=@GUILD_IDX and ItemStatus=@ITEM_STATUS
GROUP BY ItemCode
END
ELSE IF (@ITEM_STATUS=1)
BEGIN
-- 설치된 아이템 SELECT
SELECT ItemSerial, ItemCode, ItemXZ, ItemY, ItemRotate, ItemDecoScore FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ItemSerial DESC) as ItemList, ItemSerial, ItemCode, ItemXZ, ItemY, ItemRotate, ItemDecoScore
FROM dbo.userGuildHQDB WITH (READCOMMITTED) WHERE GuildIDX=@GUILD_IDX and ItemStatus=@ITEM_STATUS
) as GUILDHQ_LIST
WHERE ItemList BETWEEN (((@PAGE - 1) * @PAGE_SIZE) + 1) AND @PAGE * @PAGE_SIZE
END
RETURN;
GO
IF OBJECT_ID('dbo.game_guildhq_set_status') IS NOT NULL DROP PROCEDURE dbo.game_guildhq_set_status
GO
-- =============================================
-- Subject : dbo.game_guildHQ_set_status
-- DBA : KeuN
-- DATE : 2015-07-08 13:10
-- Description : 길드 본부 꾸미기 아이템 인벤/삭제
-- =============================================
CREATE PROC [dbo].[game_guildHQ_set_status]
@GUILD_IDX INT,
@ACCOUNT_IDX INT,
@ITEM_SERIAL BIGINT,
@ITEM_CODE INT,
@ITEM_STATUS TINYINT -- 0:인벤(해제), 2: 삭제
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
BEGIN TRAN
UPDATE dbo.userGuildHQDB WITH (XLOCK, ROWLOCK) SET ItemXZ=0, ItemY=0, ItemRotate=0, ItemStatus=@ITEM_STATUS
WHERE ItemSerial=@ITEM_SERIAL
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
-- 변경된 데이터에 대한 Version관리
EXEC @nReturn = dbo.game_guildHQ_ver_set_status @GUILD_IDX
IF @nReturn<>0
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
-- 설치로그
-- EXEC LINKED_CONNECTION_BAK.LosaLogDataC.dbo.log_guildHQ_fit_set_insert @GUILD_IDX, @ACCOUNT_IDX, @ITEM_SERIAL, @ITEM_CODE, 0
RETURN;
GO
IF OBJECT_ID('dbo.game_guildhq_set_fit') IS NOT NULL DROP PROCEDURE dbo.game_guildhq_set_fit
GO
-- =============================================
-- Subject : dbo.game_guildHQ_set_fit
-- DBA : KeuN
-- DATE : 2015-07-08 13:10
-- Description : 길드 본부 꾸미기 아이템 설치 좌표
-- =============================================
CREATE PROC [dbo].[game_guildHQ_set_fit]
@GUILD_IDX INT,
@ACCOUNT_IDX INT,
@ITEM_SERIAL BIGINT,
@ITEM_CODE INT,
@ITEM_XZ INT,
@ITEM_Y INT,
@ITEM_ROTATE TINYINT,
@ITEM_STATUS TINYINT -- 1:설치, 2: 변경
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
-- 최초 설치시 즉, 인벤(ItemStus=0)에서 userGuildHQDB로 넘어와서 설치 될때 서버가 ItemSerial 정보를 모르기 때문에 제일 먼저 인벤에
-- 들어온 아이템 부터 설치 하도록 ItemSerial을 서버에 전달
IF (@ITEM_SERIAL=0 and @ITEM_STATUS=1)
BEGIN
SET @ITEM_SERIAL= (SELECT MIN(ItemSerial) FROM dbo.userGuildHQDB WHERE GuildIDX=@GUILD_IDX and ItemCode=@ITEM_CODE and ItemStatus=0)
SELECT @ITEM_SERIAL
END
BEGIN TRAN
-- @ITEM_STATUS 는 0: 미설치, 1:설치됨, 2:삭제 만 사용되지만, 현재 설치된 아이템의 위치 변경을 위해
-- 현재 프로시저에서 2번 코드를 받지만 실제는 1로 강제 초기화 하여 설치로 상태 정보를 유지한다.
SET @ITEM_STATUS=1
UPDATE dbo.userGuildHQDB WITH (XLOCK, ROWLOCK) SET ItemXZ=@ITEM_XZ, ItemY=@ITEM_Y, ItemRotate=@ITEM_ROTATE, ItemStatus=@ITEM_STATUS
WHERE ItemSerial=@ITEM_SERIAL
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
SELECT -1;
ROLLBACK TRAN
RETURN;
END
-- 변경된 데이터에 대한 Version관리
EXEC @nReturn = dbo.game_guildHQ_ver_set_status @GUILD_IDX
IF @nReturn<>0
BEGIN
SELECT @nReturn
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
-- 설치로그
-- EXEC LINKED_CONNECTION_BAK.LosaLogDataC.dbo.log_guildHQ_fit_set_insert @GUILD_IDX, @ACCOUNT_IDX, @ITEM_SERIAL, @ITEM_CODE, 1
RETURN;
GO
IF OBJECT_ID('dbo.game_guildhq_ver_get_info') IS NOT NULL DROP PROCEDURE dbo.game_guildhq_ver_get_info
GO
-- =============================================
-- Subject : dbo.game_guildHQ_ver_get_info
-- DBA : KEUN
-- DATE : 2015-07-13
-- Description : 길드 본부 변경사항에 대한 버전 조회
-- =============================================
CREATE PROCEDURE [dbo].[game_guildHQ_ver_get_info]
@GUILD_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT VerStatus FROM dbo.userGuildHQVerDB WITH (READCOMMITTED) WHERE GuildIDX=@GUILD_IDX
RETURN;
GO
IF OBJECT_ID('dbo.game_guildhq_set_add') IS NOT NULL DROP PROCEDURE dbo.game_guildhq_set_add
GO
-- =============================================
-- Subject : dbo.game_guildHQ_set_add
-- DBA : KeuN
-- DATE : 2015-07-08 13:10
-- Description : 길드본부 꾸미기 아이템 추가
-- =============================================
CREATE PROC [dbo].[game_guildHQ_set_add]
@GUILD_IDX INT,
@ITEM_CODE INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
BEGIN TRAN
-- 길드 본부 꾸미기 아이템 추가
INSERT dbo.userGuildHQDB WITH (XLOCK, ROWLOCK) (GuildIDX, ItemCode) VALUES (@GUILD_IDX, @ITEM_CODE)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
-- 변경된 데이터에 대한 Version관리
EXEC @nReturn = dbo.game_guildHQ_ver_set_status @GUILD_IDX
IF @nReturn<>0
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_guildhq_set_default_add') IS NOT NULL DROP PROCEDURE dbo.game_guildhq_set_default_add
GO
-- =============================================
-- Subject : dbo.game_guildHQ_set_default_add
-- DBA : KeuN
-- DATE : 2015-08-18 10:28
-- Description : 길드 생성시 기본 아이템 설치
-- =============================================
CREATE PROC [dbo].[game_guildHQ_set_default_add]
@GUILD_IDX INT,
@ITEM_CODE INT,
@ITEM_XZ INT,
@ITEM_Y INT,
@ITEM_ROTATE TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
BEGIN TRAN
-- 길드 본부 꾸미기 아이템 추가
INSERT dbo.userGuildHQDB WITH (XLOCK, ROWLOCK) (GuildIDX, ItemCode, ItemXZ, ItemY, ItemRotate, ItemStatus) VALUES (@GUILD_IDX, @ITEM_CODE, @ITEM_XZ, @ITEM_Y, @ITEM_ROTATE, 1)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
-- 변경된 데이터에 대한 Version관리
EXEC @nReturn = dbo.game_guildHQ_ver_set_status @GUILD_IDX
IF @nReturn<>0
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_personalhq_get_data') IS NOT NULL DROP PROCEDURE dbo.game_personalhq_get_data
GO
-- =============================================
-- Subject : dbo.game_personalHQ_get_data
-- DBA : KeuN
-- DATE : 2015-09-30 13:10
-- Description : 개인 본부 꾸미기 아이템 SELECT
-- =============================================
CREATE PROC [dbo].[game_personalHQ_get_data]
@ACCOUNT_IDX INT,
@ITEM_STATUS TINYINT, -- 0: 미설치, 1:설치됨, 2:삭제
@PAGE INT,
@PAGE_SIZE INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
IF (@ITEM_STATUS=0)
BEGIN
-- 길드 공용 인벤 아이템 SELECT
SELECT ItemCode, COUNT(*) FROM dbo.userPersonalHQDB WITH (READCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX and ItemStatus=@ITEM_STATUS
GROUP BY ItemCode
END
ELSE IF (@ITEM_STATUS=1)
BEGIN
-- 설치된 아이템 SELECT
SELECT ItemSerial, ItemCode, ItemXZ, ItemY, ItemRotate, ItemDecoScore FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ItemSerial DESC) as ItemList, ItemSerial, ItemCode, ItemXZ, ItemY, ItemRotate, ItemDecoScore
FROM dbo.userPersonalHQDB WITH (READCOMMITTED) WHERE AccountIDX=@ACCOUNT_IDX and ItemStatus=@ITEM_STATUS
) as USER_PERSONAL_HQ_LIST
WHERE ItemList BETWEEN (((@PAGE - 1) * @PAGE_SIZE) + 1) AND @PAGE * @PAGE_SIZE
END
RETURN;
GO
IF OBJECT_ID('dbo.game_personalhq_set_add') IS NOT NULL DROP PROCEDURE dbo.game_personalhq_set_add
GO
-- =============================================
-- Subject : dbo.game_personalHQ_set_add
-- DBA : KeuN
-- DATE : 2015-09-30 17:10:00
-- Description : 개인 본부 꾸미기 아이템 추가
-- =============================================
CREATE PROC [dbo].[game_personalHQ_set_add]
@ACCOUNT_IDX INT,
@ITEM_CODE INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
BEGIN TRAN
-- 길드 본부 꾸미기 아이템 추가
INSERT dbo.userPersonalHQDB WITH (XLOCK, ROWLOCK) (AccountIDX, ItemCode) VALUES (@ACCOUNT_IDX, @ITEM_CODE)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_personalhq_get_user_info') IS NOT NULL DROP PROCEDURE dbo.game_personalhq_get_user_info
GO
-- =============================================
-- Subject : dbo.game_personalHQ_get_user_info
-- DBA : KeuN
-- DATE : 2015-10-01 13:10
-- Description : 개인 본부 사용 여부 확인
-- =============================================
CREATE PROC [dbo].[game_personalHQ_get_user_info]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
IF EXISTS (SELECT * FROM dbo.userPersonalHQDB WITH (READUNCOMMITTED) WHERE AccountIDX=@ACCOUNT_IDX)
BEGIN
SELECT 1;
RETURN
END
SELECT 0;
RETURN;
GO
IF OBJECT_ID('dbo.game_personalhq_set_status') IS NOT NULL DROP PROCEDURE dbo.game_personalhq_set_status
GO
-- =============================================
-- Subject : dbo.game_personalHQ_set_status
-- DBA : KeuN
-- DATE : 2015-10-01 13:10
-- Description : 개인 본부 꾸미기 아이템 인벤/삭제
-- =============================================
CREATE PROC [dbo].[game_personalHQ_set_status]
@ACCOUNT_IDX INT,
@ITEM_SERIAL BIGINT,
@ITEM_CODE INT,
@ITEM_STATUS TINYINT -- 0:인벤(해제), 2: 삭제
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
BEGIN TRAN
UPDATE dbo.userPersonalHQDB WITH (XLOCK, ROWLOCK) SET ItemXZ=0, ItemY=0, ItemRotate=0, ItemStatus=@ITEM_STATUS
WHERE ItemSerial=@ITEM_SERIAL
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
-- 설치로그
-- EXEC LINKED_CONNECTION_BAK.LosaLogDataC.dbo.log_guildHQ_fit_set_insert @GUILD_IDX, @ACCOUNT_IDX, @ITEM_SERIAL, @ITEM_CODE, 0
RETURN;
GO
IF OBJECT_ID('dbo.game_personalhq_set_fit') IS NOT NULL DROP PROCEDURE dbo.game_personalhq_set_fit
GO
-- =============================================
-- Subject : dbo.game_personalHQ_set_fit
-- DBA : KeuN
-- DATE : 2015-10-01 13:10
-- Description : 개인 본부 꾸미기 아이템 설치 좌표
-- =============================================
CREATE PROC [dbo].[game_personalHQ_set_fit]
@ACCOUNT_IDX INT,
@ITEM_SERIAL BIGINT,
@ITEM_CODE INT,
@ITEM_XZ INT,
@ITEM_Y INT,
@ITEM_ROTATE TINYINT,
@ITEM_STATUS TINYINT -- 1:설치, 2: 변경
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
-- 최초 설치시 즉, 인벤(ItemStus=0)에서 userGuildHQDB로 넘어와서 설치 될때 서버가 ItemSerial 정보를 모르기 때문에 제일 먼저 인벤에
-- 들어온 아이템 부터 설치 하도록 ItemSerial을 서버에 전달
IF (@ITEM_SERIAL=0 and @ITEM_STATUS=1)
BEGIN
SET @ITEM_SERIAL= (SELECT MIN(ItemSerial) FROM dbo.userPersonalHQDB WHERE AccountIDX=@ACCOUNT_IDX and ItemCode=@ITEM_CODE and ItemStatus=0)
SELECT @ITEM_SERIAL
END
BEGIN TRAN
-- @ITEM_STATUS 는 0: 미설치, 1:설치됨, 2:삭제 만 사용되지만, 현재 설치된 아이템의 위치 변경을 위해
-- 현재 프로시저에서 2번 코드를 받지만 실제는 1로 강제 초기화 하여 설치로 상태 정보를 유지한다.
SET @ITEM_STATUS=1
UPDATE dbo.userPersonalHQDB WITH (XLOCK, ROWLOCK) SET ItemXZ=@ITEM_XZ, ItemY=@ITEM_Y, ItemRotate=@ITEM_ROTATE, ItemStatus=@ITEM_STATUS
WHERE ItemSerial=@ITEM_SERIAL
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
SELECT -1;
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
-- 설치로그
-- EXEC LINKED_CONNECTION_BAK.LosaLogDataC.dbo.log_guildHQ_fit_set_insert @GUILD_IDX, @ACCOUNT_IDX, @ITEM_SERIAL, @ITEM_CODE, 1
RETURN;
GO
IF OBJECT_ID('dbo.game_personalhq_set_default_add') IS NOT NULL DROP PROCEDURE dbo.game_personalhq_set_default_add
GO
-- =============================================
-- Subject : dbo.game_personalHQ_set_default_add
-- DBA : KeuN
-- DATE : 2015-09-30 15:28
-- Description : 개인 본부 생성시 기본 아이템 설치
-- =============================================
CREATE PROC [dbo].[game_personalHQ_set_default_add]
@ACCCOUNT_IDX INT,
@ITEM_CODE INT,
@ITEM_XZ INT,
@ITEM_Y INT,
@ITEM_ROTATE TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 15000
DECLARE @nReturn INT = 0;
BEGIN TRAN
-- 길드 본부 꾸미기 아이템 추가
INSERT dbo.userPersonalHQDB WITH (XLOCK, ROWLOCK) (AccountIDX, ItemCode, ItemXZ, ItemY, ItemRotate, ItemStatus) VALUES (@ACCCOUNT_IDX, @ITEM_CODE, @ITEM_XZ, @ITEM_Y, @ITEM_ROTATE, 1)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_title_get_data') IS NOT NULL DROP PROCEDURE dbo.game_title_get_data
GO
-- =============================================
-- Subject : dbo.game_title_get_data
-- DBA : Michael.DongKeun
-- DATE : 2015-10-26 11:38:00
-- Description : 칭호시스템 데이터 세팅
-- =============================================
CREATE PROCEDURE [dbo].[game_title_get_data]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
BEGIN
SELECT TitleCode, TitleValue, TitleLevel, TitlePremium, TitleEquip, TitleStatus FROM dbo.userTitleDB WITH (READUNCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX
IF (@@ERROR<>0)
BEGIN
RETURN;
END
END
RETURN;
GO
IF OBJECT_ID('dbo.game_title_set_status') IS NOT NULL DROP PROCEDURE dbo.game_title_set_status
GO
-- =============================================
-- Subject : dbo.game_title_set_status
-- DBA : Michael.DongKeun
-- DATE : 2015-10-26 11:38:00
-- Description : 칭호시스템 상태 변경 New -> Active
-- =============================================
CREATE PROCEDURE [dbo].[game_title_set_status]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
BEGIN TRAN
UPDATE dbo.userTitleDB WITH (XLOCK, ROWLOCK) SET TitleStatus=1
WHERE AccountIDX=@ACCOUNT_IDX and TitleStatus=2
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_title_set_add') IS NOT NULL DROP PROCEDURE dbo.game_title_set_add
GO
-- =============================================
-- Subject : dbo.game_title_set_add
-- DBA : Michael.DongKeun
-- DATE : 2015-10-26 11:38:00
-- Description : 칭호시스템 데이터 세팅
-- =============================================
CREATE PROCEDURE [dbo].[game_title_set_add]
@ACCOUNT_IDX INT,
@TITLE_CODE INT,
@TITLE_VALUE BIGINT,
@TITLE_LEVEL INT,
@TITLE_PREMIUM TINYINT,
@TITLE_EQUIP TINYINT,
@TITLE_STATUS TINYINT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @GET_DATE DATETIME
SET @GET_DATE=GETDATE();
BEGIN TRAN
-- Insert or Update
;MERGE dbo.userTitleDB as TARGET
USING (SELECT @ACCOUNT_IDX, @TITLE_CODE) AS SOURCE (AccountIDX, TitleCode)
ON (TARGET.AccountIDX=SOURCE.AccountIDX and TARGET.TitleCode=SOURCE.TitleCode)
WHEN MATCHED THEN
UPDATE SET TARGET.TitleValue=@TITLE_VALUE, TARGET.TitleLevel=@TITLE_LEVEL, TARGET.TitlePremium=@TITLE_PREMIUM, TARGET.TitleEquip=@TITLE_EQUIP, TARGET.TitleStatus=@TITLE_STATUS, TARGET.UpdDate=@GET_DATE
WHEN NOT MATCHED THEN
INSERT (AccountIDX, TitleCode, TitleValue, TitleLevel, TitlePremium, TitleEquip, TitleStatus) VALUES (SOURCE.AccountIDX, SOURCE.TitleCode, @TITLE_VALUE, @TITLE_LEVEL, @TITLE_PREMIUM, @TITLE_EQUIP, @TITLE_STATUS);
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
RETURN;
END
COMMIT TRAN
RETURN;
GO
IF OBJECT_ID('dbo.game_timecash_get_list') IS NOT NULL DROP PROCEDURE dbo.game_timecash_get_list
GO
-- =============================================
-- Subject : dbo.game_timeCash_get_list
-- DBA : Michael.DongKeun
-- DATE : 2015-11-10 11:19:00
-- Description : 캐쉬 지급 아이템 리스트
-- =============================================
CREATE PROCEDURE [dbo].[game_timeCash_get_list]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT ItemCode, startDate, OverDate, UpdDate FROM dbo.userTimeCashDB WITH (READCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX and ItemStatus=1
IF (@@ERROR<>0)
BEGIN
RETURN -1;
END
RETURN;
GO
IF OBJECT_ID('dbo.game_timecash_set_cash') IS NOT NULL DROP PROCEDURE dbo.game_timecash_set_cash
GO
-- =============================================
-- Subject : dbo.game_timeCash_set_cash
-- DBA : Michael.DongKeun
-- DATE : 2015-11-09 11:19:00
-- Description : 캐쉬 지급 아이템
-- =============================================
CREATE PROCEDURE [dbo].[game_timeCash_set_cash]
@ACCOUNT_IDX INT,
@ITEM_CODE INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT = 0;
DECLARE @ITEM_STATUS TINYINT = 0;
DECLARE @GET_DATE DATETIME = GETDATE();
DECLARE @UPD_DATE DATETIME = 0;
DECLARE @ABLE_START DATETIME = DATEADD(hh, 5, CAST(CAST(@GET_DATE as DATE) as DATETIME));
DECLARE @ABLE_END DATETIME = DATEADD(hh, 5, CAST(CAST(@GET_DATE+1 as DATE) as DATETIME));
-- 아이템 기간 만료 및 보유 여부 확인
EXEC @nReturn=dbo.usp_timeCash_set_expire @ACCOUNT_IDX, @ITEM_CODE, @GET_DATE, @ITEM_STATUS OUTPUT, @UPD_DATE OUTPUT
-- -1: 아이템 만료 처리 실패, -2: 없는 아이템
IF @nReturn IN (-1, -2)
BEGIn
SELECT @nReturn, @GET_DATE
RETURN;
END
-- 정상 확인 했고, 아이템이 만료 되지 않았으면,
IF (@nReturn=0) and (@ITEM_STATUS=1)
BEGIN
-- 이미 받은 사용자면 False Return
IF (@UPD_DATE>=@ABLE_START) and (@UPD_DATE<=@ABLE_END)
BEGIN
SELECT 2, @GET_DATE
RETURN;
END
ELSE
BEGIN
BEGIN TRAN
-- 정상 처리로 변경된 시간을 업데이트 하고, 횟수를 업데이트 한다.
UPDATE dbo.userTimeCashDB WITH (XLOCK, ROWLOCK) SET UpdDate=@GET_DATE, ItemCount+=1
WHERE AccountIDX=@ACCOUNT_IDX and ItemCode=@ITEM_CODE
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT 0, @GET_DATE
RETURN;
END
COMMIT TRAN
SELECT 0, @GET_DATE
RETURN;
END
END
-- 기간 만료된 아이템
SELECT 1, @GET_DATE
RETURN;
GO
IF OBJECT_ID('dbo.game_gold_get_data') IS NOT NULL DROP PROCEDURE dbo.game_gold_get_data
GO
-- =============================================
-- Subject : dbo.game_gold_get_data
-- Author : galura
-- DATE : 2016-02-29
-- Description : 보너스 골드 정보
-- =============================================
CREATE PROCEDURE [dbo].[game_gold_get_data]
@ACCOUNT_IDX INT,
@GOLD_TYPE TINYINT
AS
SET NOCOUNT ON;
SET LOCK_TIMEOUT 10000;
DECLARE @nReturn INT = 0 -- True
SELECT idx, OriginalAmount, Amount, ExpirationDate
FROM dbo.userGoldDB WITH (NOLOCK)
WHERE AccountIDX = @ACCOUNT_IDX
AND GoldType = @GOLD_TYPE
AND GoldStatus = 1
AND ExpirationDate > GETDATE()
ORDER BY ExpirationDate ASC, idx asc;
RETURN @nReturn;
GO
IF OBJECT_ID('dbo.game_gold_set_delete') IS NOT NULL DROP PROCEDURE dbo.game_gold_set_delete
GO
-- =============================================
-- Subject : dbo.game_gold_set_delete
-- Author : galura
-- DATE : 2016-02-29
-- Description : 보너스 골드 유효기간 만료 데이터 삭제
-- =============================================
CREATE PROCEDURE [dbo].[game_gold_set_delete]
@ACCOUNT_IDX INT,
@GOLD_TYPE TINYINT
AS
SET NOCOUNT ON;
SET LOCK_TIMEOUT 10000;
DECLARE @nReturn INT = 0 -- True
UPDATE dbo.userGoldDB WITH (XLOCK, ROWLOCK)
SET GoldStatus = 0
WHERE AccountIDX = @ACCOUNT_IDX
AND GoldType = @GOLD_TYPE
AND GoldStatus = 1
AND ExpirationDate < GETDATE();
IF (@@ERROR <> 0)
BEGIN
RETURN -1;
END
RETURN @nReturn;
GO
IF OBJECT_ID('dbo.game_gold_get_used_list') IS NOT NULL DROP PROCEDURE dbo.game_gold_get_used_list
GO
-- =============================================
-- Subject : dbo.game_gold_get_data
-- Author : galura
-- DATE : 2016-02-29
-- Description : 보너스 골드 정보
-- =============================================
CREATE PROCEDURE [dbo].[game_gold_get_used_list]
@ACCOUNT_IDX INT,
@GOLD_TYPE TINYINT,
@TOP_N SMALLINT
AS
SET NOCOUNT ON;
SET LOCK_TIMEOUT 10000;
DECLARE @nReturn INT = 0 -- True
SELECT TOP (@TOP_N) idx, OriginalAmount, Amount, ExpirationDate
FROM dbo.userGoldDB WITH (NOLOCK)
WHERE AccountIDX = @ACCOUNT_IDX
AND GoldType = @GOLD_TYPE
AND GoldStatus = 0
ORDER BY idx DESC
RETURN @nReturn;
GO
IF OBJECT_ID('dbo.game_league_list') IS NOT NULL DROP PROCEDURE dbo.game_league_list
GO
CREATE PROCEDURE [dbo].[game_league_list]
(
@topN int
, @idx int
)
AS
SET NOCOUNT ON
SELECT TOP (@topN) idx, accountIDX, startDate, endDate, leagueType, leagueState
FROM dbo.userLeagueListDB WITH(NOLOCK)
WHERE idx < @idx
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_league_list_save') IS NOT NULL DROP PROCEDURE dbo.game_league_list_save
GO
CREATE PROCEDURE [dbo].[game_league_list_save]
(
@idx int
, @startDate int
, @endDate int
, @leagueType tinyint
, @leagueState tinyint
)
AS
SET NOCOUNT ON
UPDATE dbo.userLeagueListDB
SET startDate = @startDate
, endDate = @endDate
, leagueType = @leagueType
, leagueState = @leagueState
WHERE idx = @idx
GO
IF OBJECT_ID('dbo.game_league_team_add') IS NOT NULL DROP PROCEDURE dbo.game_league_team_add
GO
CREATE PROCEDURE [dbo].[game_league_team_add]
(
@leagueIDX int
, @teamName nvarchar(20)
, @leaderIDX int
, @maxcount tinyint
, @factionPoint int
, @factionType tinyint
)
AS
SET NOCOUNT ON
DECLARE @errcount int
DECLARE @teamIDX int
SET @errcount = 0
SET @teamIDX = 0
IF EXISTS(SELECT * FROM dbo.userLeagueTeamDB WITH(NOLOCK) WHERE leagueIDX=@leagueIDX AND teamName=@teamName)
BEGIN
SET @errcount = 2
SELECT @errcount AS 'returnVal'
RETURN
END
IF EXISTS(SELECT * FROM dbo.userLeagueTeamDB WITH(NOLOCK) WHERE leagueIDX=@leagueIDX AND leaderIDX=@leaderIDX)
BEGIN
SET @errcount = 3
SELECT @errcount AS 'returnVal'
RETURN
END
IF EXISTS(SELECT * FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK) WHERE leagueIDX=@leagueIDX AND accountIDX=@leaderIDX AND joinType=1)
BEGIN
SET @errcount = 4
SELECT @errcount AS 'returnVal'
RETURN
END
IF (@errcount = 0)
BEGIN
SET @errcount = 1
SELECT @errcount AS 'returnVal'
INSERT INTO dbo.userLeagueTeamDB (
leagueIDX, teamName, leaderIDX, leaguePosition, maxcount, cheerPoint, currentRound, factionPoint, factionType, leagueStartPosition, regDate
)
VALUES (
@leagueIDX, @teamName, @leaderIDX, 0, @maxcount, 0, 0, @factionPoint, @factionType, 0, GETDATE()
)
SET @teamIDX = SCOPE_IDENTITY()
INSERT INTO dbo.userLeagueTeamMemberDB (accountIDX, leagueIDX, teamIDX, joinType, regDate)
VALUES (@leaderIDX, @leagueIDX, @teamIDX, 1, GETDATE())
END
GO
IF OBJECT_ID('dbo.game_league_team_self_index') IS NOT NULL DROP PROCEDURE dbo.game_league_team_self_index
GO
CREATE PROCEDURE [dbo].[game_league_team_self_index]
(
@leagueIDX int
, @leaderIDX int
)
AS
SET NOCOUNT ON
SELECT TOP 1 idx
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE leagueIDX = @leagueIDX
AND leaderIDX = @leaderIDX
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_league_my_team_list') IS NOT NULL DROP PROCEDURE dbo.game_league_my_team_list
GO
CREATE PROCEDURE [dbo].[game_league_my_team_list]
(
@topN int
, @idx int
, @accountIDX int
)
AS
SET NOCOUNT ON
SELECT TOP (@topN) idx, leagueIDX, teamName, leaderIDX, leaguePosition,
cheerPoint, currentRound, factionPoint, factionType
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE idx < @idx
AND idx IN (
SELECT teamIDX
FROM userLeagueTeamMemberDB WITH(NOLOCK)
WHERE accountIDX=@accountIDX
AND joinType=1
)
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_league_team_delete') IS NOT NULL DROP PROCEDURE dbo.game_league_team_delete
GO
CREATE PROCEDURE [dbo].[game_league_team_delete]
(
@teamIDX int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueTeamMemberDB WHERE teamIDX=@teamIDX
DELETE FROM dbo.userLeagueTeamDB WHERE idx=@teamIDX
GO
IF OBJECT_ID('dbo.game_league_team_list') IS NOT NULL DROP PROCEDURE dbo.game_league_team_list
GO
CREATE PROCEDURE [dbo].[game_league_team_list]
(
@topN int
, @idx int
)
AS
SET NOCOUNT ON
SELECT TOP (@topN) idx, leagueIDX, teamName, leaderIDX, leaguePosition,
maxcount, cheerPoint, currentRound, factionPoint, factionType, leagueStartPosition
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE idx < @idx
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_league_team_get_data') IS NOT NULL DROP PROCEDURE dbo.game_league_team_get_data
GO
CREATE PROCEDURE [dbo].[game_league_team_get_data]
(
@idx int
)
AS
SET NOCOUNT ON
SELECT leagueIDX, teamName, leaderIDX, leaguePosition,
maxcount, cheerPoint, currentRound, factionPoint, factionType
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_league_team_delete_all') IS NOT NULL DROP PROCEDURE dbo.game_league_team_delete_all
GO
CREATE PROCEDURE [dbo].[game_league_team_delete_all]
(
@leagueIDX int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueTeamMemberDB WHERE leagueIDX=@leagueIDX
DELETE FROM dbo.userLeagueTeamDB WHERE leagueIDX=@leagueIDX
DELETE FROM dbo.userLeagueCheerDB WHERE leagueIDX=@leagueIDX
GO
IF OBJECT_ID('dbo.game_league_team_member_list') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_list
GO
CREATE PROCEDURE [dbo].[game_league_team_member_list]
(
@teamIDX int
)
AS
SET NOCOUNT ON
SELECT a.idx, a.accountIDX, b.nickName, c.userLevel, c.renspoint,
CASE WHEN d.guildJoinType=1 THEN d.guildIDX
ELSE 0
END AS guildIDX
FROM dbo.userLeagueTeamMemberDB a WITH(NOLOCK)
JOIN dbo.userMemberDB b WITH(NOLOCK)
ON a.accountIDX = b.accountIDX
JOIN dbo.userGameDB c WITH(NOLOCK)
ON a.accountIDX = c.accountIDX
LEFT JOIN dbo.userGuildInfoDB d WITH(NOLOCK)
ON a.accountIDX = d.accountIDX
WHERE a.teamIDX=@teamIDX
AND a.joinType=1
ORDER BY a.idx ASC
GO
IF OBJECT_ID('dbo.game_league_backup') IS NOT NULL DROP PROCEDURE dbo.game_league_backup
GO
CREATE PROCEDURE [dbo].[game_league_backup]
(
@leagueIDX int
)
AS
SET NOCOUNT ON
DECLARE @startDate int
SET @startDate = ISNULL((
SELECT startDate
FROM dbo.userLeagueListDB WITH(NOLOCK)
WHERE idx=@leagueIDX
), 0)
-- 리그 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_list (
leagueIDX, accountIDX, startDate, endDate, leagueType, leagueState, regDate, insertDate
)
SELECT idx, accountIDX, startDate, endDate, leagueType, leagueState, regDate, GETDATE()
FROM dbo.userLeagueListDB WITH(NOLOCK)
WHERE idx=@leagueIDX
-- 리그 팀 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_team (
teamIDX, leagueIDX, startDate, teamName, leaderIDX, leagueStartPosition, leaguePosition, maxcount, cheerPoint,
currentRound, factionPoint, factionType, regDate, insertDate
)
SELECT idx, leagueIDX, @startDate, teamName, leaderIDX, leagueStartPosition, leaguePosition, maxcount, cheerPoint,
currentRound, factionPoint, factionType, regDate, GETDATE()
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
-- 리그 팀원 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_team_member (
teamMemberIDX, startDate, accountIDX, leagueIDX, teamIDX, joinType, regDate, insertDate
)
SELECT idx, @startDate, accountIDX, leagueIDX, teamIDX, joinType, regDate, GETDATE()
FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
-- 리그 응원 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_cheer (
cheerIDX, startDate, accountIDX, leagueIDX, teamIDX, regDate, insertDate
)
SELECT idx, @startDate, accountIDX, leagueIDX, teamIDX, regDate, GETDATE()
FROM dbo.userLeagueCheerDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
GO
IF OBJECT_ID('dbo.game_league_team_member_app_list') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_app_list
GO
CREATE PROCEDURE [dbo].[game_league_team_member_app_list]
(
@teamIDX int
)
AS
SET NOCOUNT ON
SELECT a.idx, a.accountIDX, b.nickName, c.userLevel, c.regionType,
CASE WHEN d.guildJoinType=1 THEN d.guildIDX
ELSE 0
END AS guildIDX
FROM dbo.userLeagueTeamMemberDB a WITH(NOLOCK)
JOIN dbo.userMemberDB b WITH(NOLOCK)
ON a.accountIDX = b.accountIDX
JOIN dbo.userGameDB c WITH(NOLOCK)
ON a.accountIDX = c.accountIDX
LEFT JOIN dbo.userGuildInfoDB d WITH(NOLOCK)
ON a.accountIDX = d.accountIDX
WHERE a.teamIDX=@teamIDX
AND a.joinType=0
ORDER BY a.idx ASC
GO
IF OBJECT_ID('dbo.game_league_team_member_app_add') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_app_add
GO
CREATE PROCEDURE [dbo].[game_league_team_member_app_add]
(
@accountIDX int
, @leagueIDX int
, @teamIDX int
)
AS
SET NOCOUNT ON
DECLARE @chkcnt int
DECLARE @errcount int
DECLARE @maxque tinyint
SET @chkcnt = 0
SET @errcount = 0
SET @maxque = 8
SET @chkcnt = (SELECT joinType FROM dbo.userMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX)
IF (@chkcnt <= 1)
BEGIN
SET @errcount = 2
SELECT @errcount AS 'returnVal'
RETURN
END
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userLeagueTeamDB WITH(NOLOCK) WHERE idx=@teamIDX)
IF (@chkcnt = 0)
BEGIN
SET @errcount = 6
SELECT @errcount AS 'returnVal'
RETURN
END
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX AND leagueIDX=@leagueIDX AND joinType=1)
IF (@chkcnt > 0)
BEGIN
SET @errcount = 3
SELECT @errcount AS 'returnVal'
RETURN
END
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX AND teamIDX=@teamIDX and joinType=0)
IF (@chkcnt > 0)
BEGIN
SET @errcount = 4
SELECT @errcount AS 'returnVal'
RETURN
END
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK) WHERE teamIDX=@teamIDX and joinType=0)
IF (@maxque <= @chkcnt)
BEGIN
SET @errcount = 5
SELECT @errcount AS 'returnVal'
RETURN
END
IF (@errcount = 0)
BEGIN
SET @errcount = 1
SELECT @errcount AS 'returnVal'
INSERT INTO dbo.userLeagueTeamMemberDB (accountIDX, leagueIDX, teamIDX, joinType, regDate)
VALUES (@accountIDX, @leagueIDX, @teamIDX, 0, GETDATE())
END
GO
IF OBJECT_ID('dbo.game_league_team_member_app_del') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_app_del
GO
CREATE PROCEDURE [dbo].[game_league_team_member_app_del]
(
@idx int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueTeamMemberDB WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_league_team_member_app_reg') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_app_reg
GO
CREATE PROCEDURE [dbo].[game_league_team_member_app_reg]
(
@accountIDX int
, @leagueIDX int
, @teamIDX int
, @factionType tinyint
)
AS
SET NOCOUNT ON
DECLARE @chkcnt int
DECLARE @errcount int
DECLARE @maxmember tinyint
DECLARE @dbFactionType tinyint
SET @chkcnt = 0
SET @errcount = 0
SET @maxmember = 0
SET @dbFactionType = 0
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX)
IF (@chkcnt = 0)
BEGIN
SET @errcount = 2
SELECT @errcount AS 'returnVal'
RETURN
END
SET @chkcnt = (SELECT joinType FROM dbo.userMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX)
IF (@chkcnt <= 1)
BEGIN
SET @errcount = 3
SELECT @errcount AS 'returnVal'
RETURN
END
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX AND leagueIDX=@leagueIDX AND joinType=1)
IF (@chkcnt > 0)
BEGIN
SET @errcount = 4
SELECT @errcount AS 'returnVal'
RETURN
END
SET @maxmember = (SELECT maxcount FROM dbo.userLeagueTeamDB WITH(NOLOCK) WHERE idx=@teamIDX)
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK) WHERE teamIDX=@teamIDX AND joinType=1)
IF (@maxmember <= @chkcnt)
BEGIN
SET @errcount = 5
SELECT @errcount AS 'returnVal'
RETURN
END
IF (@factionType > 0)
BEGIN
SET @dbFactionType = (SELECT regionType FROM dbo.userGameDB WITH(NOLOCK) WHERE accountIDX=@accountIDX)
IF (@factionType <> @dbFactionType)
BEGIN
SET @errcount = 6
SELECT @errcount AS 'returnVal'
RETURN
END
END
IF (@errcount = 0)
BEGIN
SET @errcount = 1
SELECT @errcount AS 'returnVal'
UPDATE dbo.userLeagueTeamMemberDB
SET joinType=1
WHERE teamIDX=@teamIDX AND accountIDX=@accountIDX
END
GO
IF OBJECT_ID('dbo.game_league_team_member_app_list_del') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_app_list_del
GO
CREATE PROCEDURE [dbo].[game_league_team_member_app_list_del]
(
@accountIDX int
, @leagueIDX int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueTeamMemberDB
WHERE accountIDX=@accountIDX
AND leagueIDX=@leagueIDX
AND joinType=0
GO
IF OBJECT_ID('dbo.game_league_team_member_delete') IS NOT NULL DROP PROCEDURE dbo.game_league_team_member_delete
GO
CREATE PROCEDURE [dbo].[game_league_team_member_delete]
(
@idx int
, @accountIDX int
)
AS
SET NOCOUNT ON
DECLARE @renspoint int
DECLARE @chkcnt int
DECLARE @errcount int
SET @chkcnt = 0
SET @errcount = 0
SET @chkcnt = (SELECT COUNT(*) FROM dbo.userMemberDB WITH(NOLOCK) WHERE accountIDX=@accountIDX)
IF (@chkcnt = 0)
BEGIN
SET @errcount = -2
SELECT @errcount AS 'returnVal'
RETURN
END
SET @renspoint = (SELECT renspoint FROM userGameDB WITH(NOLOCK) WHERE accountIDX=@accountIDX)
SELECT @renspoint AS factionPoint
DELETE FROM dbo.userLeagueTeamMemberDB
WHERE idx=@idx
AND accountIDX=@accountIDX
GO
IF OBJECT_ID('dbo.game_league_team_point_save') IS NOT NULL DROP PROCEDURE dbo.game_league_team_point_save
GO
CREATE PROCEDURE [dbo].[game_league_team_point_save]
(
@idx int
, @factionPoint int
, @currentRound tinyint
, @leaguePosition smallint
, @leagueStartPosition smallint
)
AS
SET NOCOUNT ON
UPDATE dbo.userLeagueTeamDB
SET leaguePosition = @leaguePosition,
currentRound = @currentRound,
factionPoint = @factionPoint,
leagueStartPosition = @leagueStartPosition
WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_factiontype_save') IS NOT NULL DROP PROCEDURE dbo.game_factiontype_save
GO
CREATE PROCEDURE [dbo].[game_factiontype_save]
(
@accountIDX int
, @factionType int
)
AS
SET NOCOUNT ON
UPDATE dbo.userGameDB
SET regionType=@factionType
WHERE accountIDX=@accountIDX
GO
IF OBJECT_ID('dbo.game_league_winner_history') IS NOT NULL DROP PROCEDURE dbo.game_league_winner_history
GO
CREATE PROCEDURE [dbo].[game_league_winner_history]
(
@leagueTitle nvarchar(20)
, @startDate int
, @endDate int
, @teamIDX int
, @teamName nvarchar(20)
, @factionName nvarchar(20)
, @factionType int
)
AS
SET NOCOUNT ON
IF (NOT EXISTS(SELECT * FROM dbo.userLeagueWinnerHistoryDB WHERE startDate=@startDate AND teamIDX > 0))
BEGIN
INSERT INTO dbo.userLeagueWinnerHistoryDB (
leagueTitle, startDate, endDate, teamIDX, teamName, factionName, factionType, regDate
)
VALUES (
@leagueTitle, @startDate, @endDate, @teamIDX, @teamName, @factionName, @factionType, GETDATE()
)
IF (@teamIDX > 0)
BEGIN
EXEC game_league_winner_info_add @startDate, @teamIDX
END
END
GO
IF OBJECT_ID('dbo.game_league_reward_add') IS NOT NULL DROP PROCEDURE dbo.game_league_reward_add
GO
CREATE PROCEDURE [dbo].[game_league_reward_add]
(
@startDate int
, @leagueIDX int
, @leagueSize smallint
, @teamConst smallint
, @userConst smallint
)
AS
SET NOCOUNT ON
DECLARE @winnerFactionType INT
DECLARE @factionType TINYINT
DECLARE @winnerTeamIDX INT
DECLARE @winnerTeamCheerCount INT
DECLARE @cheerTotalCount decimal( 10, 2 )
DECLARE @rewardTeamCheer INT
DECLARE @rewardUserCheer float
DECLARE @DeveloperKIDX INT
SET @winnerFactionType = 0
SET @factionType = 0
SET @winnerTeamIDX = 0
-- 우승팀 진영 타입
IF (EXISTS(SELECT * FROM dbo.userLeagueWinnerHistoryDB WITH(NOLOCK) WHERE startDate=@startDate))
BEGIN
SELECT TOP 1 @winnerFactionType = factionType,
@winnerTeamIDX = teamIDX
FROM dbo.userLeagueWinnerHistoryDB WITH(NOLOCK)
WHERE startDate=@startDate
ORDER BY idx DESC
END
-- 기존 보상 데이터 삭제
DELETE FROM dbo.userLeagueRewardDB
WHERE regDate < LEFT(CONVERT(varchar(20), GETDATE(), 120), 10)
DELETE FROM dbo.userLeagueRewardCheerDB
WHERE regDate < LEFT(CONVERT(varchar(20), GETDATE(), 120), 10)
-- 대회 참여 유저 보상
INSERT INTO dbo.userLeagueRewardDB (
accountIDX, startDate, currentRound, userFactionType, winnerFactionType, rewardPeso, factionRank, factionPoint, regDate
)
SELECT a.accountIDX, @startDate, c.currentRound, b.regionType, @winnerFactionType,
CASE WHEN @winnerFactionType = b.regionType THEN CAST(b.renspoint * 50 AS INT)
WHEN @winnerFactionType <> b.regionType THEN CAST(b.renspoint AS INT)
ELSE CAST(b.renspoint AS INT) END AS rewardPeso,
0, b.renspoint, GETDATE()
FROM dbo.userLeagueTeamMemberDB a WITH(NOLOCK)
JOIN dbo.userGameDB b WITH(NOLOCK)
ON a.accountIDX = b.accountIDX
JOIN dbo.userLeagueTeamDB c WITH(NOLOCK)
ON a.teamIDX=c.idx
WHERE a.leagueIDX=@leagueIDX
AND a.joinType=1
-- 대회 참여 하지 않은 전체 유저 보상
INSERT INTO dbo.userLeagueRewardDB (
accountIDX, startDate, currentRound, userFactionType, winnerFactionType, rewardPeso, factionRank, factionPoint, regDate
)
SELECT accountIDX, @startDate, 0, regionType, @winnerFactionType,
CASE WHEN @winnerFactionType = regionType THEN CAST(renspoint * 50 AS INT)
WHEN @winnerFactionType <> regionType THEN CAST(renspoint AS INT)
ELSE CAST(renspoint AS INT) END AS rewardPeso,
0, renspoint, GETDATE()
FROM dbo.userGameDB WITH(NOLOCK)
WHERE renspoint > 0
AND accountIDX NOT IN (
SELECT accountIDX
FROM dbo.userLeagueRewardDB WITH(NOLOCK)
)
-- 대회 참여자만 업데이트
-- 위 쿼리에서 currentRound 값도 함께 업데이트 하지만 혹시 몰라 기존 쿼리 그냥 놔둠)
UPDATE B
SET currentRound = A.currentRound
FROM (
SELECT a.accountIDX, b.currentRound, @startDate AS startDate
FROM dbo.userLeagueTeamMemberDB a WITH(NOLOCK)
JOIN dbo.userLeagueTeamDB b WITH(NOLOCK)
ON a.teamIDX=b.idx
WHERE a.leagueIDX=@leagueIDX
AND b.leagueIDX=@leagueIDX
) A
JOIN dbo.userLeagueRewardDB B WITH(NOLOCK)
ON A.accountIDX = B.accountIDX
-- 진영 랭킹 업데이트
UPDATE B
SET factionRank = A.region
FROM (
SELECT accountIDX, region
FROM dbo.userRankingDB WITH(NOLOCK)
) A
JOIN dbo.userLeagueRewardDB B WITH(NOLOCK)
ON A.accountIDX = B.accountIDX
-- 진영 랭킹 0인 유저 업데이트
UPDATE dbo.userLeagueRewardDB
SET factionRank = 100000000
WHERE factionRank = 0
-- 응원 보상
IF (@winnerTeamIDX > 0)
BEGIN
-- 우승팀원 응원 보상
SET @winnerTeamCheerCount = (
SELECT cheerPoint
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
AND idx=@winnerTeamIDX
)
-- 우승팀 응원 보상
SET @cheerTotalCount = convert( numeric( 19,1 ), (
SELECT COUNT(*)
FROM dbo.userLeagueCheerDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
) )
SET @rewardUserCheer = ((@cheerTotalCount / @leagueSize) / @winnerTeamCheerCount * @userConst)
-- 1000 보다 작으면 1000 으로 고정. 수민씨 !
IF (@rewardUserCheer < 1000)
BEGIN
SET @rewardUserCheer = 1000
END
-- 100000만보다 크면 10만 고정. 수민씨 !
IF (@rewardUserCheer > 100000)
BEGIN
SET @rewardUserCheer = 100000
END
INSERT INTO dbo.userLeagueRewardCheerDB (
accountIDX, rewardPeso, startDate, flag, regDate
)
SELECT accountIDX, @rewardUserCheer, @startDate, 1, GETDATE()
FROM dbo.userLeagueCheerDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
AND teamIDX=@winnerTeamIDX
END
GO
IF OBJECT_ID('dbo.game_league_history_list') IS NOT NULL DROP PROCEDURE dbo.game_league_history_list
GO
CREATE PROCEDURE [dbo].[game_league_history_list]
(
@topN int
, @idx int
)
AS
SET NOCOUNT ON
SELECT TOP (@topN) idx, leagueTitle, startDate, teamName, factionType, factionName
FROM dbo.userLeagueWinnerHistoryDB WITH(NOLOCK)
WHERE idx > @idx
ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_league_winner_info') IS NOT NULL DROP PROCEDURE dbo.game_league_winner_info
GO
CREATE PROCEDURE [dbo].[game_league_winner_info]
(
@historyIDX int
)
AS
SET NOCOUNT ON
SELECT accountIDX, nickName, userLevel,
classType, classGender, classFace, classHair, classSkinColor, classHairColor, classUnderwear,
classGear1, classGear2, classGear3, classGear4
FROM dbo.userLeagueWinnerInfoDB WITH(NOLOCK)
WHERE historyIDX=@historyIDX
GO
IF OBJECT_ID('dbo.game_league_get_reward') IS NOT NULL DROP PROCEDURE dbo.game_league_get_reward
GO
CREATE PROCEDURE [dbo].[game_league_get_reward]
(
@accountIDX int
)
AS
SET NOCOUNT ON
SELECT idx, startDate, currentRound, userFactionType, winnerFactionType, rewardPeso, factionRank, factionPoint
FROM dbo.userLeagueRewardDB WITH(NOLOCK)
WHERE accountIDX = @accountIDX
GO
IF OBJECT_ID('dbo.game_league_reward_del') IS NOT NULL DROP PROCEDURE dbo.game_league_reward_del
GO
CREATE PROCEDURE [dbo].[game_league_reward_del]
(
@idx int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueRewardDB WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_league_user_list_add') IS NOT NULL DROP PROCEDURE dbo.game_league_user_list_add
GO
CREATE PROCEDURE [dbo].[game_league_user_list_add]
(
@accountIDX int
, @startDate int
, @endDate int
, @leagueType tinyint
, @leagueState tinyint
, @leagueName nvarchar(20)
, @maxRound smallint
, @bannerA int
, @bannerB int
, @modeType int
, @maxcount tinyint
, @autoType tinyint
, @recruitDate int
, @waitDate int
, @roundType1_playDate int
, @roundType2_playDate int
, @roundType3_playDate int
, @roundType4_playDate int
, @roundType5_playDate int
, @roundType6_playDate int
, @roundType7_playDate int
, @roundType8_playDate int
, @roundType9_playDate int
, @roundType10_playDate int
, @roundType11_playDate int
)
AS
SET NOCOUNT ON
BEGIN TRAN
DECLARE @leagueIDX int
DECLARE @infoIDX int
DECLARE @roundIDX int
INSERT INTO dbo.userLeagueListDB (
accountIDX, startDate, endDate, leagueType, leagueState, regDate
)
VALUES (
@accountIDX, @startDate, @endDate, @leagueType, @leagueState, GETDATE()
)
IF (@@ROWCOUNT = 0 or @@ERROR <> 0)
BEGIN
ROLLBACK TRAN
SET @leagueIDX = 0
SELECT @leagueIDX AS 'idx'
END
ELSE
BEGIN
SET @leagueIDX = SCOPE_IDENTITY()
END
INSERT INTO dbo.userLeagueInfoDB (
leagueIDX, leagueName, maxRound, bannerA, bannerB, modeType, maxcount, autoType, announce, recruitDate, waitDate, regDate
)
VALUES (
@leagueIDX, @leagueName, @maxRound, @bannerA, @bannerB, @modeType, @maxcount, @autoType, '', @recruitDate, @waitDate, GETDATE()
)
IF (@@ROWCOUNT = 0 or @@ERROR <> 0)
BEGIN
ROLLBACK TRAN
SET @infoIDX = 0
SELECT @infoIDX AS 'idx'
END
ELSE
BEGIN
SET @infoIDX = SCOPE_IDENTITY()
END
INSERT INTO dbo.userLeagueRoundDB (
infoIDX,
roundType1_playDate, roundType1_presentCode1, roundType1_presentCode2, roundType1_presentCode3, roundType1_presentCode4,
roundType2_playDate, roundType2_presentCode1, roundType2_presentCode2, roundType2_presentCode3, roundType2_presentCode4,
roundType3_playDate, roundType3_presentCode1, roundType3_presentCode2, roundType3_presentCode3, roundType3_presentCode4,
roundType4_playDate, roundType4_presentCode1, roundType4_presentCode2, roundType4_presentCode3, roundType4_presentCode4,
roundType5_playDate, roundType5_presentCode1, roundType5_presentCode2, roundType5_presentCode3, roundType5_presentCode4,
roundType6_playDate, roundType6_presentCode1, roundType6_presentCode2, roundType6_presentCode3, roundType6_presentCode4,
roundType7_playDate, roundType7_presentCode1, roundType7_presentCode2, roundType7_presentCode3, roundType7_presentCode4,
roundType8_playDate, roundType8_presentCode1, roundType8_presentCode2, roundType8_presentCode3, roundType8_presentCode4,
roundType9_playDate, roundType9_presentCode1, roundType9_presentCode2, roundType9_presentCode3, roundType9_presentCode4,
roundType10_playDate, roundType10_presentCode1, roundType10_presentCode2, roundType10_presentCode3, roundType10_presentCode4,
roundType11_playDate, roundType11_presentCode1, roundType11_presentCode2, roundType11_presentCode3, roundType11_presentCode4,
regDate
)
VALUES (
@infoIDX,
@roundType1_playDate, 0, 0, 0, 0,
@roundType2_playDate, 0, 0, 0, 0,
@roundType3_playDate, 0, 0, 0, 0,
@roundType4_playDate, 0, 0, 0, 0,
@roundType5_playDate, 0, 0, 0, 0,
@roundType6_playDate, 0, 0, 0, 0,
@roundType7_playDate, 0, 0, 0, 0,
@roundType8_playDate, 0, 0, 0, 0,
@roundType9_playDate, 0, 0, 0, 0,
@roundType10_playDate, 0, 0, 0, 0,
@roundType11_playDate, 0, 0, 0, 0,
GETDATE()
)
IF (@@ROWCOUNT = 0 or @@ERROR <> 0)
BEGIN
ROLLBACK TRAN
SET @roundIDX = 0
SELECT @roundIDX AS 'idx'
END
ELSE
BEGIN
COMMIT TRAN
SELECT @leagueIDX AS 'idx'
END
GO
IF OBJECT_ID('dbo.game_league_get_info') IS NOT NULL DROP PROCEDURE dbo.game_league_get_info
GO
CREATE PROCEDURE [dbo].[game_league_get_info]
(
@leagueIDX int
)
AS
SET NOCOUNT ON
SELECT idx, leagueName, maxRound, bannerA, bannerB, modeType, maxcount, autoType, announce, recruitDate, waitDate
FROM dbo.userLeagueInfoDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
GO
IF OBJECT_ID('dbo.game_league_round_data') IS NOT NULL DROP PROCEDURE dbo.game_league_round_data
GO
CREATE PROCEDURE [dbo].[game_league_round_data]
(
@infoIDX int
)
AS
SET NOCOUNT ON
SELECT idx,
roundType1_playDate, roundType1_presentCode1, roundType1_presentCode2, roundType1_presentCode3, roundType1_presentCode4,
roundType2_playDate, roundType2_presentCode1, roundType2_presentCode2, roundType2_presentCode3, roundType2_presentCode4,
roundType3_playDate, roundType3_presentCode1, roundType3_presentCode2, roundType3_presentCode3, roundType3_presentCode4,
roundType4_playDate, roundType4_presentCode1, roundType4_presentCode2, roundType4_presentCode3, roundType4_presentCode4,
roundType5_playDate, roundType5_presentCode1, roundType5_presentCode2, roundType5_presentCode3, roundType5_presentCode4,
roundType6_playDate, roundType6_presentCode1, roundType6_presentCode2, roundType6_presentCode3, roundType6_presentCode4,
roundType7_playDate, roundType7_presentCode1, roundType7_presentCode2, roundType7_presentCode3, roundType7_presentCode4,
roundType8_playDate, roundType8_presentCode1, roundType8_presentCode2, roundType8_presentCode3, roundType8_presentCode4,
roundType9_playDate, roundType9_presentCode1, roundType9_presentCode2, roundType9_presentCode3, roundType9_presentCode4,
roundType10_playDate, roundType10_presentCode1, roundType10_presentCode2, roundType10_presentCode3, roundType10_presentCode4,
roundType11_playDate, roundType11_presentCode1, roundType11_presentCode2, roundType11_presentCode3, roundType11_presentCode4
FROM dbo.userLeagueRoundDB WITH(NOLOCK)
WHERE infoIDX=@infoIDX
GO
IF OBJECT_ID('dbo.game_league_auth_add') IS NOT NULL DROP PROCEDURE dbo.game_league_auth_add
GO
CREATE PROCEDURE [dbo].[game_league_auth_add]
(
@leagueIDX int
, @accountIDX int
)
AS
SET NOCOUNT ON
INSERT INTO dbo.userLeagueAuthDB (leagueIDX, accountIDX, regDate)
VALUES (@leagueIDX, @accountIDX, GETDATE())
GO
IF OBJECT_ID('dbo.game_league_auth_list') IS NOT NULL DROP PROCEDURE dbo.game_league_auth_list
GO
CREATE PROCEDURE [dbo].[game_league_auth_list]
(
@topN int
, @idx int
, @leagueIDX int
)
AS
SET NOCOUNT ON
SELECT TOP (@topN) a.accountIDX, b.nickName, c.userLevel
FROM dbo.userLeagueAuthDB a WITH(NOLOCK)
JOIN userMemberDB b WITH(NOLOCK)
ON a.accountIDX = b.accountIDX
JOIN userGameDB c WITH(NOLOCK)
ON a.accountIDX = c.accountIDX
WHERE idx > @idx
AND leagueIDX=@leagueIDX
ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_league_info_save') IS NOT NULL DROP PROCEDURE dbo.game_league_info_save
GO
CREATE PROCEDURE [dbo].[game_league_info_save]
(
@idx int
, @announce nvarchar(512)
, @recruitDate int
, @waitDate int
)
AS
SET NOCOUNT ON
UPDATE dbo.userLeagueInfoDB
SET announce = @announce
, recruitDate = @recruitDate
, waitDate = @waitDate
WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_league_round_save') IS NOT NULL DROP PROCEDURE dbo.game_league_round_save
GO
CREATE PROCEDURE [dbo].[game_league_round_save]
(
@idx int
, @roundType1_playDate int
, @roundType1_presentCode1 int
, @roundType1_presentCode2 int
, @roundType1_presentCode3 int
, @roundType1_presentCode4 int
, @roundType2_playDate int
, @roundType2_presentCode1 int
, @roundType2_presentCode2 int
, @roundType2_presentCode3 int
, @roundType2_presentCode4 int
, @roundType3_playDate int
, @roundType3_presentCode1 int
, @roundType3_presentCode2 int
, @roundType3_presentCode3 int
, @roundType3_presentCode4 int
, @roundType4_playDate int
, @roundType4_presentCode1 int
, @roundType4_presentCode2 int
, @roundType4_presentCode3 int
, @roundType4_presentCode4 int
, @roundType5_playDate int
, @roundType5_presentCode1 int
, @roundType5_presentCode2 int
, @roundType5_presentCode3 int
, @roundType5_presentCode4 int
, @roundType6_playDate int
, @roundType6_presentCode1 int
, @roundType6_presentCode2 int
, @roundType6_presentCode3 int
, @roundType6_presentCode4 int
, @roundType7_playDate int
, @roundType7_presentCode1 int
, @roundType7_presentCode2 int
, @roundType7_presentCode3 int
, @roundType7_presentCode4 int
, @roundType8_playDate int
, @roundType8_presentCode1 int
, @roundType8_presentCode2 int
, @roundType8_presentCode3 int
, @roundType8_presentCode4 int
, @roundType9_playDate int
, @roundType9_presentCode1 int
, @roundType9_presentCode2 int
, @roundType9_presentCode3 int
, @roundType9_presentCode4 int
, @roundType10_playDate int
, @roundType10_presentCode1 int
, @roundType10_presentCode2 int
, @roundType10_presentCode3 int
, @roundType10_presentCode4 int
, @roundType11_playDate int
, @roundType11_presentCode1 int
, @roundType11_presentCode2 int
, @roundType11_presentCode3 int
, @roundType11_presentCode4 int
)
AS
SET NOCOUNT ON
UPDATE dbo.userLeagueRoundDB
SET roundType1_playDate = @roundType1_playDate,
roundType1_presentCode1 = @roundType1_presentCode1,
roundType1_presentCode2 = @roundType1_presentCode2,
roundType1_presentCode3 = @roundType1_presentCode3,
roundType1_presentCode4 = @roundType1_presentCode4,
roundType2_playDate = @roundType2_playDate,
roundType2_presentCode1 = @roundType2_presentCode1,
roundType2_presentCode2 = @roundType2_presentCode2,
roundType2_presentCode3 = @roundType2_presentCode3,
roundType2_presentCode4 = @roundType2_presentCode4,
roundType3_playDate = @roundType3_playDate,
roundType3_presentCode1 = @roundType3_presentCode1,
roundType3_presentCode2 = @roundType3_presentCode2,
roundType3_presentCode3 = @roundType3_presentCode3,
roundType3_presentCode4 = @roundType3_presentCode4,
roundType4_playDate = @roundType4_playDate,
roundType4_presentCode1 = @roundType4_presentCode1,
roundType4_presentCode2 = @roundType4_presentCode2,
roundType4_presentCode3 = @roundType4_presentCode3,
roundType4_presentCode4 = @roundType4_presentCode4,
roundType5_playDate = @roundType5_playDate,
roundType5_presentCode1 = @roundType5_presentCode1,
roundType5_presentCode2 = @roundType5_presentCode2,
roundType5_presentCode3 = @roundType5_presentCode3,
roundType5_presentCode4 = @roundType5_presentCode4,
roundType6_playDate = @roundType6_playDate,
roundType6_presentCode1 = @roundType6_presentCode1,
roundType6_presentCode2 = @roundType6_presentCode2,
roundType6_presentCode3 = @roundType6_presentCode3,
roundType6_presentCode4 = @roundType6_presentCode4,
roundType7_playDate = @roundType7_playDate,
roundType7_presentCode1 = @roundType7_presentCode1,
roundType7_presentCode2 = @roundType7_presentCode2,
roundType7_presentCode3 = @roundType7_presentCode3,
roundType7_presentCode4 = @roundType7_presentCode4,
roundType8_playDate = @roundType8_playDate,
roundType8_presentCode1 = @roundType8_presentCode1,
roundType8_presentCode2 = @roundType8_presentCode2,
roundType8_presentCode3 = @roundType8_presentCode3,
roundType8_presentCode4 = @roundType8_presentCode4,
roundType9_playDate = @roundType9_playDate,
roundType9_presentCode1 = @roundType9_presentCode1,
roundType9_presentCode2 = @roundType9_presentCode2,
roundType9_presentCode3 = @roundType9_presentCode3,
roundType9_presentCode4 = @roundType9_presentCode4,
roundType10_playDate = @roundType10_playDate,
roundType10_presentCode1 = @roundType10_presentCode1,
roundType10_presentCode2 = @roundType10_presentCode2,
roundType10_presentCode3 = @roundType10_presentCode3,
roundType10_presentCode4 = @roundType10_presentCode4,
roundType11_playDate = @roundType11_playDate,
roundType11_presentCode1 = @roundType11_presentCode1,
roundType11_presentCode2 = @roundType11_presentCode2,
roundType11_presentCode3 = @roundType11_presentCode3,
roundType11_presentCode4 = @roundType11_presentCode4
WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_clover_info') IS NOT NULL DROP PROCEDURE dbo.game_clover_info
GO
CREATE PROCEDURE [dbo].[game_clover_info]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF ( EXISTS(SELECT * FROM dbo.userCloverDB WITH( NOLOCK ) WHERE accountIDX = @accountIDX) )
BEGIN
SELECT CloverCnt, LastChargeDate, RemainTime
FROM dbo.userCloverDB WITH( NOLOCK )
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
-- 클로버 정보가 없을때 insert
INSERT INTO dbo.userCloverDB( accountIDX, CloverCnt, LastChargeDate, RemainTime, regDate )
VALUES( @accountIDX, 0, 0, 0, GETDATE() )
-- return 0, 0, 0
SELECT 0 AS CloverCnt, 0 AS LastChargeDate, 0 AS RemainTime
END
GO
IF OBJECT_ID('dbo.game_clover_info_update') IS NOT NULL DROP PROCEDURE dbo.game_clover_info_update
GO
CREATE PROCEDURE [dbo].[game_clover_info_update]
(
@accountIDX int
, @CloverCount int
, @UpdateTime int
, @RemainTime smallint
)
AS
SET NOCOUNT ON
UPDATE dbo.userCloverDB
SET CloverCnt = @CloverCount,
LastChargeDate = @UpdateTime,
RemainTime = @RemainTime,
regDate = GETDATE()
WHERE accountIDX = @accountIDX
GO
IF OBJECT_ID('dbo.game_friend_clover_info_update') IS NOT NULL DROP PROCEDURE dbo.game_friend_clover_info_update
GO
CREATE PROCEDURE [dbo].[game_friend_clover_info_update]
(
@index int
, @sendCount int
, @sendDate int
, @receiveCount int
, @receiveDate int
, @BeforeReceiveCount int
)
AS
SET NOCOUNT ON
-- 로그아웃중인 친구에게 클로버를 보냈다.
UPDATE dbo.userFriendDB
SET sendCloverCnt = @sendCount,
sendCloverDate = @sendDate,
receiveCloverCnt = @receiveCount,
receiveCloverDate = @receiveDate,
receiveBCloverCnt = @BeforeReceiveCount
WHERE idx = @index
GO
IF OBJECT_ID('dbo.game_clover_friend_receive_save') IS NOT NULL DROP PROCEDURE dbo.game_clover_friend_receive_save
GO
CREATE PROCEDURE [dbo].[game_clover_friend_receive_save]
(
@UserIndex int
, @FriendIndex int
, @receiveDate int
, @receiveCount int
)
AS
SET NOCOUNT ON
DECLARE @Index INT
SET @Index = ISNULL((
SELECT idx
FROM dbo.userFriendDB WITH( NOLOCK )
WHERE userAccIDX = @FriendIndex
AND FriendAccIDX = @UserIndex
), 0 )
IF ( @Index > 0 )
BEGIN
-- update
UPDATE dbo.userFriendDB
SET receiveBCloverCnt = receiveBCloverCnt + @receiveCount,
receiveCloverDate = @receiveDate
WHERE idx = @Index
-- select
SELECT idx, userAccIDX, receiveCloverDate, receiveBCloverCnt
FROM dbo.userFriendDB WITH( nolock )
WHERE idx = @Index
END
ELSE
BEGIN
SELECT 0 as idx, 0 as userAccIDX, 0 as receiveCloverDate, 0 as receiveBCloverCnt
END
GO
IF OBJECT_ID('dbo.game_present_add_tool') IS NOT NULL DROP PROCEDURE dbo.game_present_add_tool
GO
CREATE PROCEDURE [dbo].[game_present_add_tool]
(
@sendNick varchar(20)
, @receiveNick varchar(20)
, @persentType smallint
, @value1 int
, @value2 int
, @value3 int
, @value4 int
, @msgType smallint
, @limitDate datetime
, @flag smallint
)
AS
SET NOCOUNT ON
DECLARE @sendIDX int, @receiveIDX int
SET @sendIDX = (
SELECT accountIDX
FROM dbo.userMemberDB WITH(NOLOCK)
WHERE nickName=@sendNick
)
SET @receiveIDX = (
SELECT accountIDX
FROM dbo.userMemberDB WITH(NOLOCK)
WHERE nickName=@receiveNick
)
SELECT @sendIDX = ISNULL(@sendIDX, 1105)
INSERT INTO userPresentDB (sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate)
VALUES (@sendIDX, @receiveIDX, @persentType, @value1, @value2, @value3, @value4, @msgType, @flag, @limitDate, GETDATE())
SELECT @receiveIDX AS recvIndex
GO
IF OBJECT_ID('dbo.game_league_delete') IS NOT NULL DROP PROCEDURE dbo.game_league_delete
GO
CREATE PROCEDURE [dbo].[game_league_delete]
(
@leagueIDX int
)
AS
SET NOCOUNT ON
DECLARE @infoIDX int
SET @infoIDX = ISNULL((SELECT idx FROM dbo.userLeagueInfoDB WITH(NOLOCK) WHERE leagueIDX=@leagueIDX), 0)
DELETE FROM dbo.userLeagueTeamMemberDB WHERE leagueIDX=@leagueIDX
DELETE FROM dbo.userLeagueTeamDB WHERE leagueIDX=@leagueIDX
DELETE FROM dbo.userLeagueInfoDB WHERE leagueIDX=@leagueIDX
DELETE FROM dbo.userLeagueAuthDB WHERE leagueIDX=@leagueIDX
DELETE FROM dbo.userLeagueRoundDB WHERE infoIDX=@infoIDX
DELETE FROM dbo.userLeagueListDB WHERE idx=@leagueIDX
GO
IF OBJECT_ID('dbo.game_league_backup_user') IS NOT NULL DROP PROCEDURE dbo.game_league_backup_user
GO
CREATE PROCEDURE [dbo].[game_league_backup_user]
(
@leagueIDX int
)
AS
SET NOCOUNT ON
DECLARE @startDate int
DECLARE @infoIDX int
SET @startDate = ISNULL((
SELECT startDate
FROM dbo.userLeagueListDB WITH(NOLOCK)
WHERE idx=@leagueIDX
), 0)
SET @infoIDX = ISNULL((
SELECT idx
FROM dbo.userLeagueInfoDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
), 0)
-- 리그 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_list_user (
leagueIDX, accountIDX, startDate, endDate, leagueType, leagueState, regDate, insertDate
)
SELECT idx, accountIDX, startDate, endDate, leagueType, leagueState, regDate, GETDATE()
FROM dbo.userLeagueListDB WITH(NOLOCK)
WHERE idx=@leagueIDX
-- 리그 팀 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_team_user (
teamIDX, leagueIDX, startDate, teamName, leaderIDX, leagueStartPosition, leaguePosition, maxcount, cheerPoint,
currentRound, factionPoint, factionType, regDate, insertDate
)
SELECT idx, leagueIDX, @startDate, teamName, leaderIDX, leagueStartPosition, leaguePosition, maxcount, cheerPoint,
currentRound, factionPoint, factionType, regDate, GETDATE()
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
-- 리그 팀원 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_team_member_user (
teamMemberIDX, startDate, accountIDX, leagueIDX, teamIDX, joinType, regDate, insertDate
)
SELECT idx, @startDate, accountIDX, leagueIDX, teamIDX, joinType, regDate, GETDATE()
FROM dbo.userLeagueTeamMemberDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
-- 대회 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_info_user (
infoIDX, leagueIDX, leagueName, maxRound, bannerA, bannerB, modeType, maxcount, autoType, announce, recruitDate, waitDate, regDate, insertDate
)
SELECT idx, leagueIDX, leagueName, maxRound, bannerA, bannerB, modeType, maxcount, autoType, announce, recruitDate, waitDate, regDate, GETDATE()
FROM dbo.userLeagueInfoDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
-- 대회 라운드 정보 백업
INSERT INTO LosaGame_log.dbo.log_league_round_user (
roundIDX, infoIDX,
roundType1_playDate, roundType1_presentCode1, roundType1_presentCode2, roundType1_presentCode3, roundType1_presentCode4,
roundType2_playDate, roundType2_presentCode1, roundType2_presentCode2, roundType2_presentCode3, roundType2_presentCode4,
roundType3_playDate, roundType3_presentCode1, roundType3_presentCode2, roundType3_presentCode3, roundType3_presentCode4,
roundType4_playDate, roundType4_presentCode1, roundType4_presentCode2, roundType4_presentCode3, roundType4_presentCode4,
roundType5_playDate, roundType5_presentCode1, roundType5_presentCode2, roundType5_presentCode3, roundType5_presentCode4,
roundType6_playDate, roundType6_presentCode1, roundType6_presentCode2, roundType6_presentCode3, roundType6_presentCode4,
roundType7_playDate, roundType7_presentCode1, roundType7_presentCode2, roundType7_presentCode3, roundType7_presentCode4,
roundType8_playDate, roundType8_presentCode1, roundType8_presentCode2, roundType8_presentCode3, roundType8_presentCode4,
roundType9_playDate, roundType9_presentCode1, roundType9_presentCode2, roundType9_presentCode3, roundType9_presentCode4,
roundType10_playDate, roundType10_presentCode1, roundType10_presentCode2, roundType10_presentCode3, roundType10_presentCode4,
roundType11_playDate, roundType11_presentCode1, roundType11_presentCode2, roundType11_presentCode3, roundType11_presentCode4,
regDate, insertDate
)
SELECT idx, infoIDX,
roundType1_playDate, roundType1_presentCode1, roundType1_presentCode2, roundType1_presentCode3, roundType1_presentCode4,
roundType2_playDate, roundType2_presentCode1, roundType2_presentCode2, roundType2_presentCode3, roundType2_presentCode4,
roundType3_playDate, roundType3_presentCode1, roundType3_presentCode2, roundType3_presentCode3, roundType3_presentCode4,
roundType4_playDate, roundType4_presentCode1, roundType4_presentCode2, roundType4_presentCode3, roundType4_presentCode4,
roundType5_playDate, roundType5_presentCode1, roundType5_presentCode2, roundType5_presentCode3, roundType5_presentCode4,
roundType6_playDate, roundType6_presentCode1, roundType6_presentCode2, roundType6_presentCode3, roundType6_presentCode4,
roundType7_playDate, roundType7_presentCode1, roundType7_presentCode2, roundType7_presentCode3, roundType7_presentCode4,
roundType8_playDate, roundType8_presentCode1, roundType8_presentCode2, roundType8_presentCode3, roundType8_presentCode4,
roundType9_playDate, roundType9_presentCode1, roundType9_presentCode2, roundType9_presentCode3, roundType9_presentCode4,
roundType10_playDate, roundType10_presentCode1, roundType10_presentCode2, roundType10_presentCode3, roundType10_presentCode4,
roundType11_playDate, roundType11_presentCode1, roundType11_presentCode2, roundType11_presentCode3, roundType11_presentCode4,
regDate, GETDATE()
FROM dbo.userLeagueRoundDB WITH(NOLOCK)
WHERE infoIDX=@infoIDX
GO
IF OBJECT_ID('dbo.game_league_user_reward_add') IS NOT NULL DROP PROCEDURE dbo.game_league_user_reward_add
GO
CREATE PROCEDURE [dbo].[game_league_user_reward_add]
(
@leagueIDX int
)
AS
SET NOCOUNT ON
DECLARE @rt1A int, @rt1B int, @rt1C int, @rt1D int
DECLARE @rt2A int, @rt2B int, @rt2C int, @rt2D int
DECLARE @rt3A int, @rt3B int, @rt3C int, @rt3D int
DECLARE @rt4A int, @rt4B int, @rt4C int, @rt4D int
DECLARE @rt5A int, @rt5B int, @rt5C int, @rt5D int
DECLARE @rt6A int, @rt6B int, @rt6C int, @rt6D int
DECLARE @rt7A int, @rt7B int, @rt7C int, @rt7D int
DECLARE @rt8A int, @rt8B int, @rt8C int, @rt8D int
DECLARE @rt9A int, @rt9B int, @rt9C int, @rt9D int
DECLARE @rt10A int, @rt10B int, @rt10C int, @rt10D int
DECLARE @rt11A int, @rt11B int, @rt11C int, @rt11D int
DECLARE @infoIDX int
DECLARE @maxRound smallint
DECLARE @sendNickName nvarchar(20)
DECLARE @leagueName nvarchar(20)
DECLARE @teamList table (accountIDX int, currentRound tinyint)
-- 보상 받을 전체 유저
INSERT INTO @teamList (accountIDX, currentRound)
SELECT a.accountIDX, b.currentRound
FROM dbo.userLeagueTeamMemberDB a WITH(NOLOCK)
JOIN dbo.userLeagueTeamDB b WITH(NOLOCK)
ON a.teamIDX=b.idx
WHERE a.leagueIDX=@leagueIDX
AND b.leagueIDX=@leagueIDX
AND a.joinType=1
SELECT @infoIDX = idx,
@leagueName = leagueName,
@maxRound = maxRound
FROM dbo.userLeagueInfoDB WITH(NOLOCK)
WHERE leagueIDX=@leagueIDX
SET @sendNickName = ISNULL((
SELECT b.nickName
FROM dbo.userLeagueListDB a WITH(NOLOCK)
JOIN dbo.userMemberDB b WITH(NOLOCK)
ON a.accountIDX=b.accountIDX
WHERE a.idx=@leagueIDX
), '개발자K')
SELECT @rt1A = roundType1_presentCode1,
@rt1B = roundType1_presentCode2,
@rt1C = roundType1_presentCode3,
@rt1D = roundType1_presentCode4,
@rt2A = roundType2_presentCode1,
@rt2B = roundType2_presentCode2,
@rt2C = roundType2_presentCode3,
@rt2D = roundType2_presentCode4,
@rt3A = roundType3_presentCode1,
@rt3B = roundType3_presentCode2,
@rt3C = roundType3_presentCode3,
@rt3D = roundType3_presentCode4,
@rt4A = roundType4_presentCode1,
@rt4B = roundType4_presentCode2,
@rt4C = roundType4_presentCode3,
@rt4D = roundType4_presentCode4,
@rt5A = roundType5_presentCode1,
@rt5B = roundType5_presentCode2,
@rt5C = roundType5_presentCode3,
@rt5D = roundType5_presentCode4,
@rt6A = roundType6_presentCode1,
@rt6B = roundType6_presentCode2,
@rt6C = roundType6_presentCode3,
@rt6D = roundType6_presentCode4,
@rt7A = roundType7_presentCode1,
@rt7B = roundType7_presentCode2,
@rt7C = roundType7_presentCode3,
@rt7D = roundType7_presentCode4,
@rt8A = roundType8_presentCode1,
@rt8B = roundType8_presentCode2,
@rt8C = roundType8_presentCode3,
@rt8D = roundType8_presentCode4,
@rt9A = roundType9_presentCode1,
@rt9B = roundType9_presentCode2,
@rt9C = roundType9_presentCode3,
@rt9D = roundType9_presentCode4,
@rt10A = roundType10_presentCode1,
@rt10B = roundType10_presentCode2,
@rt10C = roundType10_presentCode3,
@rt10D = roundType10_presentCode4,
@rt11A = roundType11_presentCode1,
@rt11B = roundType11_presentCode2,
@rt11C = roundType11_presentCode3,
@rt11D = roundType11_presentCode4
FROM dbo.userLeagueRoundDB WITH(NOLOCK)
WHERE infoIDX=@infoIDX
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=1))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt1A, @rt1B, @rt1C, @rt1D, GETDATE()
FROM @teamList
WHERE currentRound=1
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=2))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt2A, @rt2B, @rt2C, @rt2D, GETDATE()
FROM @teamList
WHERE currentRound=2
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=3))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt3A, @rt3B, @rt3C, @rt3D, GETDATE()
FROM @teamList
WHERE currentRound=3
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=4))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt4A, @rt4B, @rt4C, @rt4D, GETDATE()
FROM @teamList
WHERE currentRound=4
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=5))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt5A, @rt5B, @rt5C, @rt5D, GETDATE()
FROM @teamList
WHERE currentRound=5
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=6))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt6A, @rt6B, @rt6C, @rt6D, GETDATE()
FROM @teamList
WHERE currentRound=6
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=7))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt7A, @rt7B, @rt7C, @rt7D, GETDATE()
FROM @teamList
WHERE currentRound=7
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=8))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt8A, @rt8B, @rt8C, @rt8D, GETDATE()
FROM @teamList
WHERE currentRound=8
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=9))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt9A, @rt9B, @rt9C, @rt9D, GETDATE()
FROM @teamList
WHERE currentRound=9
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=10))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt10A, @rt10B, @rt10C, @rt10D, GETDATE()
FROM @teamList
WHERE currentRound=10
END
IF (EXISTS(SELECT * FROM @teamList WHERE currentRound=11))
BEGIN
INSERT INTO dbo.userLeagueRewardUserDB (
accountIDX, leagueIDX, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4, regDate
)
SELECT accountIDX, @leagueIDX, @sendNickName, @leagueName, currentRound, @maxRound, @rt11A, @rt11B, @rt11C, @rt11D, GETDATE()
FROM @teamList
WHERE currentRound=11
END
GO
IF OBJECT_ID('dbo.game_league_user_reward_get_data') IS NOT NULL DROP PROCEDURE dbo.game_league_user_reward_get_data
GO
CREATE PROCEDURE [dbo].[game_league_user_reward_get_data]
(
@accountIDX int
)
AS
SET NOCOUNT ON
SELECT idx, sendNickname, leagueName, roundnum, maxRound, presentCode1, presentCode2, presentCode3, presentCode4
FROM dbo.userLeagueRewardUserDB WITH(NOLOCK)
WHERE accountIDX=@accountIDX
AND regDate >= DATEADD(d, -14, GETDATE())
GO
IF OBJECT_ID('dbo.game_league_user_reward_del') IS NOT NULL DROP PROCEDURE dbo.game_league_user_reward_del
GO
CREATE PROCEDURE [dbo].[game_league_user_reward_del]
(
@idx int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueRewardUserDB
WHERE idx=@idx
GO
IF OBJECT_ID('dbo.game_event_bingo_number_add') IS NOT NULL DROP PROCEDURE dbo.game_event_bingo_number_add
GO
create PROCEDURE [dbo].[game_event_bingo_number_add]
(
@accountIDX int
, @num1 tinyint
, @num2 tinyint
, @num3 tinyint
, @num4 tinyint
, @num5 tinyint
, @num6 tinyint
, @num7 tinyint
, @num8 tinyint
, @num9 tinyint
, @num10 tinyint
, @num11 tinyint
, @num12 tinyint
, @num13 tinyint
, @num14 tinyint
, @num15 tinyint
, @num16 tinyint
, @num17 tinyint
, @num18 tinyint
, @num19 tinyint
, @num20 tinyint
, @num21 tinyint
, @num22 tinyint
, @num23 tinyint
, @num24 tinyint
, @num25 tinyint
)
AS
SET NOCOUNT ON
IF( EXISTS( SELECT * FROM dbo.userEventBingo_Number WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
RETURN
END
ELSE
BEGIN
INSERT
INTO dbo.userEventBingo_Number( accountidx, number1, number2, number3, number4, number5
, number6, number7, number8, number9, number10
, number11, number12, number13, number14, number15
, number16, number17, number18, number19, number20
, number21, number22, number23, number24, number25, regDate )
VALUES ( @accountIDX, @num1, @num2, @num3, @num4, @num5
, @num6, @num7, @num8, @num9, @num10
, @num11, @num12, @num13, @num14, @num15
, @num16, @num17, @num18, @num19, @num20
, @num21, @num22, @num23, @num24, @num25, GETDATE() )
END
GO
IF OBJECT_ID('dbo.game_event_bingo_present_add') IS NOT NULL DROP PROCEDURE dbo.game_event_bingo_present_add
GO
create PROCEDURE [dbo].[game_event_bingo_present_add]
(
@accountIDX int
, @item1 tinyint
, @item2 tinyint
, @item3 tinyint
, @item4 tinyint
, @item5 tinyint
, @item6 tinyint
, @item7 tinyint
, @item8 tinyint
, @item9 tinyint
, @item10 tinyint
, @item11 tinyint
, @item12 tinyint
, @item13 tinyint
)
AS
SET NOCOUNT ON
IF( EXISTS( SELECT * FROM dbo.userEventBingo_Present WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
RETURN
END
ELSE
BEGIN
INSERT
INTO dbo.userEventBingo_Present( accountidx, item1, item2, item3, item4, item5, item6
, item7, item8, item9, item10, item11, item12, item13, regDate )
VALUES ( @accountIDX, @item1, @item2, @item3, @item4, @item5, @item6
, @item7, @item8, @item9, @item10, @item11, @item12, @item13, GETDATE() )
END
GO
IF OBJECT_ID('dbo.game_event_bingo_number_get') IS NOT NULL DROP PROCEDURE dbo.game_event_bingo_number_get
GO
create PROCEDURE [dbo].[game_event_bingo_number_get]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF ( EXISTS(SELECT * FROM dbo.userEventBingo_Number WITH( NOLOCK ) WHERE accountIDX = @accountIDX) )
BEGIN
SELECT number1, number2, number3, number4, number5
, number6, number7, number8, number9, number10
, number11, number12, number13, number14, number15
, number16, number17, number18, number19, number20
, number21, number22, number23, number24, number25
FROM dbo.userEventBingo_Number WITH( NOLOCK )
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
-- return 0, 0, 0, ... 0
SELECT 0 AS number1, 0 AS number2, 0 AS number3, 0 AS number4, 0 AS number5
, 0 AS number6, 0 AS number7, 0 AS number8, 0 AS number9, 0 AS number10
, 0 AS number11, 0 AS number12, 0 AS number13, 0 AS number14, 0 AS number15
, 0 AS number16, 0 AS number17, 0 AS number18, 0 AS number19, 0 AS number20
, 0 AS number21, 0 AS number22, 0 AS number23, 0 AS number24, 0 AS number25
END
GO
IF OBJECT_ID('dbo.game_event_bingo_present_get') IS NOT NULL DROP PROCEDURE dbo.game_event_bingo_present_get
GO
create PROCEDURE [dbo].[game_event_bingo_present_get]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF ( EXISTS(SELECT * FROM dbo.userEventBingo_Present WITH( NOLOCK ) WHERE accountIDX = @accountIDX) )
BEGIN
SELECT item1, item2, item3, item4, item5, item6
, item7, item8, item9, item10, item11, item12, item13
FROM dbo.userEventBingo_Present WITH( NOLOCK )
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
-- return 0, 0, 0, ... 0
SELECT 0 AS item1, 0 AS item2, 0 AS item3, 0 AS item4, 0 AS item5, 0 AS item6
, 0 AS item7, 0 AS item8, 0 AS item9, 0 AS item10, 0 AS item11, 0 AS item12, 0 AS item13
END
GO
IF OBJECT_ID('dbo.game_event_bingo_number_save') IS NOT NULL DROP PROCEDURE dbo.game_event_bingo_number_save
GO
create PROCEDURE [dbo].[game_event_bingo_number_save]
(
@accountIDX int
, @num1 tinyint
, @num2 tinyint
, @num3 tinyint
, @num4 tinyint
, @num5 tinyint
, @num6 tinyint
, @num7 tinyint
, @num8 tinyint
, @num9 tinyint
, @num10 tinyint
, @num11 tinyint
, @num12 tinyint
, @num13 tinyint
, @num14 tinyint
, @num15 tinyint
, @num16 tinyint
, @num17 tinyint
, @num18 tinyint
, @num19 tinyint
, @num20 tinyint
, @num21 tinyint
, @num22 tinyint
, @num23 tinyint
, @num24 tinyint
, @num25 tinyint
)
AS
SET NOCOUNT ON
UPDATE dbo.userEventBingo_Number
SET number1 = @num1, number2 = @num2, number3 = @num3, number4 = @num4, number5 = @num5
, number6 = @num6, number7 = @num7, number8 = @num8, number9 = @num9, number10 = @num10
, number11 = @num11, number12 = @num12, number13 = @num13, number14 = @num14, number15 = @num15
, number16 = @num16, number17 = @num17, number18 = @num18, number19 = @num19, number20 = @num20
, number21 = @num21, number22 = @num22, number23 = @num23, number24 = @num24, number25 = @num25
WHERE accountIDX = @accountIDX
GO
IF OBJECT_ID('dbo.game_event_bingo_present_save') IS NOT NULL DROP PROCEDURE dbo.game_event_bingo_present_save
GO
create PROCEDURE [dbo].[game_event_bingo_present_save]
(
@accountIDX int
, @item1 tinyint
, @item2 tinyint
, @item3 tinyint
, @item4 tinyint
, @item5 tinyint
, @item6 tinyint
, @item7 tinyint
, @item8 tinyint
, @item9 tinyint
, @item10 tinyint
, @item11 tinyint
, @item12 tinyint
, @item13 tinyint
)
AS
SET NOCOUNT ON
UPDATE dbo.userEventBingo_Present
SET item1 = @item1, item2 = @item2, item3 = @item3, item4 = @item4, item5 = @item5, item6 = @item6
, item7 = @item7, item8 = @item8, item9 = @item9, item10 = @item10, item11 = @item11, item12 = @item12, item13 = @item13
WHERE accountIDX = @accountIDX
GO
IF OBJECT_ID('dbo.game_relative_reward_get_data') IS NOT NULL DROP PROCEDURE dbo.game_relative_reward_get_data
GO
create PROCEDURE [dbo].[game_relative_reward_get_data]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF ( EXISTS( SELECT * FROM dbo.userRelativeLevelDB WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
SELECT weekend_BackupExp, init_time, reward_State
FROM dbo.userRelativeLevelDB WITH( NOLOCK )
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
-- return 0, 0, 0, ... 0
SELECT 0 AS weekend_BackupExp, 0 AS init_time, 0 AS reward_State
END
GO
IF OBJECT_ID('dbo.game_relative_reward_save') IS NOT NULL DROP PROCEDURE dbo.game_relative_reward_save
GO
create PROCEDURE [dbo].[game_relative_reward_save]
(
@accountIDX int
, @reward tinyint
)
AS
SET NOCOUNT ON
IF( EXISTS( SELECT * FROM dbo.userRelativeLevelDB WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
UPDATE dbo.userRelativeLevelDB
SET reward_State = @reward
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
RETURN
END
GO
IF OBJECT_ID('dbo.game_relative_ranking_week') IS NOT NULL DROP PROCEDURE dbo.game_relative_ranking_week
GO
CREATE PROCEDURE [dbo].[game_relative_ranking_week]
(
@init_time int
, @rate int
)
AS
SET NOCOUNT ON
-- 임시테이블 선언.
DECLARE @temp_table TABLE
(
ranking INT
, accountIDX INT
, tempLevel INT
)
--------------------------------------------------------------------
-- 0. 프로시져 시작 할때 userGameDB 백업 하자.
--------------------------------------------------------------------
DECLARE @tblName varchar(256), @sql varchar(256)
SET @tblName = '[Backup].dbo.LosaGame_' + convert( varchar(16), @init_time ) + '_userGameDB'
--SET @tblName = 'LosaGameQA.dbo.LosaGame_' + convert( varchar(16), @init_time ) + '_userGameDB'
SET @sql = 'SELECT * INTO ' + @tblName + ' FROM dbo.userGameDB WITH( NOLOCK )'
EXECUTE( @sql )
--------------------------------------------------------------------
-- step 1. 탈퇴 유저 / 장성이 된 유저
--------------------------------------------------------------------
-- 탈퇴 유저 : userRelativeLevelDB 삭제.
DELETE
FROM dbo.userRelativeLevelDB
WHERE accountIDX NOT IN
(
SELECT accountIDX
FROM dbo.userGameDB WITH( NOLOCK )
WHERE userLevel >= 50
)
-- 장성이 된 유저 : userRelativeLevelDB 추가.
INSERT INTO dbo.userRelativeLevelDB( accountIDX, weekend_BackupLevel, weekend_BackupExp, init_time, reward_State, regDate )
(
SELECT accountIDX, userLevel, userExp, @init_time, 1, GETDATE()
FROM dbo.userGameDB WITH( NOLOCK )
WHERE userLevel >= 50
AND accountIDX NOT IN
(
SELECT accountIDX
FROM dbo.userRelativeLevelDB WITH( NOLOCK )
)
)
--------------------------------------------------------------------
-- step 2. 유저 현재 레벨 백업( update )
-- userRelativeLevelDB.weekend_BackupLevel
--------------------------------------------------------------------
UPDATE dbo.userRelativeLevelDB
SET weekend_BackupLevel = UG.userLevel
FROM dbo.userRelativeLevelDB URL WITH( NOLOCK )
JOIN dbo.userGameDB UG WITH( NOLOCK )
ON UG.accountIDX = URL.accountIDX
WHERE UG.userLevel >= 50
--------------------------------------------------------------------
-- step 3. 순위 ( userGameDB.userExp )
--------------------------------------------------------------------
-- 임시 테이블에 장성급 유저를 저장.
INSERT INTO @temp_table( ranking, accountIDX, tempLevel )
(
SELECT ROW_NUMBER() OVER ( ORDER BY userExp DESC ), accountIDX, 50
FROM dbo.userGameDB WITH( NOLOCK )
WHERE userLevel >= 50
)
DECLARE @userCount INT
SET @userCount = 0;
-- 장성급 유저 수 구하기.
SELECT @userCount = COUNT(*) FROM @temp_table
-- 변수 선언.
DECLARE @5star INT, @4star INT, @3star INT, @2star INT
DECLARE @LoopCount INT, @LoopLevel INT
-- 장성 유저수
IF( @userCount <= 5 )
BEGIN
-- 5명 이하.
SET @2star = 30;
SET @LoopCount = 2;
SET @LoopLevel = 51; -- 2 star
END
ELSE IF( @userCount > 5 and @userCount <= 50 )
BEGIN
-- 6~50.
SET @3star = 5
SET @2star = 25;
SET @LoopCount = 3;
SET @LoopLevel = 52; -- 3 start
END
ELSE IF( @userCount > 50 and @userCount <= 80 )
BEGIN
-- 51~80.
SET @4star = 2
SET @3star = 3
SET @2star = 25;
SET @LoopCount = 4;
SET @LoopLevel = 53; -- 4 start
END
ELSE IF( @userCount > 80 )
BEGIN
-- 81.
SET @5star = 1
SET @4star = 2
SET @3star = 3
SET @2star = 24;
SET @LoopCount = 5;
SET @LoopLevel = 54; -- 5 start
END
-- 반복문
WHILE( @LoopCount > 0 )
BEGIN
IF( @LoopCount = 1 )
BEGIN
-- 소장은 나머지 인원이 된다.
UPDATE @temp_table
SET tempLevel = @LoopLevel
WHERE tempLevel = 0
END
ELSE
BEGIN
-- %로 인원 계산하기 위해
DECLARE @percent INT;
SET @percent =
CASE
WHEN ( @LoopCount = 2 ) THEN @2star
WHEN ( @LoopCount = 3 ) THEN @3star
WHEN ( @LoopCount = 4 ) THEN @4star
WHEN ( @LoopCount = 5 ) THEN @5star
END
DECLARE @userCNT INT;
SET @userCNT = 0;
-- 소수점 버림.
SELECT @userCNT = FLOOR( @userCount * 1. * @percent / 100 );
IF( @userCNT > 0 )
BEGIN
-- 임시테이블 update.
UPDATE @temp_table
SET tempLevel = @LoopLevel
WHERE accountIDX IN
(
-- 경험치 위에서 부터 순위
SELECT TOP( @userCNT ) accountIDX
FROM @temp_table
WHERE tempLevel = 0
ORDER BY ranking
)
END
END
SET @LoopLevel = @LoopLevel - 1;
SET @LoopCount = @LoopCount - 1;
END
-- 최종업데이트 : 임시테이블의 tempLevel 을 userGameDB.userLevel 에 업데이트
UPDATE b
SET userLevel = a.tempLevel
FROM
(
SELECT * FROM @temp_table
) a
join userGameDB b WITH( NOLOCK )
ON a.accountIDX = b.accountIDX
WHERE b.userlevel >= 50
--------------------------------------------------------------------
-- 4. 경험치 비교( userGameDB.userExp <>
-- userRelativeLevelDB.weekend_BackupExp
-- userRelativeLevelDB.reward_State 갱신
--------------------------------------------------------------------
-- 플레이 한 유저
UPDATE dbo.userRelativeLevelDB
SET reward_State = 1
WHERE accountIDX in
(
SELECT URL.accountIDX
FROM dbo.userRelativeLevelDB URL WITH( NOLOCK ),
dbo.userGameDB UG WITH( NOLOCK )
WHERE UG.userLevel >= 50
AND UG.userExp <> URL.weekend_BackupExp
)
-- 플레이 안한 유저는 0으로
UPDATE dbo.userRelativeLevelDB
SET reward_State = 0
WHERE accountIDX in
(
SELECT URL.accountIDX
FROM dbo.userRelativeLevelDB URL WITH( NOLOCK ),
dbo.userGameDB UG WITH( NOLOCK )
WHERE UG.userLevel >= 50
AND UG.userExp = URL.weekend_BackupExp
)
--------------------------------------------------------------------
-- 5. 경험치 차감 userGameDB.userExp
-- : init_time 비교하여 중복 차감을 방지한다.
-- : 경험치 % 차감 계산시 소수점 자리는 버림으로 계산한다.
--------------------------------------------------------------------
UPDATE dbo.userGameDB
SET userExp = userExp - FLOOR( ( userExp * 1. * @rate ) / 100 )
WHERE userLevel >= 50
AND accountIDX in
(
SELECT accountIDX
FROM dbo.userRelativeLevelDB WITH( NOLOCK )
WHERE init_time <> @init_time
)
-- 바로 init_time 업데이트 해줘야 한다.
UPDATE dbo.userRelativeLevelDB
SET init_time = @init_time
WHERE accountIDX in
(
SELECT accountIDX
FROM dbo.userRelativeLevelDB WITH( NOLOCK )
WHERE init_time <> @init_time
)
--------------------------------------------------------------------
-- 6. 경험치 백업 userGameDB.userExp ->
-- userRelativeLevelDB.weekend_BackupExp
--------------------------------------------------------------------
UPDATE dbo.userRelativeLevelDB
SET weekend_BackupExp = UG.userExp
FROM dbo.userRelativeLevelDB URL WITH( NOLOCK )
JOIN dbo.userGameDB UG WITH( NOLOCK )
ON UG.accountIDX = URL.accountIDX
WHERE UG.userLevel >= 50
GO
IF OBJECT_ID('dbo.game_relative_add') IS NOT NULL DROP PROCEDURE dbo.game_relative_add
GO
create PROCEDURE [dbo].[game_relative_add]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF( EXISTS( SELECT * FROM dbo.userRelativeLevelDB WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO dbo.userRelativeLevelDB ( accountIDX, weekend_BackupLevel, weekend_BackupExp, init_time, reward_State, regDate )
VALUES( @accountIDX, 50, 0, 0, 0, GETDATE() )
END
GO
IF OBJECT_ID('dbo.game_league_cheer_add') IS NOT NULL DROP PROCEDURE dbo.game_league_cheer_add
GO
CREATE PROCEDURE [dbo].[game_league_cheer_add]
(
@accountIDX int
, @leagueIDX int
, @teamIDX int
)
AS
SET NOCOUNT ON
DECLARE @cnt INT
DECLARE @errcount INT
SET @cnt = 0
SET @errcount = 0
SET @cnt = (
SELECT COUNT(*)
FROM dbo.userLeagueCheerDB WITH(NOLOCK)
WHERE accountIDX=@accountIDX
AND leagueIDX=@leagueIDX
)
IF (@cnt > 0)
BEGIN
SET @errcount = 2
SELECT @errcount AS 'returnVal'
RETURN
END
SET @cnt = (
SELECT COUNT(*)
FROM dbo.userLeagueTeamDB WITH(NOLOCK)
WHERE idx=@teamIDX
AND leagueIDX=@leagueIDX
)
IF (@cnt = 0)
BEGIN
SET @errcount = 3
SELECT @errcount AS 'returnVal'
RETURN
END
IF (@errcount = 0)
BEGIN
INSERT INTO dbo.userLeagueCheerDB (accountIDX, leagueIDX, teamIDX, regDate)
VALUES (@accountIDX, @leagueIDX, @teamIDX, GETDATE())
UPDATE dbo.userLeagueTeamDB
SET cheerPoint = cheerPoint + 1
WHERE idx=@teamIDX
SET @errcount = 1
SELECT @errcount AS 'returnVal'
END
GO
IF OBJECT_ID('dbo.game_league_cheer_list') IS NOT NULL DROP PROCEDURE dbo.game_league_cheer_list
GO
CREATE PROCEDURE [dbo].[game_league_cheer_list]
(
@accountIDX int
, @topN int
, @idx int
)
AS
SET NOCOUNT ON
SELECT TOP (@topN) leagueIDX, teamIDX, idx
FROM dbo.userLeagueCheerDB WITH(NOLOCK)
WHERE idx < @idx
AND accountIDX=@accountIDX
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_league_get_reward_cheer') IS NOT NULL DROP PROCEDURE dbo.game_league_get_reward_cheer
GO
CREATE PROCEDURE [dbo].[game_league_get_reward_cheer]
(
@accountIDX INT
, @flag TINYINT
)
AS
SET NOCOUNT ON
SELECT idx, rewardPeso
FROM dbo.userLeagueRewardCheerDB WITH(NOLOCK)
WHERE accountIDX = @accountIDX
GO
IF OBJECT_ID('dbo.game_league_reward_cheer_del') IS NOT NULL DROP PROCEDURE dbo.game_league_reward_cheer_del
GO
CREATE PROCEDURE [dbo].[game_league_reward_cheer_del]
(
@idx int
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userLeagueRewardCheerDB
WHERE idx = @idx
GO
IF OBJECT_ID('dbo.game_league_history_get_winner_team_info') IS NOT NULL DROP PROCEDURE dbo.game_league_history_get_winner_team_info
GO
CREATE PROCEDURE [dbo].[game_league_history_get_winner_team_info]
AS
SET NOCOUNT ON
SELECT TOP 1 teamName, factionType
FROM dbo.userLeagueWinnerHistoryDB WITH(NOLOCK)
ORDER BY idx DESC
GO
IF OBJECT_ID('dbo.game_managetool_user_block') IS NOT NULL DROP PROCEDURE dbo.game_managetool_user_block
GO
CREATE PROCEDURE [dbo].[game_managetool_user_block]
(
@nickName NVARCHAR(20)
, @limitType TINYINT
, @limitDate DATETIME
, @reportNick NVARCHAR(20)
, @reportIP NVARCHAR(15)
, @reason NVARCHAR(1000)
, @note NVARCHAR(1000)
)
AS
SET NOCOUNT ON;
DECLARE @accountIDX INT
DECLARE @ddiff INT
SET @ddiff = DATEDIFF(hh, GETDATE(), @limitDate)
SET @accountIDX = (
SELECT accountIDX
FROM dbo.userMemberDB WITH(NOLOCK)
WHERE nickName=@nickName
)
IF (@accountIDX IS NULL)
BEGIN
SELECT 'FALSE'
RETURN;
END
IF (@ddiff > 0)
BEGIN
UPDATE dbo.userMemberDB
SET limitType=@limitType,
limitDate=@limitDate
WHERE accountIDX=@accountIDX
END
ELSE
BEGIN
UPDATE dbo.userMemberDB
SET limitDate=GETDATE()-1
WHERE accountIDX=@accountIDX
END
IF (@@ROWCOUNT = 0)
BEGIN
SELECT 'FALSE'
RETURN;
END
ELSE
BEGIN
SELECT 'TRUE'
END
IF (@ddiff <= 0)
BEGIN
INSERT INTO LosaGame_log.dbo.log_mannerpoint (
playType, modeType, sub1Type, sub2Type,
reportIDX, reportNick, reportpubIP, reportpriIP,
receiveIDX, receiveNick, receivepubIP, roomInfo, chatinfo, note, reason, flag, resultType, regDate
)
VALUES (
0, 0, 0, 0,
0, @reportNick, @reportIP, @reportIP,
@accountIDX, @nickName, '', '', '', @note, @reason, 0, 1, GETDATE()
)
END
GO
IF OBJECT_ID('dbo.game_attendance_add') IS NOT NULL DROP PROCEDURE dbo.game_attendance_add
GO
CREATE PROCEDURE [dbo].[game_attendance_add]
(
@accountIDX INT
, @checkDate DATETIME
)
AS
SET NOCOUNT ON
INSERT INTO dbo.userAttendanceDB (accountIDX, checkDate, regDate)
VALUES (@accountIDX, @checkDate, GETDATE())
GO
IF OBJECT_ID('dbo.game_attendance_list') IS NOT NULL DROP PROCEDURE dbo.game_attendance_list
GO
CREATE PROCEDURE [dbo].[game_attendance_list]
(
@accountIDX INT
)
AS
SET NOCOUNT ON
SELECT idx, accountIDX, checkDate
FROM dbo.userAttendanceDB WITH(NOLOCK)
WHERE accountIDX=@accountIDX
ORDER BY idx ASC
GO
IF OBJECT_ID('dbo.game_attendance_delete') IS NOT NULL DROP PROCEDURE dbo.game_attendance_delete
GO
CREATE PROCEDURE [dbo].[game_attendance_delete]
(
@accountIDX INT
)
AS
SET NOCOUNT ON
DELETE FROM dbo.userAttendanceDB
WHERE accountIDX=@accountIDX
GO
IF OBJECT_ID('dbo.game_spentgold_get_data') IS NOT NULL DROP PROCEDURE dbo.game_spentgold_get_data
GO
-- =============================================
-- Subject : dbo.game_spentGold_get_data
-- DBA : KEUN
-- DATE : 2015-01-27
-- Description : 누적 소비 골드
-- =============================================
CREATE PROCEDURE [dbo].[game_spentGold_get_data]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @nReturn INT
DECLARE @SPENT_GOLD_SUM INT
DECLARE @SPENT_GOLD INT
DECLARE @PLAY_TIME INT
DECLARE @START_DATE DATETIME
DECLARE @OVER_DATE DATETIME
SET @OVER_DATE = CAST(CONVERT(CHAR(10), GETDATE(), 121) as DATETIME);
SET @START_DATE = DATEADD(dd, -90, @OVER_DATE)
SET @nReturn=0;
SET @SPENT_GOLD_SUM=0;
SET @SPENT_GOLD=0;
SET @PLAY_TIME=0;
IF EXISTS (SELECT * FROM dbo.userMemberDB WHERE AccountIDX=@ACCOUNT_IDX)
BEGIN
-- 누적 소비 골드
SET @SPENT_GOLD_SUM = (SELECT UserSpentGold FROM dbo.userSpentGoldDB WITH (READCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX);
-- 해당 기간의 소비 골드 합
SET @SPENT_GOLD=(SELECT SpentGold FROM (
SELECT AccountIDX, SUM(UserSpentGold) as SpentGold FROM dbo.userSpentGoldLogDB WITH (READCOMMITTED)
WHERE RegDate>=@START_DATE and RegDate<@OVER_DATE+1 and AccountIDX=@ACCOUNT_IDX
GROUP BY AccountIDX
) as userSpentGoldLogDB
)
-- 사용자 플레이 타임
SET @PLAY_TIME = (SELECT PlayTime FROM dbo.userGameDB WITH (READUNCOMMITTED) WHERE AccountIDX=@ACCOUNT_IDX);
SELECT @nReturn as nReturn, ISNULL(@SPENT_GOLD_SUM,0) as SPENT_GOLD_SUM, ISNULL(@SPENT_GOLD,0) as SPENT_GOLD, @PLAY_TIME as PlayTime
END
ELSE
BEGIN
SELECT -1, 0 as SPENT_GOLD_SUM, 0 as SPENT_GOLD, 0 as PlayTime
END
RETURN;
GO
IF OBJECT_ID('dbo.game_popstore_get_data') IS NOT NULL DROP PROCEDURE dbo.game_popstore_get_data
GO
-- =============================================
-- Subject : dbo.game_popStore_get_data
-- DBA : KEUN
-- DATE : 2015-01-27
-- Description : 팝업 스토어 구매 내역 SELECT
-- =============================================
CREATE PROCEDURE [dbo].[game_popStore_get_data]
@ACCOUNT_IDX INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT PopupSlot FROM dbo.userPopStoreDB WITH (READUNCOMMITTED)
WHERE AccountIDX=@ACCOUNT_IDX
RETURN;
GO
IF OBJECT_ID('dbo.game_popstore_set_add') IS NOT NULL DROP PROCEDURE dbo.game_popstore_set_add
GO
-- =============================================
-- Subject : dbo.game_popStore_set_add
-- DBA : KEUN
-- DATE : 2015-01-27
-- Description : 팝업 스토어 구매 내역 Insert
-- =============================================
CREATE PROCEDURE [dbo].[game_popStore_set_add]
@ACCOUNT_IDX INT,
@POPUP_SLOT INT
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
BEGIN TRAN
-- 팝업 스토어 구매 내역 Insert
INSERT dbo.userPopStoreDB WITH (XLOCK, ROWLOCK) (AccountIDX, PopupSlot)
VALUES (@ACCOUNT_IDX, @POPUP_SLOT)
IF (@@ROWCOUNT=0) or (@@ERROR<>0)
BEGIN
ROLLBACK TRAN
SELECT -1;
RETURN;
END
COMMIT TRAN
SELECT 0;
RETURN;
GO
IF OBJECT_ID('dbo.game_event_pirateroulette_number_add') IS NOT NULL DROP PROCEDURE dbo.game_event_pirateroulette_number_add
GO
CREATE PROCEDURE [dbo].[game_event_PirateRoulette_number_add]
(
@accountIDX int,
@HP int
)
AS
SET NOCOUNT ON
IF( EXISTS( SELECT * FROM dbo.userEventPirateRoulette_Number WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
RETURN
END
ELSE
BEGIN
INSERT
INTO dbo.userEventPirateRoulette_Number( accountidx, HP, regDate )
VALUES ( @accountIDX, @HP, GETDATE() )
END
GO
IF OBJECT_ID('dbo.game_event_pirateroulette_number_get') IS NOT NULL DROP PROCEDURE dbo.game_event_pirateroulette_number_get
GO
CREATE PROCEDURE [dbo].[game_event_PirateRoulette_number_get]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF ( EXISTS(SELECT * FROM dbo.userEventPirateRoulette_Number WITH( NOLOCK ) WHERE accountIDX = @accountIDX) )
BEGIN
SELECT HP, slot1, slot2, slot3, slot4, slot5
, slot6, slot7, slot8, slot9, slot10
, slot11, slot12, slot13, slot14, slot15
, slot16, slot17, slot18, slot19, slot20
, slot21, slot22, slot23, slot24, slot25
, slot26, slot27, slot28, slot29, slot30
, slot31, slot32, slot33, slot34, slot35
, slot36, slot37, slot38, slot39, slot40
FROM dbo.userEventPirateRoulette_Number WITH( NOLOCK )
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
SELECT -1 AS HP
, 0 AS slot1, 0 AS slot2, 0 AS slot3, 0 AS slot4, 0 AS slot5
, 0 AS slot6, 0 AS slot7, 0 AS slot8, 0 AS slot9, 0 AS slot10
, 0 AS slot11, 0 AS slot12, 0 AS slot13, 0 AS slot14, 0 AS slot15
, 0 AS slot16, 0 AS slot17, 0 AS slot18, 0 AS slot19, 0 AS slot20
, 0 AS slot21, 0 AS slot22, 0 AS slot23, 0 AS slot24, 0 AS slot25
, 0 AS slot26, 0 AS slot27, 0 AS slot28, 0 AS slot29, 0 AS slot30
, 0 AS slot31, 0 AS slot32, 0 AS slot33, 0 AS slot34, 0 AS slot35
, 0 AS slot36, 0 AS slot37, 0 AS slot38, 0 AS slot39, 0 AS slot40
END
GO
IF OBJECT_ID('dbo.game_event_pirateroulette_number_save') IS NOT NULL DROP PROCEDURE dbo.game_event_pirateroulette_number_save
GO
CREATE PROCEDURE [dbo].[game_event_PirateRoulette_number_save]
(
@accountIDX int,
@HP int
, @slot1 tinyint, @slot2 tinyint, @slot3 tinyint, @slot4 tinyint, @slot5 tinyint, @slot6 tinyint, @slot7 tinyint, @slot8 tinyint, @slot9 tinyint, @slot10 tinyint
, @slot11 tinyint, @slot12 tinyint, @slot13 tinyint, @slot14 tinyint, @slot15 tinyint, @slot16 tinyint, @slot17 tinyint, @slot18 tinyint, @slot19 tinyint, @slot20 tinyint
, @slot21 tinyint, @slot22 tinyint, @slot23 tinyint, @slot24 tinyint, @slot25 tinyint, @slot26 tinyint, @slot27 tinyint, @slot28 tinyint, @slot29 tinyint, @slot30 tinyint
, @slot31 tinyint, @slot32 tinyint, @slot33 tinyint, @slot34 tinyint, @slot35 tinyint, @slot36 tinyint, @slot37 tinyint, @slot38 tinyint, @slot39 tinyint, @slot40 tinyint
)
AS
SET NOCOUNT ON
UPDATE dbo.userEventPirateRoulette_Number
SET HP = @HP, slot1 = @slot1, slot2 = @slot2, slot3 = @slot3, slot4 = @slot4, slot5 = @slot5
, slot6 = @slot6, slot7 = @slot7, slot8 = @slot8, slot9 = @slot9, slot10 = @slot10
, slot11 = @slot11, slot12 = @slot12, slot13 = @slot13, slot14 = @slot14, slot15 = @slot15
, slot16 = @slot16, slot17 = @slot17, slot18 = @slot18, slot19 = @slot19, slot20 = @slot20
, slot21 = @slot21, slot22 = @slot22, slot23 = @slot23, slot24 = @slot24, slot25 = @slot25
, slot26 = @slot26, slot27 = @slot27, slot28 = @slot28, slot29 = @slot29, slot30 = @slot30
, slot31 = @slot31, slot32 = @slot32, slot33 = @slot33, slot34 = @slot34, slot35 = @slot35
, slot36 = @slot36, slot37 = @slot37, slot38 = @slot38, slot39 = @slot39, slot40 = @slot40
WHERE accountIDX = @accountIDX
GO
IF OBJECT_ID('dbo.game_event_pirateroulette_present_add') IS NOT NULL DROP PROCEDURE dbo.game_event_pirateroulette_present_add
GO
CREATE PROCEDURE [dbo].[game_event_PirateRoulette_present_add]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF( EXISTS( SELECT * FROM dbo.userEventPirateRoulette_Present WITH( NOLOCK ) WHERE accountIDX = @accountIDX ) )
BEGIN
RETURN
END
ELSE
BEGIN
INSERT
INTO dbo.userEventPirateRoulette_Present( accountidx, regDate )
VALUES ( @accountIDX, GETDATE() )
END
GO
IF OBJECT_ID('dbo.game_event_pirateroulette_present_get') IS NOT NULL DROP PROCEDURE dbo.game_event_pirateroulette_present_get
GO
CREATE PROCEDURE [dbo].[game_event_PirateRoulette_present_get]
(
@accountIDX int
)
AS
SET NOCOUNT ON
IF ( EXISTS(SELECT * FROM dbo.userEventPirateRoulette_Present WITH( NOLOCK ) WHERE accountIDX = @accountIDX) )
BEGIN
SELECT reward1, reward2, reward3, reward4, reward5,
reward6, reward7, reward8, reward9, reward10
-- reward11, reward12, reward13, reward14, reward15
FROM dbo.userEventPirateRoulette_Present WITH( NOLOCK )
WHERE accountIDX = @accountIDX
END
ELSE
BEGIN
SELECT 0 AS reward1, 0 AS reward2, 0 AS reward3, 0 AS reward4, 0 AS reward5,
0 AS reward6, 0 AS reward7, 0 AS reward8, 0 AS reward9, 0 AS reward10
-- 0 AS reward11, 0 AS reward12, 0 AS reward13, 0 AS reward14, 0 AS reward15
END
GO
IF OBJECT_ID('dbo.game_event_pirateroulette_present_save') IS NOT NULL DROP PROCEDURE dbo.game_event_pirateroulette_present_save
GO
CREATE PROCEDURE [dbo].[game_event_PirateRoulette_present_save]
(
@accountIDX int
, @Reward1 tinyint
, @reward2 tinyint
, @reward3 tinyint
, @reward4 tinyint
, @reward5 tinyint
, @reward6 tinyint
, @reward7 tinyint
, @reward8 tinyint
, @reward9 tinyint
, @reward10 tinyint
)
AS
SET NOCOUNT ON
UPDATE dbo.userEventPirateRoulette_Present
SET reward1 = @reward1, reward2 = @reward2, reward3 = @reward3, reward4 = @reward4, reward5 = @reward5
, reward6 = @reward6, reward7 = @reward7, reward8 = @reward8, reward9 = @reward9, reward10 = @reward10
-- , reward11 = @reward11, reward12 = @reward12, reward13 = @reward13, reward14 = @reward14, reward15 = @reward15
WHERE accountIDX = @accountIDX
GO
Reference in new issue View Git Blame Copy Permalink