-- 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