CREATE DATABASE [CHARACTER_01_DBF]
ON PRIMARY
( NAME = [CHARACTER_01_DBF_Data],
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CHARACTER_01_DBF.MDF',
SIZE = 632 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB ),
FILEGROUP [CHARACTER_01_DBF_FG1]
( NAME = [CHARACTER_01_DBF_Data1],
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CHARACTER_01_DBF_1.NDF',
SIZE = 1024 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB ),
FILEGROUP [CHARACTER_01_DBF_FG2]
( NAME = [CHARACTER_01_DBF_Data2],
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CHARACTER_01_DBF_2.NDF',
SIZE = 1024 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB ),
FILEGROUP [CHARACTER_01_DBF_FG3]
( NAME = [CHARACTER_01_DBF_Data3],
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CHARACTER_01_DBF_3.NDF',
SIZE = 500 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50 MB ),
FILEGROUP [CHARACTER_01_DBF_FGI]
( NAME = [CHARACTER_01_DBF_DataI],
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CHARACTER_01_DBF_4.NDF',
SIZE = 500 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50 MB )
LOG ON
( NAME = [CHARACTER_01_DBF_Log],
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CHARACTER_01_DBF_5.LDF',
SIZE = 138 MB,
MAXSIZE = 1024 MB,
FILEGROWTH = 10 MB )
COLLATE Latin1_General_BIN
GO
USE [CHARACTER_01_DBF]
GO
--
-- Definition for user eocrm :
--
CREATE USER [eocrm]
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = [dbo]
GO
--
-- Definition for table 1-BANK_TBL :
--
CREATE TABLE [dbo].[1-BANK_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NULL,
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[m_Bank] varchar(8000) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table 1-GUILD_BANK_TBL :
--
CREATE TABLE [dbo].[1-GUILD_BANK_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NULL,
[m_idGuild] char(6) COLLATE Latin1_General_BIN NULL,
[m_GuildBank] varchar(8000) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table 1-INVENTORY_TBL :
--
CREATE TABLE [dbo].[1-INVENTORY_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NULL,
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[m_Inventory] varchar(8000) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table BANK_EXT_TBL :
--
CREATE TABLE [dbo].[BANK_EXT_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_extBank] varchar(1296) COLLATE Latin1_General_BIN CONSTRAINT [DF_BANK_EXT_TBL_m_extBank] DEFAULT '$' NULL,
[m_BankPiercing] varchar(1872) COLLATE Latin1_General_BIN CONSTRAINT [DF_BANK_EXT_TBL_m_BankPiercing] DEFAULT $0.0000 NULL,
[szBankPet] varchar(2688) COLLATE Latin1_General_BIN CONSTRAINT [DF_BANK_EXT_TBL_szBankPet] DEFAULT $0.0000 NULL
)
ON [CHARACTER_01_DBF_FG2]
GO
--
-- Definition for table BANK_TBL :
--
CREATE TABLE [dbo].[BANK_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_BankPw] char(4) COLLATE Latin1_General_BIN NULL,
[m_Bank] varchar(4290) COLLATE Latin1_General_BIN CONSTRAINT [DF_BANK_TBL_m_Bank] DEFAULT $0.0000 NULL,
[m_apIndex_Bank] varchar(215) COLLATE Latin1_General_BIN CONSTRAINT [DF_BANK_TBL_m_apIndex_Bank] DEFAULT $0.0000 NULL,
[m_dwObjIndex_Bank] varchar(215) COLLATE Latin1_General_BIN CONSTRAINT [DF_BANK_TBL_m_dwObjIndex_Bank] DEFAULT $0.0000 NULL,
[m_dwGoldBank] int NULL
)
ON [CHARACTER_01_DBF_FG2]
GO
--
-- Definition for table BASE_VALUE_TBL :
--
CREATE TABLE [dbo].[BASE_VALUE_TBL] (
[g_nSex] char(1) COLLATE Latin1_General_BIN NULL,
[m_vScale_x] real NULL,
[m_dwMotion] int NULL,
[m_fAngle] real NULL,
[m_nHitPoint] int NULL,
[m_nManaPoint] int NULL,
[m_nFatiguePoint] int NULL,
[m_dwRideItemIdx] int NULL,
[m_dwGold] int NULL,
[m_nJob] int NULL,
[m_pActMover] varchar(50) COLLATE Latin1_General_BIN NULL,
[m_nStr] int NULL,
[m_nSta] int NULL,
[m_nDex] int NULL,
[m_nInt] int NULL,
[m_nLevel] int NULL,
[m_nExp1] bigint NULL,
[m_nExp2] bigint NULL,
[m_aJobSkill] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_aLicenseSkill] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_aJobLv] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_dwExpertLv] int NULL,
[m_idMarkingWorld] int NULL,
[m_vMarkingPos_x] real NULL,
[m_vMarkingPos_y] real NULL,
[m_vMarkingPos_z] real NULL,
[m_nRemainGP] int NULL,
[m_nRemainLP] int NULL,
[m_nFlightLv] int NULL,
[m_nFxp] int NULL,
[m_nTxp] int NULL,
[m_lpQuestCntArray] varchar(1024) COLLATE Latin1_General_BIN NULL,
[m_chAuthority] char(1) COLLATE Latin1_General_BIN NULL,
[m_dwMode] int NULL,
[blockby] varchar(32) COLLATE Latin1_General_BIN NULL,
[TotalPlayTime] int NULL,
[isblock] char(1) COLLATE Latin1_General_BIN NULL,
[m_Inventory] varchar(6940) COLLATE Latin1_General_BIN NULL,
[m_apIndex] varchar(345) COLLATE Latin1_General_BIN NULL,
[m_adwEquipment] varchar(135) COLLATE Latin1_General_BIN NULL,
[m_aSlotApplet] varchar(3100) COLLATE Latin1_General_BIN NULL,
[m_aSlotItem] varchar(6885) COLLATE Latin1_General_BIN NULL,
[m_aSlotQueue] varchar(225) COLLATE Latin1_General_BIN NULL,
[m_SkillBar] smallint NULL,
[m_dwObjIndex] varchar(345) COLLATE Latin1_General_BIN NULL,
[m_Card] varchar(1980) COLLATE Latin1_General_BIN NULL,
[m_Cube] varchar(1980) COLLATE Latin1_General_BIN NULL,
[m_apIndex_Card] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_dwObjIndex_Card] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_apIndex_Cube] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_dwObjIndex_Cube] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_idparty] int NULL,
[m_nNumKill] int NULL,
[m_idMuerderer] int NULL,
[m_nSlaughter] int NULL,
[m_nFame] int NULL,
[m_nDeathExp] int NULL,
[m_nDeathLevel] int NULL,
[m_dwFlyTime] int NULL,
[m_nMessengerState] int NULL,
[m_Bank] varchar(4290) COLLATE Latin1_General_BIN NULL,
[m_apIndex_Bank] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_dwObjIndex_Bank] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_dwGoldBank] int NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table BILING_ITEM_TBL :
--
CREATE TABLE [dbo].[BILING_ITEM_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_dwSMTime] varchar(2560) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table CHARACTER_TBL :
--
CREATE TABLE [dbo].[CHARACTER_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[account] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_szName] varchar(32) COLLATE Latin1_General_BIN NULL,
[playerslot] int NULL,
[dwWorldID] int NULL,
[m_dwIndex] int NULL,
[m_vScale_x] real NULL,
[m_dwMotion] int NULL,
[m_vPos_x] real NULL,
[m_vPos_y] real NULL,
[m_vPos_z] real NULL,
[m_fAngle] real NULL,
[m_szCharacterKey] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_nHitPoint] int NULL,
[m_nManaPoint] int NULL,
[m_nFatiguePoint] int NULL,
[m_nFuel] int CONSTRAINT [m_nFuel_default] DEFAULT -1 NULL,
[m_dwSkinSet] int NULL,
[m_dwHairMesh] int NULL,
[m_dwHairColor] int NULL,
[m_dwHeadMesh] int NULL,
[m_dwSex] int NULL,
[m_dwRideItemIdx] int NULL,
[m_dwGold] int NULL,
[m_nJob] int NULL,
[m_pActMover] varchar(50) COLLATE Latin1_General_BIN NULL,
[m_nStr] int NULL,
[m_nSta] int NULL,
[m_nDex] int NULL,
[m_nInt] int NULL,
[m_nLevel] int NULL,
[m_nMaximumLevel] int NULL,
[m_nExp1] bigint NULL,
[m_nExp2] bigint NULL,
[m_aJobSkill] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_aLicenseSkill] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_aJobLv] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_dwExpertLv] int NULL,
[m_idMarkingWorld] int NULL,
[m_vMarkingPos_x] real NULL,
[m_vMarkingPos_y] real NULL,
[m_vMarkingPos_z] real NULL,
[m_nRemainGP] int NULL,
[m_nRemainLP] int NULL,
[m_nFlightLv] int NULL,
[m_nFxp] int NULL,
[m_nTxp] int NULL,
[m_lpQuestCntArray] varchar(3072) COLLATE Latin1_General_BIN NULL,
[m_chAuthority] char(1) COLLATE Latin1_General_BIN NULL,
[m_dwMode] int NULL,
[m_idparty] int NULL,
[m_idCompany] char(6) COLLATE Latin1_General_BIN NULL,
[m_idMuerderer] int NULL,
[m_nFame] int NULL,
[m_nDeathExp] bigint NULL,
[m_nDeathLevel] int NULL,
[m_dwFlyTime] int NULL,
[m_nMessengerState] int NULL,
[blockby] varchar(32) COLLATE Latin1_General_BIN NULL,
[TotalPlayTime] int NULL,
[isblock] char(1) COLLATE Latin1_General_BIN NULL,
[End_Time] char(12) COLLATE Latin1_General_BIN NULL,
[BlockTime] char(8) COLLATE Latin1_General_BIN NULL,
[CreateTime] datetime NULL,
[m_tmAccFuel] int NULL,
[m_tGuildMember] char(14) COLLATE Latin1_General_BIN NULL,
[m_dwSkillPoint] int NULL,
[m_aCompleteQuest] varchar(1024) COLLATE Latin1_General_BIN NULL,
[m_dwReturnWorldID] int NULL,
[m_vReturnPos_x] real NULL,
[m_vReturnPos_y] real NULL,
[m_vReturnPos_z] real NULL,
[MultiServer] int NULL,
[SkillPoint] int CONSTRAINT [DF_CHARACTER_SkillPoint] DEFAULT 0 NOT NULL,
[SkillLv] int CONSTRAINT [DF_CHARACTER_SkillLv] DEFAULT 0 NOT NULL,
[SkillExp] bigint CONSTRAINT [DF_CHARACTER_SkillExp] DEFAULT 0 NOT NULL,
[dwEventFlag] bigint CONSTRAINT [DF_CHARACTER_dwEventFlag] DEFAULT 0 NULL,
[dwEventTime] int CONSTRAINT [DF_CHARACTER_dwEventTime] DEFAULT 0 NULL,
[dwEventElapsed] int CONSTRAINT [DF_CHARACTER_dwEventElapsed] DEFAULT 0 NULL,
[AngelExp] bigint CONSTRAINT [DF_CHARACTER_AngelExp] DEFAULT 0 NOT NULL,
[AngelLevel] int CONSTRAINT [DF_CHARACTER_AngelLevel] DEFAULT 0 NOT NULL,
[PKValue] int CONSTRAINT [DF_CHARACTER_PKValue] DEFAULT 0 NOT NULL,
[PKPropensity] int CONSTRAINT [DF_CHARACTER_PKPropensity] DEFAULT 0 NOT NULL,
[PKExp] int CONSTRAINT [DF_CHARACTER_PKExp] DEFAULT 0 NOT NULL,
[m_dwPetId] int CONSTRAINT [DF__CHARACTER__m_dwP__6225902D] DEFAULT -1 NULL,
[m_nExpLog] int CONSTRAINT [DF__CHARACTER__m_nEx__6E8B6712] DEFAULT 0 NULL,
[m_nAngelExpLog] int CONSTRAINT [DF__CHARACTER__m_nAn__6F7F8B4B] DEFAULT 0 NULL,
[m_nCoupon] int CONSTRAINT [DF__CHARACTER__m_nCo__5A4F643B] DEFAULT 0 NOT NULL
)
ON [CHARACTER_01_DBF_FG3]
GO
--
-- Definition for table COMPANY_TBL :
--
CREATE TABLE [dbo].[COMPANY_TBL] (
[m_idCompany] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_szCompany] varchar(16) COLLATE Latin1_General_BIN NULL,
[m_leaderid] char(6) COLLATE Latin1_General_BIN NULL,
[isuse] char(1) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_BANK_EXT_TBL :
--
CREATE TABLE [dbo].[GUILD_BANK_EXT_TBL] (
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_extGuildBank] varchar(1296) COLLATE Latin1_General_BIN NULL,
[m_GuildBankPiercing] varchar(1872) COLLATE Latin1_General_BIN NULL,
[szGuildBankPet] varchar(2688) COLLATE Latin1_General_BIN CONSTRAINT [DF_GUILD_BANK_EXT_TBL_szGuildBankPet] DEFAULT '$' NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_BANK_TBL :
--
CREATE TABLE [dbo].[GUILD_BANK_TBL] (
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_apIndex] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_dwObjIndex] varchar(215) COLLATE Latin1_General_BIN NULL,
[m_GuildBank] text COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
TEXTIMAGE_ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL :
--
CREATE TABLE [dbo].[GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[combatID] int NOT NULL,
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NOT NULL,
[m_nSeq] int NULL,
[Start_Time] datetime NULL,
[End_Time] datetime NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_COMBAT_1TO1_BATTLE_TBL :
--
CREATE TABLE [dbo].[GUILD_COMBAT_1TO1_BATTLE_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[combatID] int NOT NULL,
[m_idWorld] int NOT NULL,
[Start_Time] datetime CONSTRAINT [DF__GUILD_COM__Start__74444068] DEFAULT getdate() NOT NULL,
[End_Time] datetime NULL,
[m_idGuild_1st] char(6) COLLATE Latin1_General_BIN NOT NULL,
[m_idGuild_2nd] char(6) COLLATE Latin1_General_BIN NOT NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_COMBAT_1TO1_TENDER_TBL :
--
CREATE TABLE [dbo].[GUILD_COMBAT_1TO1_TENDER_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[combatID] int NOT NULL,
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[m_nPenya] int CONSTRAINT [DF__GUILD_COM__m_nPe__7167D3BD] DEFAULT 0 NOT NULL,
[s_date] datetime CONSTRAINT [DF__GUILD_COM__s_dat__725BF7F6] DEFAULT getdate() NOT NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_MEMBER_TBL :
--
CREATE TABLE [dbo].[GUILD_MEMBER_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[m_szAlias] varchar(20) COLLATE Latin1_General_BIN NULL,
[m_nWin] int NULL,
[m_nLose] int NULL,
[m_nSurrender] int NULL,
[m_nMemberLv] int NULL,
[m_nGiveGold] bigint NULL,
[m_nGivePxp] int NULL,
[m_idWar] int NULL,
[m_idVote] int NULL,
[isuse] char(1) COLLATE Latin1_General_BIN NULL,
[CreateTime] datetime NULL,
[m_nClass] int NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_QUEST_TBL :
--
CREATE TABLE [dbo].[GUILD_QUEST_TBL] (
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[n_Id] int NOT NULL,
[nState] int NULL,
[s_date] char(14) COLLATE Latin1_General_BIN NULL,
[e_date] char(14) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_TBL :
--
CREATE TABLE [dbo].[GUILD_TBL] (
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[Lv_1] int NULL,
[Lv_2] int NULL,
[Lv_3] int NULL,
[Lv_4] int NULL,
[Pay_0] int NULL,
[Pay_1] int NULL,
[Pay_2] int NULL,
[Pay_3] int NULL,
[Pay_4] int NULL,
[m_szGuild] varchar(48) COLLATE Latin1_General_BIN NULL,
[m_nLevel] int NULL,
[m_nGuildGold] int NULL,
[m_nGuildPxp] int NULL,
[m_nWin] int NULL,
[m_nLose] int NULL,
[m_nSurrender] int NULL,
[m_nWinPoint] int NULL,
[m_dwLogo] int NULL,
[m_szNotice] varchar(127) COLLATE Latin1_General_BIN NULL,
[isuse] char(1) COLLATE Latin1_General_BIN NULL,
[CreateTime] datetime NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_VOTE_TBL :
--
CREATE TABLE [dbo].[GUILD_VOTE_TBL] (
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_idVote] int NULL,
[m_cbStatus] char(1) COLLATE Latin1_General_BIN NULL,
[m_szTitle] varchar(128) COLLATE Latin1_General_BIN NULL,
[m_szQuestion] varchar(255) COLLATE Latin1_General_BIN NULL,
[m_szString1] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_szString2] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_szString3] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_szString4] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_cbCount1] int NULL,
[m_cbCount2] int NULL,
[m_cbCount3] int NULL,
[m_cbCount4] int NULL,
[CreateTime] datetime NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table GUILD_WAR_TBL :
--
CREATE TABLE [dbo].[GUILD_WAR_TBL] (
[m_idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_idWar] int NULL,
[f_idGuild] char(6) COLLATE Latin1_General_BIN NULL,
[m_nDeath] int NULL,
[m_nSurrender] int NULL,
[m_nCount] int NULL,
[m_nAbsent] int NULL,
[f_nDeath] int NULL,
[f_nSurrender] int NULL,
[f_nCount] char(10) COLLATE Latin1_General_BIN NULL,
[f_nAbsent] int NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL,
[StartTime] char(12) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table G_TOP_VOID_ACCOUNT :
--
CREATE TABLE [dbo].[G_TOP_VOID_ACCOUNT] (
[account_name] varchar(50) COLLATE Latin1_General_BIN NULL
)
ON [PRIMARY]
GO
--
-- Definition for table G_TOP_VOID_CHAR :
--
CREATE TABLE [dbo].[G_TOP_VOID_CHAR] (
[character_name] varchar(32) COLLATE Latin1_General_CI_AS NULL
)
ON [PRIMARY]
GO
--
-- Definition for table INVENTORY_EXT_TBL :
--
CREATE TABLE [dbo].[INVENTORY_EXT_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_extInventory] varchar(1296) COLLATE Latin1_General_BIN NULL,
[m_InventoryPiercing] varchar(1872) COLLATE Latin1_General_BIN NULL,
[szInventoryPet] varchar(2688) COLLATE Latin1_General_BIN CONSTRAINT [DF_INVENTORY_EXT_TBL_szInventoryPet] DEFAULT '$' NULL
)
ON [CHARACTER_01_DBF_FG2]
GO
--
-- Definition for table INVENTORY_TBL :
--
CREATE TABLE [dbo].[INVENTORY_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_Inventory] varchar(6000) COLLATE Latin1_General_BIN NULL,
[m_apIndex] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_adwEquipment] varchar(500) COLLATE Latin1_General_BIN NULL,
[m_dwObjIndex] varchar(512) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG2]
GO
--
-- Definition for table ITEM_REMOVE_TBL :
--
CREATE TABLE [dbo].[ITEM_REMOVE_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[Item_Name] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_nAbilityOption] int NULL,
[Item_count] int NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL,
[m_nNo] int IDENTITY(1, 1) NOT NULL,
[End_Time] char(8) COLLATE Latin1_General_BIN NULL,
[m_bItemResist] int NULL,
[m_nResistAbilityOption] int NULL,
[ItemFlag] int CONSTRAINT [DF_RemoveItemFlag] DEFAULT 0 NULL,
[ReceiveDt] datetime CONSTRAINT [DF_ITEM_REMOVE_TBL_ReceiveDt] DEFAULT getdate() NULL,
[DeleteDt] datetime NULL,
[RequestUser] varchar(30) COLLATE Latin1_General_BIN CONSTRAINT [DF_RequestUser] DEFAULT 'UNKNOWN' NULL,
[RandomOption] bigint CONSTRAINT [DF_ITEM_REMOVE_RandomOpt] DEFAULT 0 NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table ITEM_SEND_TBL :
--
CREATE TABLE [dbo].[ITEM_SEND_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[Item_Name] varchar(32) COLLATE Latin1_General_BIN NULL,
[Item_count] int NULL,
[m_nAbilityOption] int NULL,
[m_nNo] int IDENTITY(1, 1) NOT NULL,
[End_Time] char(8) COLLATE Latin1_General_BIN NULL,
[m_bItemResist] int NULL,
[m_nResistAbilityOption] int NULL,
[m_bCharged] int NULL,
[idSender] char(7) COLLATE Latin1_General_BIN CONSTRAINT [DF_ITEM_SEND_TBL_idRecvd] DEFAULT '000000' NULL,
[nPiercedSize] int NULL,
[adwItemId0] int NULL,
[adwItemId1] int NULL,
[adwItemId2] int NULL,
[adwItemId3] int NULL,
[m_dwKeepTime] bigint NULL,
[ItemFlag] int CONSTRAINT [DF_ItemFlag] DEFAULT 0 NULL,
[ReceiveDt] datetime CONSTRAINT [DF_ReceiveDt] DEFAULT getdate() NULL,
[ProvideDt] datetime NULL,
[nRandomOptItemId] bigint CONSTRAINT [DF_ITEM_SEND_RandomOpt] DEFAULT 0 NULL,
[adwItemId4] int CONSTRAINT [DF_ITEM_SEND_adwItemId4] DEFAULT 0 NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table MAIL_TBL :
--
CREATE TABLE [dbo].[MAIL_TBL] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[nMail] bigint NOT NULL,
[idReceiver] char(7) COLLATE Latin1_General_BIN NULL,
[idSender] char(7) COLLATE Latin1_General_BIN NULL,
[nGold] bigint NOT NULL,
[tmCreate] int CONSTRAINT [DF_MAIL_TBL_tmCreate] DEFAULT 0 NULL,
[byRead] int CONSTRAINT [DF_MAIL_TBL_byRead] DEFAULT 0 NULL,
[szTitle] varchar(128) COLLATE Latin1_General_BIN NOT NULL,
[szText] varchar(255) COLLATE Latin1_General_BIN NOT NULL,
[dwItemId] int NOT NULL,
[nItemNum] int NOT NULL,
[nRepairNumber] int NOT NULL,
[nHitPoint] int NOT NULL,
[nMaxHitPoint] int NOT NULL,
[nMaterial] int NOT NULL,
[byFlag] int NOT NULL,
[dwSerialNumber] int NOT NULL,
[nOption] int NOT NULL,
[bItemResist] int NOT NULL,
[nResistAbilityOption] int NOT NULL,
[idGuild] int NOT NULL,
[nResistSMItemId] int NOT NULL,
[bCharged] int NOT NULL,
[dwKeepTime] int NOT NULL,
[nRandomOptItemId] bigint NULL,
[nPiercedSize] int NOT NULL,
[dwItemId1] int NOT NULL,
[dwItemId2] int NOT NULL,
[dwItemId3] int NOT NULL,
[dwItemId4] int NOT NULL,
[SendDt] datetime NULL,
[ReadDt] datetime NULL,
[GetGoldDt] datetime NULL,
[DeleteDt] datetime NULL,
[ItemFlag] int CONSTRAINT [DF_MAIL_TBL_ItemFlag] DEFAULT 0 NOT NULL,
[ItemReceiveDt] datetime NULL,
[GoldFlag] int CONSTRAINT [DF_MAIL_TBL_GoldFag] DEFAULT 0 NOT NULL,
[bPet] int CONSTRAINT [DF__MAIL_TBL__bPet__6319B466] DEFAULT 0 NULL,
[nKind] int CONSTRAINT [DF__MAIL_TBL__nKind__640DD89F] DEFAULT 0 NULL,
[nLevel] int CONSTRAINT [DF__MAIL_TBL__nLevel__6501FCD8] DEFAULT 0 NULL,
[dwExp] int CONSTRAINT [DF__MAIL_TBL__dwExp__65F62111] DEFAULT 0 NULL,
[wEnergy] int CONSTRAINT [DF__MAIL_TBL__wEnerg__66EA454A] DEFAULT 0 NULL,
[wLife] int CONSTRAINT [DF__MAIL_TBL__wLife__67DE6983] DEFAULT 0 NULL,
[anAvailLevel_D] int CONSTRAINT [DF__MAIL_TBL__anAvai__68D28DBC] DEFAULT 0 NULL,
[anAvailLevel_C] int CONSTRAINT [DF__MAIL_TBL__anAvai__69C6B1F5] DEFAULT 0 NULL,
[anAvailLevel_B] int CONSTRAINT [DF__MAIL_TBL__anAvai__6ABAD62E] DEFAULT 0 NULL,
[anAvailLevel_A] int CONSTRAINT [DF__MAIL_TBL__anAvai__6BAEFA67] DEFAULT 0 NULL,
[anAvailLevel_S] int CONSTRAINT [DF__MAIL_TBL__anAvai__6CA31EA0] DEFAULT 0 NULL,
[dwItemId5] int CONSTRAINT [DF__MAIL_TBL__dwItem__6D9742D9] DEFAULT 0 NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table MESSENGER_TBL :
--
CREATE TABLE [dbo].[MESSENGER_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[f_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[m_nJob] int NULL,
[m_dwSex] int NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL,
[CreateTime] datetime NULL,
[m_dwState] int NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table MESSENGER_TBL_sos :
--
CREATE TABLE [dbo].[MESSENGER_TBL_sos] (
[f_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[m_nJob] int NULL,
[m_dwSex] int NULL,
[m_dwState] char(1) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table ONLINE_TBL :
--
CREATE TABLE [dbo].[ONLINE_TBL] (
[account] varchar(50) COLLATE Latin1_General_BIN NULL,
[name] varchar(50) COLLATE Latin1_General_BIN NULL
)
ON [PRIMARY]
GO
--
-- Definition for table PARTY_TBL :
--
CREATE TABLE [dbo].[PARTY_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[partyname] varchar(16) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table QUEST_TBL :
--
CREATE TABLE [dbo].[QUEST_TBL] (
[m_idPlayer] char(6) COLLATE Latin1_General_BIN NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_wId] int NULL,
[m_nState] tinyint NULL,
[m_wTime] int NULL,
[m_nKillNPCNum_0] tinyint NULL,
[m_nKillNPCNum_1] tinyint NULL,
[m_bPatrol] tinyint NULL,
[m_bDialog] tinyint NULL,
[m_bReserve3] tinyint NULL,
[m_bReserve4] tinyint NULL,
[m_bReserve5] tinyint NULL,
[m_bReserve6] tinyint NULL,
[m_bReserve7] tinyint NULL,
[m_bReserve8] tinyint NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table SKILLINFLUENCE_TBL :
--
CREATE TABLE [dbo].[SKILLINFLUENCE_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NULL,
[SkillInfluence] varchar(7500) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table SKILL_TBL :
--
CREATE TABLE [dbo].[SKILL_TBL] (
[Index] float NULL,
[szName] nvarchar(255) COLLATE Latin1_General_BIN NULL,
[job] float NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table TAG_TBL :
--
CREATE TABLE [dbo].[TAG_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[f_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[m_Message] varchar(255) COLLATE Latin1_General_BIN NULL,
[State] char(1) COLLATE Latin1_General_BIN NULL,
[CreateTime] datetime NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table TASKBAR_ITEM_TBL :
--
CREATE TABLE [dbo].[TASKBAR_ITEM_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_aSlotItem] varchar(6885) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table TASKBAR_TBL :
--
CREATE TABLE [dbo].[TASKBAR_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[m_aSlotApplet] varchar(3100) COLLATE Latin1_General_BIN NULL,
[m_aSlotQueue] varchar(225) COLLATE Latin1_General_BIN NULL,
[m_SkillBar] smallint NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table WANTED_TBL :
--
CREATE TABLE [dbo].[WANTED_TBL] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[penya] bigint NULL,
[szMsg] varchar(40) COLLATE Latin1_General_BIN NULL,
[s_date] char(12) COLLATE Latin1_General_BIN NULL,
[CreateTime] datetime NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table byGMCharacter :
--
CREATE TABLE [dbo].[byGMCharacter] (
[account] varchar(32) COLLATE Latin1_General_BIN NULL,
[m_szName] varchar(32) COLLATE Latin1_General_BIN NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table event_item :
--
CREATE TABLE [dbo].[event_item] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[min] int NULL,
[max] int NULL,
[rid] int IDENTITY(1, 1) NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table item2row_tbl :
--
CREATE TABLE [dbo].[item2row_tbl] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NULL,
[m_dwItemId] int NULL,
[m_nItemNum] int NULL,
[m_nAbilityOption] int NULL,
[m_dwObjId] int NULL
)
ON [PRIMARY]
GO
--
-- Definition for table item_stat_tbl :
--
CREATE TABLE [dbo].[item_stat_tbl] (
[m_dwItemId] int NULL,
[szName] nvarchar(459) COLLATE Latin1_General_BIN NULL,
[lv 1-10] int NULL,
[lv 11-20] int NULL,
[lv 21-30] int NULL,
[lv 31-40] int NULL,
[lv 41-50] int NULL,
[lv 51-60] int NULL,
[lv 61-70] int NULL,
[lv 71-80] int NULL,
[lv 81-90] int NULL,
[lv 91-100] int NULL,
[lv 101-110] int NULL,
[lv 111-120] int NULL,
[lv 121-130] int NULL,
[lv 131-140] int NULL,
[lv 141-150] int NULL,
[lv 151-160] int NULL,
[lv 161-170] int NULL,
[lv 171-180] int NULL,
[lv 181-190] int NULL,
[lv 191-200] int NULL,
[lv 201-210] int NULL,
[lv 211-220] int NULL,
[lv 221-230] int NULL,
[lv 231-240] int NULL,
[lv 241-250] int NULL,
[lv 251-260] int NULL,
[lv 261-270] int NULL,
[lv 271-280] int NULL,
[lv 281-290] int NULL,
[lv 291-300] int NULL,
[cnt] int NULL,
[dwItemLV] int NULL
)
ON [PRIMARY]
GO
--
-- Definition for table level :
--
CREATE TABLE [dbo].[level] (
[Col001] float NULL,
[Col002] float NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table stat_pet_inven :
--
CREATE TABLE [dbo].[stat_pet_inven] (
[rid] int NULL,
[petlevel] int NULL,
[petdiv] int NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblCharacterRank :
--
CREATE TABLE [dbo].[tblCharacterRank] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[CharName] varchar(32) COLLATE Latin1_General_BIN NOT NULL,
[account] varchar(32) COLLATE Latin1_General_BIN NOT NULL,
[Level] int NOT NULL,
[Exp] bigint NOT NULL,
[PlayTime] bigint NOT NULL,
[RankDt] smalldatetime NOT NULL,
[m_nJob] int NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblCombatInfo :
--
CREATE TABLE [dbo].[tblCombatInfo] (
[CombatID] int NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[Status] varchar(3) COLLATE Latin1_General_BIN NOT NULL,
[StartDt] datetime NULL,
[EndDt] datetime NULL,
[Comment] varchar(1000) COLLATE Latin1_General_BIN CONSTRAINT [DF_tblCombatInfo_Comment] DEFAULT '' NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblCombatJoinGuild :
--
CREATE TABLE [dbo].[tblCombatJoinGuild] (
[CombatID] int NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[GuildID] char(6) COLLATE Latin1_General_BIN NOT NULL,
[Status] varchar(3) COLLATE Latin1_General_BIN NOT NULL,
[ApplyDt] datetime NOT NULL,
[CombatFee] bigint NOT NULL,
[ReturnCombatFee] bigint NOT NULL,
[Reward] bigint NOT NULL,
[Point] int NOT NULL,
[RewardDt] datetime NULL,
[CancelDt] datetime NULL,
[StraightWin] int CONSTRAINT [DF_tblCombatJoinGuild_StraightWin] DEFAULT 0 NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblCombatJoinPlayer :
--
CREATE TABLE [dbo].[tblCombatJoinPlayer] (
[CombatID] int NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[GuildID] char(6) COLLATE Latin1_General_BIN NOT NULL,
[PlayerID] char(7) COLLATE Latin1_General_BIN NULL,
[Status] varchar(3) COLLATE Latin1_General_BIN CONSTRAINT [DF_tblCombatJoinPlayer_Status] DEFAULT '30' NOT NULL,
[Point] int CONSTRAINT [DF_tblCombatJoinPlayer_Point] DEFAULT 0 NOT NULL,
[Reward] bigint CONSTRAINT [DF_tblCombatJoinPlayer_Reward] DEFAULT 0 NOT NULL,
[RewardDt] datetime NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblMessenger :
--
CREATE TABLE [dbo].[tblMessenger] (
[idPlayer] char(7) COLLATE Latin1_General_BIN NOT NULL,
[idFriend] char(7) COLLATE Latin1_General_BIN NOT NULL,
[bBlock] int CONSTRAINT [DF_tblMessenger_dwState] DEFAULT 0 NOT NULL,
[chUse] char(1) COLLATE Latin1_General_BIN CONSTRAINT [DF_tblMessenger_chUse] DEFAULT 'T' NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblPocket :
--
CREATE TABLE [dbo].[tblPocket] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[idPlayer] char(7) COLLATE Latin1_General_BIN NOT NULL,
[nPocket] int NOT NULL,
[szItem] varchar(4290) COLLATE Latin1_General_BIN NOT NULL,
[szIndex] varchar(215) COLLATE Latin1_General_BIN NOT NULL,
[szObjIndex] varchar(215) COLLATE Latin1_General_BIN NOT NULL,
[bExpired] int NOT NULL,
[tExpirationDate] int NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblPocketExt :
--
CREATE TABLE [dbo].[tblPocketExt] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[idPlayer] char(7) COLLATE Latin1_General_BIN NOT NULL,
[nPocket] int NOT NULL,
[szExt] varchar(2000) COLLATE Latin1_General_BIN NOT NULL,
[szPiercing] varchar(2000) COLLATE Latin1_General_BIN NOT NULL,
[szPet] varchar(2688) COLLATE Latin1_General_BIN NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblRemoveItemFromGuildBank :
--
CREATE TABLE [dbo].[tblRemoveItemFromGuildBank] (
[nNum] int IDENTITY(1, 1) NOT NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[idGuild] char(6) COLLATE Latin1_General_BIN NOT NULL,
[ItemIndex] varchar(32) COLLATE Latin1_General_BIN NOT NULL,
[ItemSerialNum] int NOT NULL,
[ItemCnt] int CONSTRAINT [DF_tblRemoveItemFromGuildBank_ItemCnt] DEFAULT 0 NOT NULL,
[DeleteRequestCnt] int CONSTRAINT [DF_tblRemoveItemFromGuildBank_DeleteRequestCnt] DEFAULT 0 NOT NULL,
[DeleteCnt] int CONSTRAINT [DF_tblRemoveItemFromGuildBank_DeleteCnt] DEFAULT 0 NOT NULL,
[ItemFlag] int CONSTRAINT [DF_tblRemoveItemFromGuildBank_ItemFlag] DEFAULT 0 NOT NULL,
[RegisterDt] datetime CONSTRAINT [DF_tblRemoveItemFromGuildBank_RegisterDt] DEFAULT getdate() NOT NULL,
[DeleteDt] datetime NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblSkillPoint :
--
CREATE TABLE [dbo].[tblSkillPoint] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[PlayerID] char(7) COLLATE Latin1_General_BIN NULL,
[SkillID] int NOT NULL,
[SkillLv] int NOT NULL,
[SkillPosition] int NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblevent_2007_newyear :
--
CREATE TABLE [dbo].[tblevent_2007_newyear] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[regdate] datetime CONSTRAINT [DF__tblevent___regda__3EA749C6] DEFAULT getdate() NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tblevent_2007_xmas :
--
CREATE TABLE [dbo].[tblevent_2007_xmas] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[regdate] datetime CONSTRAINT [DF__tblevent___regda__3CBF0154] DEFAULT getdate() NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table tcr :
--
CREATE TABLE [dbo].[tcr] (
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[CharName] varchar(32) COLLATE Latin1_General_BIN NOT NULL,
[account] varchar(32) COLLATE Latin1_General_BIN NOT NULL,
[Level] int NOT NULL,
[Exp] bigint NOT NULL,
[PlayTime] bigint NOT NULL,
[RankDt] smalldatetime NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for table temp1 :
--
CREATE TABLE [dbo].[temp1] (
[m_dwItemId] int NULL,
[dwItemLV] int NULL
)
ON [PRIMARY]
GO
--
-- Definition for table tmp01 :
--
CREATE TABLE [dbo].[tmp01] (
[m_idPlayer] char(7) COLLATE Latin1_General_BIN NULL,
[serverindex] char(2) COLLATE Latin1_General_BIN NOT NULL,
[rid] int IDENTITY(1, 1) NOT NULL
)
ON [CHARACTER_01_DBF_FG1]
GO
--
-- Definition for stored procedure ADD_BANK_STR :
--
GO
/****** Object: Stored Procedure dbo.ADD_BANK_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].ADD_BANK_STR
@iGu CHAR(2) = 'U1',
@im_idPlayer CHAR(7) = '0000001',
@iserverindex CHAR(2) = '01',
-- BANK_TBL
@im_Bank VARCHAR(4290)= '',
@im_apIndex_Bank VARCHAR(215)= '',
@im_dwObjIndex_Bank VARCHAR(215)= '',
@im_dwGoldBank INT = 0,
@im_extBank varchar(2000) = '',
@im_BankPiercing varchar(2000) = ''
, @iszBankPet varchar(2688) = '$'
/*******************************************************
Gu 구분
S : SELECT
I : INSERT
U : UPDATE
D : DELETE
*******************************************************/
AS
set nocount on
IF @iGu = 'U1' -- 캐릭터 저장
BEGIN
UPDATE BANK_TBL
SET m_Bank = @im_Bank,
m_apIndex_Bank = @im_apIndex_Bank,
m_dwObjIndex_Bank = @im_dwObjIndex_Bank,
m_dwGoldBank = @im_dwGoldBank
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE BANK_EXT_TBL
SET m_extBank = @im_extBank,
m_BankPiercing = @im_BankPiercing,
szBankPet = @iszBankPet
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex
SELECT fError = '1', fText = 'OK'
RETURN
END
/*
정보업데이트
ex )
ADD_BANK_STR 'U1', @iGu,@im_idPlayer,@iserverindex,@im_Bank ,@im_apIndex_Bank ,@im_dwObjIndex_Bank,@im_dwGoldBank
ADD_BANK_STR 'U1','000001','01','$','$','$',1000
*/
set nocount off
RETURN
GO
--
-- Definition for stored procedure BACKSYSTEM_STR :
--
GO
/****** Object: Stored Procedure dbo.BACKSYSTEM_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROCEDURE [dbo].[BACKSYSTEM_STR]
@iGu CHAR(2) = 'S1',
@iIndex INT = 0
AS
set nocount on
IF @iGu = 'G1' -- Base GameSetting
BEGIN
select * from Base_GameSetteing
RETURN
END
ELSE
IF @iGu = 'G2' -- QUEST_TBL Data Insert
BEGIN
RETURN
END
set nocount off
GO
--
-- Definition for stored procedure BANK_STR :
--
GO
/****** Object: Stored Procedure dbo.BANK_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].BANK_STR
@iGu CHAR(1) ,
@im_idPlayer CHAR(7) = '0000001',
@iserverindex CHAR(2) = '01',
@im_BankPw CHAR(4)
AS
set nocount on
IF @iGu = 'U'
BEGIN
UPDATE BANK_TBL
SET m_BankPw = @im_BankPw
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
RETURN
END
/*
BANK 비밀번호 저장하기
ex )
BANK_STR 'U',@im_idPlayer,@iserverindex,@im_BankPw
BANK_STR 'U','000001','01','1234'
*/
GO
--
-- Definition for stored procedure CHARACTER_DELETE_STR :
--
GO
/****** Object: Stored Procedure dbo.CHARACTER_DELETE_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].CHARACTER_DELETE_STR
AS
DECLARE @serverindex char(2)
DECLARE Delete_Server CURSOR FOR
SELECT serverindex FROM CHARACTER_TBL GROUP BY serverindex ORDER BY serverindex
OPEN Delete_Server
FETCH NEXT FROM Delete_Server
INTO @serverindex
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE CHARACTER_TBL WHERE isblock='D' AND End_Time <= convert(char(8),DATEADD(d,-10,getdate()),112) and serverindex = @serverindex
DECLARE @name varchar(256)
DECLARE Delete_Cursor CURSOR FOR
SELECT B.name
FROM syscolumns A,sysobjects B
WHERE A.id = B.id
AND B.name NOT IN('CHARACTER_TBL','MESSENGER_TBL')
AND A.name = 'm_idPlayer'
AND B.type='U'
ORDER BY B.name
OPEN Delete_Cursor
FETCH NEXT FROM Delete_Cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name + ' TABLE DELETE'
EXEC('DELETE ' + @name + ' WHERE m_idPlayer NOT IN (SELECT m_idPlayer FROM CHARACTER_TBL where serverindex = ''' + @serverindex + ''') and serverindex = ''' + @serverindex + '''')
FETCH NEXT FROM Delete_Cursor
INTO @name
END
PRINT 'NOTE DELETE'
DELETE TAG_TBL WHERE f_idPlayer NOT IN (SELECT m_idPlayer FROM CHARACTER_TBL where serverindex = @serverindex) and serverindex = @serverindex
PRINT 'FRIEND LIST DELETE(OTHER USER)'
EXEC MESSENGER_STR 'D2','',@serverindex
CLOSE Delete_Cursor
DEALLOCATE Delete_Cursor
FETCH NEXT FROM Delete_Server
INTO @serverindex
END
CLOSE Delete_Server
DEALLOCATE Delete_Server
RETURN
GO
--
-- Definition for stored procedure CHARACTER_STR :
--
GO
/****** Object: Stored Procedure dbo.CHARACTER_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE proc [dbo].[CHARACTER_STR]
@iGu CHAR(2) = 'S1',
@im_idPlayer CHAR(7) = '0000001',
@iserverindex CHAR(2) = '01',
/**********************************************
INSERT 용
**********************************************/
-- CHARACTER_TBL
@iaccount VARCHAR(32) = '',
@im_szName VARCHAR(32) = '',
@iplayerslot INT = 0,
@idwWorldID INT = 0,
@im_dwIndex INT = 0,
@im_vPos_x REAL = 0,
@im_vPos_y REAL = 0,
@im_vPos_z REAL = 0,
@im_szCharacterKey VARCHAR(32) = '',
@im_dwSkinSet INT = 0,
@im_dwHairMesh INT = 0,
@im_dwHairColor INT = 0,
@im_dwHeadMesh INT = 0,
@im_dwSex INT = 0,
/**********************************************
UPDATE 용
**********************************************/
-- CHARACTER_TBL
@im_vScale_x REAL = 0,
@im_dwMotion INT = 0,
@im_fAngle REAL = 0,
@im_nHitPoint INT = 0,
@im_nManaPoint INT = 0,
@im_nFatiguePoint INT = 0,
@im_dwRideItemIdx INT = 0,
@im_dwGold INT = 0,
@im_nJob INT = 0,
@im_pActMover VARCHAR(50) = '',
@im_nStr INT = 0,
@im_nSta INT = 0,
@im_nDex INT = 0,
@im_nInt INT = 0,
@im_nLevel INT = 0,
@im_nExp1 BIGINT = 0,
@im_nExp2 BIGINT = 0,
@im_aJobSkill VARCHAR(500) ='',
@im_aLicenseSkill VARCHAR(500) ='',
@im_aJobLv VARCHAR(500) ='',
@im_dwExpertLv INT = 0,
@im_idMarkingWorld INT = 0,
@im_vMarkingPos_x REAL = 0,
@im_vMarkingPos_y REAL = 0,
@im_vMarkingPos_z REAL = 0,
@im_nRemainGP INT = 0,
@im_nRemainLP INT = 0,
@im_nFlightLv INT = 0,
@im_nFxp INT = 0,
@im_nTxp INT = 0,
@im_lpQuestCntArray VARCHAR(3072)= '',
@im_chAuthority CHAR(1) = '',
@im_dwMode INT = 0,
@im_idparty INT = 0,
--@im_nNumKill INT = 0,
@im_idMuerderer INT = 0,
--@im_nSlaughter INT = 0,
@im_nFame INT = 0,
@im_nDeathExp BIGINT = 0,
@im_nDeathLevel INT = 0,
@im_dwFlyTime INT = 0,
@im_nMessengerState INT = 0,
@iTotalPlayTime INT = 0,
---------- Additional end ----------
-- PK Variable
@im_nPKValue int=0,
@im_dwPKPropensity int=0,
@im_dwPKExp int=0,
---------- Additional end ----------
-- CARD_CUBE_TBL
@im_Card VARCHAR(1980)= '',
@im_Index_Card VARCHAR(215) = '',
@im_ObjIndex_Card VARCHAR(215) = '',
@im_Cube VARCHAR(1980)= '',
@im_Index_Cube VARCHAR(215) = '',
@im_ObjIndex_Cube VARCHAR(215) = '',
-- INVENTORY_TBL
@im_Inventory VARCHAR(6940)= '',
@im_apIndex VARCHAR(345) = '',
@im_adwEquipment VARCHAR(135) = '',
@im_dwObjIndex VARCHAR(345) = '',
-- TASKBAR_TBL
@im_aSlotApplet VARCHAR(3100)= '',
-- TASKBAR_ITEM_TBL
@im_aSlotItem VARCHAR(6885)= '',
-- TASKBAR_TBL
@im_aSlotQueue VARCHAR(225)= '',
@im_SkillBar SMALLINT = 0,
-- BANK_TBL
@im_Bank VARCHAR(4290)= '',
@im_apIndex_Bank VARCHAR(215)= '',
@im_dwObjIndex_Bank VARCHAR(215)= '',
@im_dwGoldBank INT = 0,
@im_nFuel INT = -1,
@im_tmAccFuel INT = 0,
@im_dwSMTime VARCHAR(2560)='',
@iSkillInfluence varchar(2048) ='',
@im_dwSkillPoint INT = 0,
@im_aCompleteQuest varchar(1024) = '',
@im_extInventory varchar(2000) = '',
@im_extBank varchar(2000) = '',
@im_InventoryPiercing varchar(2000) = '',
@im_BankPiercing varchar(2000) = '',
@im_dwReturnWorldID INT = 1,
@im_vReturnPos_x REAL = 0,
@im_vReturnPos_y REAL = 0,
@im_vReturnPos_z REAL= 0
-------------- ( Version 7 : Skill Update)
, @im_SkillPoint int=0
, @im_SkillLv int=0
, @im_SkillExp bigint=0
-------------- (Additional end)
, @idwEventFlag bigint=0,
@idwEventTime int=0,
@idwEventElapsed int=0
-------------- (Additional end)
----------Additional end----------
-- PVP (Additional end)
,@im_nAngelExp bigint=0
,@im_nAngelLevel int=0
----------Additional end ----------
--------------- Version 9 Relation of additional partial Pet
,@iszInventoryPet varchar(2688) = '$'
,@iszBankPet varchar(2688) = '$'
,@im_dwPetId int = -1
,@im_nExpLog int = 0
,@im_nAngelExpLog int = 0
--------- Ver 11 Coupon
,@im_nCoupon int = 0
/*******************************************************
Gu Dividing
S : SELECT
I : INSERT
U : UPDATE
D : DELETE
2005.04.11 updated
ALTER TABLE CHARACTER_TBL ADD m_aCompleteQuest varchar(1024) NULL
ALTER TABLE CHARACTER_TBL ALTER COLUMN m_lpQuestCntArray VARCHAR(3072) NULL
*******************************************************/
AS
set nocount on
declare @last_connect tinyint
set @last_connect = 1
IF @iGu = 'S2' -- Is a player list which follows in the slot cuts coil information bringing,
BEGIN
IF @iaccount = '' OR @im_szName = ''
BEGIN
SELECT m_chAuthority = '',fError = '1', fText = 'Wrong password'
RETURN
END
SELECT A.dwWorldID,
A.m_szName,
A.playerslot,
A.End_Time,
A.BlockTime,
A.m_dwIndex,
A.m_idPlayer,
A.m_idparty,
A.m_dwSkinSet,
A.m_dwHairMesh,
A.m_dwHeadMesh,
A.m_dwHairColor,
A.m_dwSex,
A.m_nJob,
A.m_nLevel,
A.m_vPos_x,
A.m_vPos_y,
A.m_vPos_z,
A.m_nStr,
A.m_nSta,
A.m_nDex,
A.m_nInt,
--A.m_nSlaughter, -- raidersAddition 2005.5.11
A.m_aJobLv,
A.m_chAuthority,
A.m_idCompany,
A.m_nMessengerState,
B.m_Inventory,
B.m_apIndex,
B.m_adwEquipment,
B.m_dwObjIndex,
m_idGuild = CASE WHEN C.m_idGuild IS NULL THEN '0' ELSE C.m_idGuild END ,
m_idWar = CASE WHEN C.m_idWar IS NULL THEN '0' ELSE C.m_idWar END,
D.m_extInventory,
D.m_InventoryPiercing,
A.m_idCompany,
last_connect = @last_connect
FROM CHARACTER_TBL A, INVENTORY_TBL B,GUILD_MEMBER_TBL C, INVENTORY_EXT_TBL D
WHERE A.m_idPlayer = B.m_idPlayer
AND A.serverindex= B.serverindex
AND B.m_idPlayer = D.m_idPlayer
AND B.serverindex = D.serverindex
AND D.m_idPlayer *= C.m_idPlayer
AND D.serverindex *= C.serverindex
AND A.isblock = 'F'
AND A.account = @iaccount
AND A.serverindex= @iserverindex
ORDER BY A.playerslot
RETURN
END
/*
Is a player list which follows in the slot cuts coil information bringing,
ex )
CHARACTER_STR 'S2',@im_idPlayer (iMode),@iserverindex,@iaccount,@im_szName (iPassword)
CHARACTER_STR 'S2','0','02','seghope','1234'
*/
ELSE
IF @iGu = 'S3' -- The server initially idPlayer of characters which will execute all the branch, coming,
BEGIN
SELECT m_szName, m_idPlayer,m_idCompany
FROM CHARACTER_TBL
WHERE serverindex = @iserverindex
-- AND isblock = 'F'
ORDER BY m_idPlayer
RETURN
END
/*
The server initially idPlayer of characters which will execute all the branch, coming,
ex )
CHARACTER_STR 'S3','',@iserverindex
CHARACTER_STR 'S3','','02'
*/
ELSE
IF @iGu = 'S4' -- The item confirmation which will add
BEGIN
/* if (getdate() >= '2007-10-29 00:00:00' and getdate() <= '2007-10-31 23:59:59')
begin
if not exists (select * from EOCRM.MANAGE_DBF.dbo.tblEvent_Halloween_2007 where serverindex = @iserverindex and m_idPlayer = @im_idPlayer)
begin
declare @q1 nvarchar(4000)
declare @ktime bigint
select @ktime = cast(replace(replace(replace(convert(char(16), dateadd(hh, 72, getdate()), 21), '-', ''), ' ', ''), ':', '') as bigint)
set @q1 = '
insert into ITEM_SEND_TBL (m_idPlayer, serverindex, Item_Name, Item_count, m_nAbilityOption, m_bItemResist, m_nResistAbilityOption, m_bCharged, m_dwKeepTime)
select @im_idPlayer, @iserverindex, ''26425'', 1, 0, 0, 0, 0, @ktime'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2), @ktime bigint', @im_idPlayer, @iserverindex, @ktime
set @q1 = '
insert into EOCRM.MANAGE_DBF.dbo.tblEvent_Halloween_2007 (serverindex, m_idPlayer)
select @iserverindex, @im_idPlayer'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
set @q1 = '
SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption, m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, m_dwKeepTime, nRandomOptItemId
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
end
else
begin
SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption, m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, m_dwKeepTime, nRandomOptItemId
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0
end
end
else
begin
SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption, m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, m_dwKeepTime, nRandomOptItemId
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0
end*/
declare @q1 nvarchar(4000)
/* if ((getdate() >= '2007-12-23 00:00:00') and (getdate() <= '2007-12-25 23:59:59'))
begin
if not exists (select * from tblevent_2007_xmas where m_idPlayer = @im_idPlayer)
begin
declare @m_dwSex int
declare @xmas_Item varchar(32)
select @m_dwSex = m_dwSex from CHARACTER_TBL where m_idPlayer = @im_idPlayer
if @m_dwSex = 0
begin
set @xmas_Item = '26339'
end
else
begin
set @xmas_Item = '26340'
end
set @q1 = 'insert into ITEM_SEND_TBL(m_idPlayer, serverindex, Item_Name, Item_count, m_nAbilityOption, idSender)
select @im_idPlayer, ''01'', @xmas_Item, 1, 0, ''0000000'''
exec sp_executesql @q1, N'@im_idPlayer char(7), @xmas_Item varchar(32)', @im_idPlayer, @xmas_Item
insert into tblevent_2007_xmas (m_idPlayer)
select @im_idPlayer
set @q1 = 'SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption,
m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, m_dwKeepTime
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
end
else
begin
set @q1 = 'SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption,
m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, m_dwKeepTime
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
end
end*/
if ((getdate() >= '2007-12-31 00:00:00') and (getdate() <= '2007-12-31 23:59:59'))
begin
if not exists (select * from tblevent_2007_newyear where m_idPlayer = @im_idPlayer)
begin
declare @newyear_Item varchar(32)
set @newyear_Item = '2904'
set @q1 = 'insert into ITEM_SEND_TBL(m_idPlayer, serverindex, Item_Name, Item_count, m_nAbilityOption, idSender)
select @im_idPlayer, ''01'', @newyear_Item, 5, 0, ''0000000'''
exec sp_executesql @q1, N'@im_idPlayer char(7), @newyear_Item varchar(32)', @im_idPlayer, @newyear_Item
insert into tblevent_2007_newyear (m_idPlayer)
select @im_idPlayer
set @q1 = 'SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption,
m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, adwItemId4, m_dwKeepTime, nRandomOptItemId
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
end
else
begin
set @q1 = 'SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption,
m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, adwItemId4, m_dwKeepTime, nRandomOptItemId
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
end
end
else
begin
set @q1 = 'SELECT Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption,
m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, adwItemId4, m_dwKeepTime, nRandomOptItemId
FROM ITEM_SEND_TBL
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND ItemFlag = 0'
exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
end
RETURN
END
/*
Item confirmation
ex )
CHARACTER_STR 'S4',@im_idPlayer,@iserverindex
CHARACTER_STR 'S4','000001','01'
*/
ELSE
IF @iGu = 'S5' -- From table after item utmost urgency item elimination
BEGIN
-- DELETE ITEM_SEND_TBL
UPDATE ITEM_SEND_TBL SET ProvideDt=getdate(), ItemFlag=1
WHERE m_nNo = @iplayerslot
IF @@ROWCOUNT = 0
SELECT fError = '0'
ELSE
SELECT fError = '1'
RETURN
END
/*
From table after item utmost urgency item elimination
ex )
CHARACTER_STR 'S5',@im_idPlayer,@iserverindex,@iaccount
CHARACTER_STR 'S5','000001','01','Broom',1,1
*/
ELSE
IF @iGu = 'S6' -- The item confirmation which will eliminate
BEGIN
SELECT Item_Name,
Item_count,
m_nAbilityOption,
m_nNo,
State,
m_bItemResist,
m_nResistAbilityOption
FROM ITEM_REMOVE_TBL
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND ItemFlag = 0
RETURN
END
/*
Item confirmation
ex )
CHARACTER_STR 'S6',@im_idPlayer,@iserverindex
CHARACTER_STR 'S6','000001','01'
*/
ELSE
IF @iGu = 'S7' -- After item eliminating from table item elimination
BEGIN
-- DELETE ITEM_REMOVE_TBL
UPDATE ITEM_REMOVE_TBL SET DeleteDt=getdate(), ItemFlag=1
WHERE m_nNo = @iplayerslot
IF @@ROWCOUNT = 0
SELECT fError = '0'
ELSE
SELECT fError = '1'
RETURN
END
/*
From table after item utmost urgency item elimination
ex )
CHARACTER_STR 'S7',@im_idPlayer,@iserverindex,@iaccount
CHARACTER_STR 'S7','000001','01','Broom',1,1
*/
IF @iGu = 'S8' -- The data whole bringing,
BEGIN
PRINT @iaccount
DECLARE @om_chLoginAuthority CHAR(1)
-- Bank information character stars bringing
SET @om_chLoginAuthority = 'F'
--SELECT @om_chLoginAuthority = m_chLoginAuthority
-- FROM ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL
--WHERE lower(account) = lower(@iaccount)
--INSERT INTO [ONLINE_TBL] ([account] ,[name]) VALUES(@iaccount,@im_idPlayer)
SELECT m_chLoginAuthority = @om_chLoginAuthority,
A.account,
A.m_idPlayer,
A.playerslot,
A.serverindex,
A.dwWorldID,
A.m_szName,
A.m_dwIndex,
A.m_vScale_x,
A.m_dwMotion,
A.m_vPos_x,
A.m_vPos_y,
A.m_vPos_z,
A.m_fAngle,
A.m_szCharacterKey,
A.m_idPlayer,
A.m_nHitPoint,
A.m_nManaPoint,
A.m_nFatiguePoint,
A.m_nFuel,
A.m_dwSkinSet,
A.m_dwHairMesh,
A.m_dwHairColor,
A.m_dwHeadMesh,
A.m_dwSex,
A.m_dwRideItemIdx,
A.m_dwGold,
A.m_nJob,
A.m_pActMover,
A.m_nStr,
A.m_nSta,
A.m_nDex,
A.m_nInt,
A.m_nLevel,
A.m_nMaximumLevel,
A.m_nExp1,
A.m_nExp2,
A.m_aJobSkill,
A.m_aLicenseSkill,
A.m_aJobLv,
A.m_dwExpertLv,
A.m_idMarkingWorld,
A.m_vMarkingPos_x,
A.m_vMarkingPos_y,
A.m_vMarkingPos_z,
A.m_nRemainGP,
A.m_nRemainLP,
A.m_nFlightLv,
A.m_nFxp,
A.m_nTxp,
A.m_lpQuestCntArray,
m_aCompleteQuest = ISNULL(A.m_aCompleteQuest,'$'),
A.m_chAuthority,
A.m_dwMode,
A.m_idparty,
A.m_idCompany,
--A.m_nNumKill,
A.m_idMuerderer,
--A.m_nSlaughter,
A.m_nFame,
A.m_nDeathExp,
A.m_nDeathLevel,
A.m_dwFlyTime,
A.m_nMessengerState,
A.End_Time,
A.BlockTime,
A.blockby,
A.isblock,
A.TotalPlayTime,
A.CreateTime,
A.m_dwSkillPoint,
B.m_aSlotApplet,
B.m_aSlotQueue,
B.m_SkillBar,
C.m_aSlotItem,
D.m_Inventory,
D.m_apIndex,
D.m_adwEquipment,
D.m_dwObjIndex,
m_idGuild = ISNULL(G.m_idGuild,'0'),
m_idWar = ISNULL(G.m_idWar,'0'),
A.m_tmAccFuel,
A.m_tGuildMember,
m_dwSMTime = ISNULL(H.m_dwSMTime,'NULL') ,
SkillInfluence = ISNULL(E.SkillInfluence,'$'),
F.m_extInventory,
F.m_InventoryPiercing,
A.m_dwReturnWorldID,
A.m_idCompany,
A.m_vReturnPos_x,
A.m_vReturnPos_y,
A.m_vReturnPos_z
------------------ ( ADD : Version7-Skill System)
, m_SkillPoint = SkillPoint
, m_SkillLv = SkillLv
, m_SkillExp = SkillExp
-------------- (Additional start)
, A.dwEventFlag,
A.dwEventTime,
A.dwEventElapsed
-------------- (Additional start)
----------Additional start ----------
, A.PKValue as m_nPKValue,
A.PKPropensity as m_dwPKPropensity,
A.PKExp as m_dwPKExp
----------Additional end ----------
----------Additional end ----------
,AngelExp as m_nAngelExp
,AngelLevel as m_nAngelLevel
----------Additional end ----------
------------------- Version9 Pet
, F.szInventoryPet as szInventoryPet
, A.m_dwPetId
, A.m_nExpLog, A.m_nAngelExpLog
------------- Ver 11 Coupon
, m_nCoupon
, last_connect = @last_connect
FROM CHARACTER_TBL A,
TASKBAR_TBL B,
TASKBAR_ITEM_TBL C,
INVENTORY_TBL D,
SKILLINFLUENCE_TBL E,
INVENTORY_EXT_TBL F,
GUILD_MEMBER_TBL G,
BILING_ITEM_TBL H
WHERE A.m_idPlayer = @im_idPlayer
AND A.serverindex = @iserverindex
AND A.m_idPlayer = B.m_idPlayer
AND A.serverindex = B.serverindex
AND B.m_idPlayer = C.m_idPlayer
AND B.serverindex = C.serverindex
AND C.m_idPlayer = D.m_idPlayer
AND C.serverindex = D.serverindex
AND D.m_idPlayer = E.m_idPlayer
AND D.serverindex = E.serverindex
AND E.m_idPlayer = F.m_idPlayer
AND E.serverindex = F.serverindex
AND F.serverindex *= G.serverindex
AND F.m_idPlayer *= G.m_idPlayer
AND F.serverindex *= H.serverindex
AND F.m_idPlayer *= H.m_idPlayer
AND A.account = lower(@iaccount)
-- Bank information account stars bringing
-- DECLARE @bank TABLE (m_idPlayer CHAR(6),serverindex CHAR(2),playerslot INT)
--
-- INSERT @bank
-- (m_idPlayer,serverindex,playerslot)
-- SELECT m_idPlayer,serverindex,playerslot
-- FROM CHARACTER_TBL
-- WHERE account = @iaccount
-- AND isblock = 'F'
-- ORDER BY playerslot
SELECT a.m_idPlayer,
c.playerslot,
a.m_Bank,
a.m_apIndex_Bank,
a.m_dwObjIndex_Bank,
a.m_dwGoldBank,
a.m_BankPw,
b.m_extBank,
b.m_BankPiercing, b.szBankPet
FROM dbo.BANK_TBL a,
dbo.BANK_EXT_TBL b,
dbo.CHARACTER_TBL c
WHERE a.m_idPlayer = b.m_idPlayer
AND a.serverindex = b.serverindex
AND b.m_idPlayer = c.m_idPlayer
AND b.serverindex = c.serverindex
AND c.account = lower(@iaccount)
AND c.isblock = 'F'
ORDER BY c.playerslot
------------- Ver11 Pocket
SELECT a.nPocket,
a.szItem,
a.szIndex,
a.szObjIndex,
a.bExpired,
a.tExpirationDate,
b.szExt,
b.szPiercing,
b.szPet
FROM tblPocket as a inner join tblPocketExt as b
on a.serverindex = b.serverindex AND a.idPlayer = b.idPlayer AND a.nPocket = b.nPocket
WHERE a.serverindex = @iserverindex AND a.idPlayer = @im_idPlayer
ORDER BY a.nPocket
RETURN
END
/*
The data whole bringing, New
ex )
CHARACTER_STR 'S8',@im_idPlayer,@iserverindex,@iaccount
CHARACTER_STR 'S8','425120','01','ata3k'
*/
ELSE
IF @iGu = 'U1' -- Character store
BEGIN
--DELETE FROM ONLINE_TBL WHERE name = @im_idPlayer
UPDATE CHARACTER_TBL
SET dwWorldID = @idwWorldID,
m_dwIndex = @im_dwIndex,
m_dwSex = @im_dwSex,
m_vScale_x = @im_vScale_x,
m_dwMotion = @im_dwMotion,
m_vPos_x = @im_vPos_x,
m_vPos_y = @im_vPos_y,
m_vPos_z = @im_vPos_z,
m_dwHairMesh = @im_dwHairMesh,
m_dwHairColor = @im_dwHairColor,
m_dwHeadMesh = @im_dwHeadMesh, -- 2004/11/08 Addition
m_fAngle = 0, --@im_fAngle,
m_szCharacterKey = @im_szCharacterKey,
m_nHitPoint = @im_nHitPoint,
m_nManaPoint = @im_nManaPoint,
m_nFatiguePoint = @im_nFatiguePoint,
m_nFuel = @im_nFuel,
m_dwRideItemIdx = @im_dwRideItemIdx,
m_dwGold = @im_dwGold,
m_nJob = @im_nJob,
m_pActMover = @im_pActMover,
m_nStr = @im_nStr,
m_nSta = @im_nSta,
m_nDex = @im_nDex,
m_nInt = @im_nInt,
m_nLevel = @im_nLevel,
m_nMaximumLevel = CASE WHEN m_nMaximumLevel < @im_nLevel THEN @im_nLevel ELSE m_nMaximumLevel END,
m_nExp1 = @im_nExp1,
m_nExp2 = @im_nExp2,
m_aJobSkill = @im_aJobSkill,
m_aLicenseSkill = @im_aLicenseSkill,
m_aJobLv = @im_aJobLv,
m_dwExpertLv = @im_dwExpertLv,
m_idMarkingWorld = @im_idMarkingWorld,
m_vMarkingPos_x = @im_vMarkingPos_x,
m_vMarkingPos_y = @im_vMarkingPos_y,
m_vMarkingPos_z = @im_vMarkingPos_z,
m_nRemainGP = @im_nRemainGP,
m_nRemainLP = @im_nRemainLP,
m_nFlightLv = @im_nFlightLv,
m_nFxp = @im_nFxp,
m_nTxp = @im_nTxp,
m_lpQuestCntArray = @im_lpQuestCntArray,
m_aCompleteQuest = @im_aCompleteQuest,
m_dwMode = @im_dwMode,
m_idparty = @im_idparty,
--m_nNumKill = @im_nNumKill,
m_idMuerderer = @im_idMuerderer,
--m_nSlaughter = @im_nSlaughter ,
m_nFame = @im_nFame,
m_nDeathExp = @im_nDeathExp,
m_nDeathLevel = @im_nDeathLevel,
-- m_dwFlyTime = m_dwFlyTime + @im_dwFlyTime,
m_dwFlyTime = @im_dwFlyTime,
m_nMessengerState = @im_nMessengerState,
TotalPlayTime = TotalPlayTime + @iTotalPlayTime,
m_tmAccFuel = @im_tmAccFuel,
m_dwSkillPoint = @im_dwSkillPoint,
m_dwReturnWorldID= @im_dwReturnWorldID,
m_idCompany = 0,
m_vReturnPos_x = @im_vReturnPos_x,
m_vReturnPos_y = @im_vReturnPos_y,
m_vReturnPos_z = @im_vReturnPos_z
-------------- (ADD: Version7-SkillSystem)
, SkillPoint = @im_SkillPoint
, SkillLv = @im_SkillLv
, SkillExp = @im_SkillExp
-------------- (Additional part)
, dwEventFlag =@idwEventFlag,
dwEventTime =@idwEventTime,
dwEventElapsed =@idwEventElapsed
-------------- (Additional part)
----------Additional start ----------
, PKValue = @im_nPKValue,
PKPropensity = @im_dwPKPropensity,
PKExp = @im_dwPKExp
----------Additional end ----------
----------Additional start ----------
, AngelExp= @im_nAngelExp
, AngelLevel= @im_nAngelLevel
----------Additional end ----------
--------------------- Version9 Pet
, m_dwPetId = @im_dwPetId
, m_nExpLog = @im_nExpLog
, m_nAngelExpLog = @im_nAngelExpLog
------------ Ver 11
, m_nCoupon = @im_nCoupon
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
-- if object_id('QUEST_TBL') is not null
-- EXEC QUEST_STR 'A1',@im_idPlayer,@iserverindex,@im_lpQuestCntArray
--Relation of [song] present three request fact Bill rings
IF @im_dwSMTime > ''
BEGIN
IF EXISTS(SELECT * FROM BILING_ITEM_TBL WHERE m_idPlayer= @im_idPlayer AND serverindex = @iserverindex)
UPDATE BILING_ITEM_TBL
SET m_dwSMTime = @im_dwSMTime
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
ELSE
INSERT BILING_ITEM_TBL
(m_idPlayer,serverindex,m_dwSMTime)
VALUES
(@im_idPlayer,@iserverindex,@im_dwSMTime)
END
ELSE
DELETE BILING_ITEM_TBL
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
-- UPDATE CARD_CUBE_TBL
-- SET m_Card = @im_Card,
-- m_apIndex_Card = @im_Index_Card,
-- m_dwObjIndex_Card= @im_ObjIndex_Card,
-- m_Cube = @im_Cube,
-- m_apIndex_Cube = @im_Index_Cube,
-- m_dwObjIndex_Cube=@im_ObjIndex_Cube
-- WHERE m_idPlayer = @im_idPlayer
-- AND serverindex = @iserverindex
UPDATE INVENTORY_TBL
SET m_Inventory = @im_Inventory,
m_apIndex = @im_apIndex,
m_adwEquipment = @im_adwEquipment,
m_dwObjIndex = @im_dwObjIndex
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE TASKBAR_TBL
SET m_aSlotApplet = @im_aSlotApplet,
m_aSlotQueue = @im_aSlotQueue,
m_SkillBar = @im_SkillBar
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE TASKBAR_ITEM_TBL
SET m_aSlotItem = @im_aSlotItem
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE BANK_TBL
SET m_Bank = @im_Bank,
m_apIndex_Bank = @im_apIndex_Bank,
m_dwObjIndex_Bank = @im_dwObjIndex_Bank,
m_dwGoldBank = @im_dwGoldBank
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE SKILLINFLUENCE_TBL
SET SkillInfluence = @iSkillInfluence
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE INVENTORY_EXT_TBL
SET m_extInventory = @im_extInventory,
m_InventoryPiercing = @im_InventoryPiercing
, szInventoryPet = @iszInventoryPet
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex
UPDATE BANK_EXT_TBL
SET m_extBank = @im_extBank,
m_BankPiercing = @im_BankPiercing
, szBankPet = @iszBankPet
WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex
SELECT fError = '1', fText = 'OK'
RETURN
END
/*
Information update
ex )
CHARACTER_STR 'U1', ALL ...
CHARACTER_STR 'U1','000001','01' ...
*/
ELSE
IF @iGu = 'U2' -- Total use time correction
BEGIN
UPDATE CHARACTER_TBL
SET TotalPlayTime = TotalPlayTime + @iplayerslot
WHERE m_szName = @im_szName
AND serverindex = @iserverindex
RETURN
END
/*
Total use time correction
ex )
CHARACTER_STR 'U2','',@iserverindex,'',@im_szName,@iplayerslot (@iTotalPlayTime)
CHARACTER_STR 'U2','','01','','beat',10234
*/
ELSE
IF @iGu = 'U3' --Total use time correction new
BEGIN
UPDATE CHARACTER_TBL
SET TotalPlayTime = TotalPlayTime + @iplayerslot
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
RETURN
END
/*
Total use time correction (new)
ex )
CHARACTER_STR 'U3',@im_idPlayer,@iserverindex,'','',@iplayerslot (@iTotalPlayTime)
CHARACTER_STR 'U3','000001','01','','',10234
*/
ELSE
IF @iGu = 'U4' --Character life fringe land
BEGIN
IF EXISTS(SELECT m_idPlayer FROM CHARACTER_TBL WHERE lower(m_szName) = lower(@im_szName) AND serverindex = @iserverindex)
BEGIN
SELECT fError = '0'
END
ELSE
BEGIN
UPDATE CHARACTER_TBL
SET m_szName = @im_szName
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
SELECT fError = '1'
END
RETURN
END
/*
Character life fringe land
ex)
CHARACTER_STR ' U4' @im_idPlayer, @iserverindex, @iaccount and @im_szName
CHARACTER_STR ' U4' ' 000001' ' 01' ' ' ' The morning star princess'
*/
ELSE
IF @iGu = 'D1' -- Character elimination
BEGIN
-- The part which the box tries to close once in the class arctic regions #1 2007-03-21
IF @im_szName = ''
BEGIN
SELECT fError = '1', fText = 'Resident number error'
RETURN
END
if not exists (select * from CHARACTER_TBL where m_idPlayer = @im_idPlayer and account = @iaccount and serverindex = @iserverindex)
begin
select fError = '1'
return
end
DECLARE @Exists int
-- IF EXISTS(SELECT name from syscolumns where name='m_idPlayer' AND collation= 'Japanese_BIN')
-- BEGIN
-- IF EXISTS(SELECT * FROM ACCOUNT_DBF.dbo.ACCOUNT_TBL WHERE account = @iaccount AND (password = @im_szName OR member = 'B' ))
SET @Exists = 1
-- ELSE
-- SET @Exists = 0
-- END
-- ELSE
-- BEGIN
-- IF EXISTS(SELECT * FROM ACCOUNT_DBF.dbo.ACCOUNT_TBL WHERE account = @iaccount AND (id_no2 = @im_szName OR member = 'B' ))
-- SET @Exists = 1
-- ELSE
-- SET @Exists = 0
-- END
IF @Exists > 0
BEGIN
DECLARE @currDate char(12)
SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)
IF EXISTS(SELECT m_idPlayer FROM GUILD_MEMBER_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND m_idWar > 0)
BEGIN
SELECT fError = '3', fText = 'In before guild'
RETURN
END
ELSE
BEGIN
UPDATE CHARACTER_TBL
SET isblock = 'D',
End_Time = @currDate ,
BlockTime = LEFT(@currDate,8)
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE MESSENGER_TBL
SET State = 'D'
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
UPDATE MESSENGER_TBL
SET State = 'D'
WHERE f_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
/****************************************************************************************/
/** 200506 event **/
/****************************************************************************************/
-- if (not exists(select account from RANKING_DBF.dbo.last_1_month_tbl where account = @iaccount)
-- and convert(char(10),getdate(),120) between '2005-07-05' and '2005-07-12')
--
-- begin --: Of 1 or more month the account objective type writing which is not connection experience
-- declare @cash int,@get_cach int
-- select @cash = case when m_nLevel between 10 and 19 then 500
-- when m_nLevel between 20 and 39 then 1000
-- when m_nLevel between 40 and 49 then 1500
-- when m_nLevel between 50 and 59 then 2000
-- when m_nLevel between 60 and 69 then 2500
-- when m_nLevel >= 70 then 3000
-- else 0 end
-- from CHARACTER_TBL
-- where m_idPlayer = @im_idPlayer and serverindex = @iserverindex
--
-- select @get_cach = isnull(sum(amount),0)
-- from RANKING_DBF.dbo.event_member_tbl
-- where account = @iaccount
-- group by account
--
-- if @cash + @get_cach > 5000
-- set @cash = 5000 - @get_cach
--
-- if @cash > 0
--
-- begin
-- declare @retcode int
--
-- if not exists(select * from BILLING.QLORD_MASTER.dbo.BX_TG_USERINFO where USER_ID = @iaccount)
-- exec BILLING.QLORD_MASTER.dbo.BX_SP_PROCESS_USERINFO @iaccount,@iaccount,'FLYF',' ','1111111111118',' ',' ',''
--
-- exec @retcode = BILLING.QLORD_MASTER.dbo.BX_SP_INSERT_BONUS_IN @iaccount,@iaccount,'FLYF','IN00000004',@cash,'Character compensation event',0,''
--
-- if @retcode <> 1
-- set @cash = 0
--
-- insert RANKING_DBF.dbo.event_member_tbl
-- (account,amount,m_idPlayer,serverindex,retcode,date)
-- values
-- (@iaccount,@cash,@im_idPlayer,@iserverindex,@retcode,getdate())
--
-- end
-- end
/****************************************************************************************/
/****************************************************************************************/
/****************************************************************************************/
-- The part which the box tries to close once in the class arctic regions #2 2007-03-21
IF EXISTS(SELECT m_idPlayer FROM GUILD_MEMBER_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex)
BEGIN
SELECT fError = '4', fText = m_idGuild FROM GUILD_MEMBER_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex
RETURN
END
ELSE
BEGIN
SELECT fError = '0', fText = 'DELETE OK'
RETURN
END
END
END
ELSE
BEGIN
SELECT fError = '1', fText = 'Resident number error'
RETURN
END
-- At the price which is not eliminated unconditionally return
select fError = '1', fText = 'Resident number error'
END
/*
Character elimination
ex )
CHARACTER_STR 'D1',@im_idPlayer,@iserverindex,@iaccount (isblock)
CHARACTER_STR 'D1','001068','01','ata3k','1019311'
*/
ELSE
IF @iGu = 'I1' -- Initially information input
BEGIN
IF EXISTS(SELECT m_szName FROM CHARACTER_TBL
WHERE m_szName = @im_szName AND serverindex = @iserverindex)
BEGIN
SELECT fError = '0', fText = 'Character duplication!'
RETURN
END
ELSE
BEGIN
DECLARE
@om_idPlayer CHAR (7) ,
@om_vScale_x REAL ,
@om_dwMotion INT ,
@om_fAngle REAL ,
@om_nHitPoint INT ,
@om_nManaPoint INT ,
@om_nFatiguePoint INT ,
@om_dwRideItemIdx INT ,
@om_dwGold INT ,
@om_nJob INT ,
@om_pActMover VARCHAR(50) ,
@om_nStr INT ,
@om_nSta INT ,
@om_nDex INT ,
@om_nInt INT ,
@om_nLevel INT ,
@om_nExp1 BIGINT ,
@om_nExp2 BIGINT ,
@om_aJobSkill VARCHAR (500),
@om_aLicenseSkill VARCHAR (500),
@om_aJobLv VARCHAR (500),
@om_dwExpertLv INT ,
@om_idMarkingWorld INT ,
@om_vMarkingPos_x REAL ,
@om_vMarkingPos_y REAL ,
@om_vMarkingPos_z REAL ,
@om_nRemainGP INT ,
@om_nRemainLP INT ,
@om_nFlightLv INT ,
@om_nFxp INT ,
@om_nTxp INT ,
@om_lpQuestCntArray VARCHAR(1024),
@om_chAuthority CHAR(1) ,
@om_dwMode INT ,
@oblockby VARCHAR(32) ,
@oTotalPlayTime INT ,
@oisblock CHAR(1) ,
@oEnd_Time CHAR(12) ,
@om_Inventory VARCHAR(6940),
@om_apIndex VARCHAR(345) ,
@om_adwEquipment VARCHAR(135) ,
@om_aSlotApplet VARCHAR(3100),
@om_aSlotItem VARCHAR(6885),
@om_aSlotQueue VARCHAR(225),
@om_SkillBar SMALLINT,
@om_dwObjIndex VARCHAR(345) ,
@om_Card VARCHAR(1980),
@om_Cube VARCHAR(1980),
@om_apIndex_Card VARCHAR(215) ,
@om_dwObjIndex_Card VARCHAR(215) ,
@om_apIndex_Cube VARCHAR(215) ,
@om_dwObjIndex_Cube VARCHAR(215) ,
@om_idparty INT ,
--@om_nNumKill INT ,
@om_idMuerderer INT ,
--@om_nSlaughter INT ,
@om_nFame INT ,
@om_nDeathExp BIGINT ,
@om_nDeathLevel INT ,
@om_dwFlyTime INT ,
@om_nMessengerState INT ,
@om_Bank VARCHAR(4290),
@om_apIndex_Bank VARCHAR(215) ,
@om_dwObjIndex_Bank VARCHAR(215) ,
@om_dwGoldBank INT
IF EXISTS (SELECT * FROM CHARACTER_TBL WHERE serverindex = @iserverindex)
SELECT @om_idPlayer = RIGHT('0000000' + CONVERT(VARCHAR(7),MAX(m_idPlayer)+1),7)
FROM CHARACTER_TBL
WHERE serverindex = @iserverindex
ELSE
SELECT @om_idPlayer = '0000001'
SELECT @om_vScale_x = m_vScale_x,
@om_dwMotion = m_dwMotion,
@om_fAngle = m_fAngle,
@om_nHitPoint = m_nHitPoint,
@om_nManaPoint = m_nManaPoint,
@om_nFatiguePoint = m_nFatiguePoint,
@om_dwRideItemIdx = m_dwRideItemIdx,
@om_dwGold = m_dwGold,
@om_nJob = m_nJob,
@om_pActMover = m_pActMover,
@om_nStr = m_nStr,
@om_nSta = m_nSta,
@om_nDex = m_nDex,
@om_nInt = m_nInt,
@om_nLevel = m_nLevel,
@om_nExp1 = m_nExp1,
@om_nExp2 = m_nExp2,
@om_aJobSkill = m_aJobSkill,
@om_aLicenseSkill = m_aLicenseSkill,
@om_aJobLv = m_aJobLv,
@om_dwExpertLv = m_dwExpertLv,
@om_idMarkingWorld = m_idMarkingWorld,
@om_vMarkingPos_x = m_vMarkingPos_x,
@om_vMarkingPos_y = m_vMarkingPos_y,
@om_vMarkingPos_z = m_vMarkingPos_z,
@om_nRemainGP = m_nRemainGP,
@om_nRemainLP = m_nRemainLP,
@om_nFlightLv = m_nFlightLv,
@om_nFxp = m_nFxp,
@om_nTxp = m_nTxp,
@om_lpQuestCntArray = m_lpQuestCntArray,
@om_chAuthority = m_chAuthority,
@om_dwMode = m_dwMode,
@oblockby = blockby,
@oTotalPlayTime = TotalPlayTime,
@oisblock = isblock,
@oEnd_Time = CONVERT(CHAR(8),DATEADD(yy,3,GETDATE()),112) + '0000',
@om_Inventory = m_Inventory,
@om_apIndex = m_apIndex,
@om_adwEquipment = m_adwEquipment,
@om_aSlotApplet = m_aSlotApplet,
@om_aSlotItem = m_aSlotItem,
@om_aSlotQueue = m_aSlotQueue,
@om_SkillBar = m_SkillBar,
@om_dwObjIndex = m_dwObjIndex,
@om_Card = m_Card,
@om_Cube = m_Cube,
@om_apIndex_Card = m_apIndex_Card,
@om_dwObjIndex_Card = m_dwObjIndex_Card,
@om_apIndex_Cube = m_apIndex_Cube,
@om_dwObjIndex_Cube = m_dwObjIndex_Cube,
@om_idparty = m_idparty,
--@om_nNumKill = m_nNumKill,
@om_idMuerderer = m_idMuerderer,
--@om_nSlaughter = m_nSlaughter ,
@om_nFame = m_nFame,
@om_nDeathExp = m_nDeathExp,
@om_nDeathLevel = m_nDeathLevel,
@om_dwFlyTime = m_dwFlyTime,
@om_nMessengerState = m_nMessengerState,
@om_Bank = m_Bank,
@om_apIndex_Bank = m_apIndex_Bank,
@om_dwObjIndex_Bank = m_dwObjIndex_Bank,
@om_dwGoldBank = m_dwGoldBank
FROM BASE_VALUE_TBL
WHERE g_nSex = @im_dwSex
INSERT CHARACTER_TBL
(
m_idPlayer,
serverindex,
account,
m_szName,
playerslot,
dwWorldID,
m_dwIndex,
m_vScale_x,
m_dwMotion,
m_vPos_x,
m_vPos_y,
m_vPos_z,
m_fAngle,
m_szCharacterKey,
m_nHitPoint,
m_nManaPoint,
m_nFatiguePoint,
m_nFuel,
m_dwSkinSet,
m_dwHairMesh,
m_dwHairColor,
m_dwHeadMesh,
m_dwSex,
m_dwRideItemIdx,
m_dwGold,
m_nJob,
m_pActMover,
m_nStr,
m_nSta,
m_nDex,
m_nInt,
m_nLevel,
m_nMaximumLevel,
m_nExp1,
m_nExp2,
m_aJobSkill,
m_aLicenseSkill,
m_aJobLv,
m_dwExpertLv,
m_idMarkingWorld,
m_vMarkingPos_x,
m_vMarkingPos_y,
m_vMarkingPos_z,
m_nRemainGP,
m_nRemainLP,
m_nFlightLv,
m_nFxp,
m_nTxp,
m_lpQuestCntArray,
m_aCompleteQuest,
m_chAuthority,
m_dwMode,
m_idparty,
m_idCompany,
--m_nNumKill,
m_idMuerderer,
--m_nSlaughter,
m_nFame,
m_nDeathExp,
m_nDeathLevel,
m_dwFlyTime,
m_nMessengerState,
blockby,
TotalPlayTime,
isblock,
End_Time,
BlockTime,
CreateTime,
m_tmAccFuel,
m_tGuildMember,
m_dwSkillPoint,
m_dwReturnWorldID,
m_vReturnPos_x,
m_vReturnPos_y,
m_vReturnPos_z
)
VALUES
(
@om_idPlayer,
@iserverindex,
@iaccount,
@im_szName,
@iplayerslot,
@idwWorldID,
@im_dwIndex,
'1',
'0',
@im_vPos_x,
@im_vPos_y,
@im_vPos_z,
'0',
@im_szCharacterKey,
'230',
'63',
'32',
-1, --m_nFuel
@im_dwSkinSet,
@im_dwHairMesh,
@im_dwHairColor,
@im_dwHeadMesh,
@im_dwSex,
'0',
'0',
'0',
'1,0',
'15',
'15',
'15',
'15',
'1',
'1', --m_nMaximumLevel
'0',
'0',
'',
'',
'0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/$',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'$',
'$', -- m_aCompleteQuest
'F',
'131072',
'0',
'0', -- m_idCompany
--@om_nNumKill,
'0',
--@om_nSlaughter,
'0',
'0',
'0',
'0' ,
'0',
@oblockby,
'0',
'F',
'200811030000',
CONVERT(CHAR(8),DATEADD(d,-1,GETDATE()),112),
GETDATE(),
0,
CONVERT(CHAR(8),DATEADD(d,-1,GETDATE()),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,DATEADD(d,-1,GETDATE()))),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,DATEADD(d,-1,GETDATE()))),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(ss,DATEADD(d,-1,GETDATE()))),2),
0, --m_dwSkillPoint
1,
0,
0,
0
)
declare @invi1 VARCHAR(8000)
declare @invi2 VARCHAR(8000)
declare @invi3 VARCHAR(8000)
declare @invi4 VARCHAR(8000)
IF @im_dwSex = '1'
BEGIN
SET @invi1 = '0,512,0,0,,1,0,4500000,0,0,0,-1118993182,0,0,0,0,0/1,504,0,0,,1,0,9000000,0,0,0,946307614,0,0,0,0,0/42,508,0,0,,1,0,5850000,0,0,0,750110947,0,0,0,0,0/44,21,0,0,,1,0,7200000,0,0,0,-2111939376,0,0,0,0,0/$'
SET @invi2 = '45/43/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34/35/36/37/38/39/40/41/-1/-1/1/-1/42/0/-1/-1/-1/-1/44/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/$'
SET @invi3 = '0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/$'
SET @invi4 = '47/44/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34/35/36/37/38/39/40/41/46/1/52/0/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/$'
END
ELSE
BEGIN
SET @invi1 = '0,506,0,0,,1,0,5850000,0,0,0,-2005860201,0,0,0,0,0/1,502,0,0,,1,0,9000000,0,0,0,688242964,0,0,0,0,0/2,510,0,0,,1,0,4500000,0,0,0,583094601,0,0,0,0,0/44,21,0,0,,1,0,7200000,0,0,0,-2111939376,0,0,0,0,0/$'
SET @invi2 = '45/42/43/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34/35/36/37/38/39/40/41/-1/-1/1/-1/0/2/-1/-1/-1/-1/44/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/$'
SET @invi3 = '0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/$'
SET @invi4 = '46/44/47/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34/35/36/37/38/39/40/41/1/2/52/0/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/-1/$'
END
INSERT INVENTORY_TBL
(
m_idPlayer,
serverindex,
m_Inventory,
m_apIndex,
m_adwEquipment,
m_dwObjIndex
)
VALUES
(
@om_idPlayer,
@iserverindex,
@invi1,
@invi2,
@invi3,
@invi4
)
IF @@SERVERNAME = 'WEB' OR @@SERVERNAME = 'SERVER4'
SET @om_aSlotApplet = '0,2,400,0,0,0,0/1,2,398,0,1,0,0/2,2,2010,0,2,0,0/3,2,1005,0,3,0,0/4,3,25,0,4,0,0/$'
INSERT TASKBAR_TBL
(
m_idPlayer,
serverindex,
m_aSlotApplet,
m_aSlotQueue,
m_SkillBar
)
VALUES
(
@om_idPlayer,
@iserverindex,
'$',
'$',
'100'
)
INSERT TASKBAR_ITEM_TBL
(
m_idPlayer,
serverindex,
m_aSlotItem
)
VALUES
(
@om_idPlayer,
@iserverindex,
'$'
)
INSERT BANK_TBL
(
m_idPlayer,
serverindex,
m_Bank,
m_BankPw,
m_apIndex_Bank,
m_dwObjIndex_Bank ,
m_dwGoldBank
)
VALUES
(
@om_idPlayer,
@iserverindex,
'$',
'0000', -- m_BankPw
'$',
'$',
'0'
)
INSERT SKILLINFLUENCE_TBL
(
m_idPlayer,
serverindex,
SkillInfluence
)
VALUES
(
@om_idPlayer,
@iserverindex,
'$'
)
INSERT INVENTORY_EXT_TBL
(
m_idPlayer,
serverindex,
m_extInventory,
m_InventoryPiercing,
szInventoryPet
)
VALUES
(
@om_idPlayer,
@iserverindex,
'$','$','$'
)
INSERT BANK_EXT_TBL
(
m_idPlayer,
serverindex,
m_extBank,
m_BankPiercing,
szBankPet
)
VALUES
(
@om_idPlayer,
@iserverindex,
'$','$','$'
)
INSERT INTO tblSkillPoint(serverindex, PlayerID, SkillID, SkillLv, SkillPosition)
VALUES (@iserverindex, @om_idPlayer, 1, 0, 0)
INSERT INTO tblSkillPoint(serverindex, PlayerID, SkillID, SkillLv, SkillPosition)
VALUES (@iserverindex, @om_idPlayer, 2, 0, 1)
INSERT INTO tblSkillPoint(serverindex, PlayerID, SkillID, SkillLv, SkillPosition)
VALUES (@iserverindex, @om_idPlayer, 3, 0, 2)
------------ Ver11 Pocket
INSERT tblPocket ( serverindex, idPlayer, nPocket, szItem, szIndex, szObjIndex, bExpired, tExpirationDate )
VALUES ( @iserverindex, @om_idPlayer, 0, '$', '$', '$', 0, 0 )
INSERT tblPocketExt ( serverindex, idPlayer, nPocket, szExt, szPiercing, szPet )
VALUES ( @iserverindex, @om_idPlayer, 0, '$', '$', '$' )
INSERT tblPocket ( serverindex, idPlayer, nPocket, szItem, szIndex, szObjIndex, bExpired, tExpirationDate )
VALUES ( @iserverindex, @om_idPlayer, 1, '$', '$', '$', 1, 0 )
INSERT tblPocketExt ( serverindex, idPlayer, nPocket, szExt, szPiercing, szPet )
VALUES ( @iserverindex, @om_idPlayer, 1, '$', '$', '$' )
INSERT tblPocket ( serverindex, idPlayer, nPocket, szItem, szIndex, szObjIndex, bExpired, tExpirationDate )
VALUES ( @iserverindex, @om_idPlayer, 2, '$', '$', '$', 1, 0 )
INSERT tblPocketExt ( serverindex, idPlayer, nPocket, szExt, szPiercing, szPet )
VALUES ( @iserverindex, @om_idPlayer, 2, '$', '$', '$' )
/*
2007-06-21 ~ 2007-07-18 New character item urgent event
*/
declare @ntime datetime
select @ntime = getdate()
if(@ntime >= '2007-06-21 00:00:00.000' and @ntime <= '2007-07-18 23:59:59.999')
begin
declare @etime bigint
select @etime = cast(replace(replace(replace(convert(char(16), dateadd(d, 7, getdate()), 21), '-', ''), ' ', ''), ':', '') as bigint)
if(@im_dwSex = 0)
begin
insert into ITEM_SEND_TBL(m_idPlayer, serverindex, Item_Name, Item_count, idSender)
select @om_idPlayer, @iserverindex, '26314', 1, '0000000'
insert into ITEM_SEND_TBL(m_idPlayer, serverindex, Item_Name, Item_count, m_dwKeepTime, idSender)
select @om_idPlayer, @iserverindex, '15200', 1, @etime, '0000000'
end
if(@im_dwSex = 1)
begin
insert into ITEM_SEND_TBL(m_idPlayer, serverindex, Item_Name, Item_count, idSender)
select @om_idPlayer, @iserverindex, '26314', 1, '0000000'
insert into ITEM_SEND_TBL(m_idPlayer, serverindex, Item_Name, Item_count, m_dwKeepTime, idSender)
select @om_idPlayer, @iserverindex, '15202', 1, @etime, '0000000'
end
end
SELECT fError = '1', fText = 'OK',m_idPlayer=@om_idPlayer
RETURN
END
END
set nocount off
RETURN
GO
--
-- Definition for stored procedure COMPANY_STR :
--
GO
/****** Object: Stored Procedure dbo.COMPANY_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].COMPANY_STR
@iGu CHAR(2) ,
@im_idCompany CHAR(6) = '000001',
@iserverindex CHAR(2) = '01',
@im_leaderid CHAR(6) = '000001',
@im_szCompany VARCHAR(16) = ''
AS
set nocount on
IF @iGu = 'A1'
BEGIN
IF EXISTS(SELECT * FROM COMPANY_TBL WHERE m_szCompany = @im_szCompany AND serverindex = @iserverindex AND isuse = 'T')
BEGIN
SELECT fError = '1', fText = '중복된 컴파니 이름'
RETURN
END
ELSE
IF EXISTS(SELECT * FROM COMPANY_TBL WHERE m_leaderid = @im_leaderid AND serverindex = @iserverindex AND isuse = 'T')
BEGIN
SELECT fError = '2', fText = '중복된 리더'
RETURN
END
ELSE
BEGIN
UPDATE CHARACTER_TBL SET m_idCompany = @im_idCompany WHERE m_idPlayer = @im_leaderid AND serverindex = @iserverindex
INSERT COMPANY_TBL
(m_idCompany,serverindex,m_szCompany,m_leaderid,isuse)
VALUES
(@im_idCompany,@iserverindex,@im_szCompany,@im_leaderid,'T')
SELECT fError = '0', fText = 'OK'
RETURN
END
END
/*
컴파니 이름 저장하기
ex )
COMPANY_STR 'A1',@im_idCompany,@iserverindex,@im_leaderid,@im_szCompany
COMPANY_STR 'A1','000001','01','000001','불타는닭갈비'
*/
ELSE
IF @iGu = 'A2'
BEGIN
UPDATE CHARACTER_TBL
SET m_idCompany = @im_idCompany
WHERE m_idPlayer = @im_leaderid
AND serverindex = @iserverindex
RETURN
END
/*
컴파니 가입하기
ex )
COMPANY_STR 'A2',@im_idCompany,@iserverindex,@im_leaderid(m_idPlayer),@im_szCompany
COMPANY_STR 'A2','000001','01','000001'
*/
ELSE
IF @iGu = 'S1'
BEGIN
DECLARE @maxid CHAR(6)
SELECT @maxid = max(m_idCompany) FROM COMPANY_TBL
SELECT m_idCompany,m_szCompany,m_leaderid,maxid = @maxid
FROM COMPANY_TBL
WHERE serverindex = @iserverindex
AND isuse = 'T'
ORDER BY m_idCompany
RETURN
END
/*
컴파니 모두 가져오기
ex )
COMPANY_STR 'S1',@im_idCompany,@iserverindex
COMPANY_STR 'S1','','01'
*/
ELSE
IF @iGu = 'D1'
BEGIN
UPDATE COMPANY_TBL
SET isuse = 'F'
WHERE m_idCompany = @im_idCompany
AND serverindex = @iserverindex
UPDATE CHARACTER_TBL
SET m_idCompany = '000000'
WHERE m_idCompany = @im_idCompany
AND serverindex = @iserverindex
SELECT fError = '0', fText = 'OK'
RETURN
END
/*
컴파니 해체
ex )
COMPANY_STR 'D1',@im_idCompany,@iserverindex
COMPANY_STR 'D1','000001','01'
*/
ELSE
IF @iGu = 'D2'
BEGIN
UPDATE CHARACTER_TBL
SET m_idCompany = '000000'
WHERE m_idPlayer = @im_idCompany
AND serverindex = @iserverindex
SELECT fError = '0', fText = 'OK'
RETURN
END
/*
컴파니 탈퇴
ex )
COMPANY_STR 'D2',@im_idCompany(m_idPlayer),@iserverindex
COMPANY_STR 'D2','000001','01'
*/
set nocount off
GO
--
-- Definition for stored procedure DEL_FALSE_CHARACTER_STR :
--
GO
/****** Object: Stored Procedure dbo.DEL_FALSE_CHARACTER_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].DEL_FALSE_CHARACTER_STR
@serverindex char(2) = '01'
AS
UPDATE CHARACTER_TBL
SET isblock = 'D'
--select m_idPlayer,TotalPlayTime from CHARACTER_TBL
where m_idPlayer in (
select x.m_idPlayer
from
(
select a.m_idPlayer
from CHARACTER_TBL a,
(
select account, playerslot
from CHARACTER_TBL
where isblock='F'
and serverindex =@serverindex
group by account,playerslot
having count(account)>1
) b
where a.account=b.account
and a.playerslot =b.playerslot
and serverindex =@serverindex
and a.isblock = 'F'
) x
where x.m_idPlayer NOT IN( select m_idPlayer = min(m_idPlayer)
from CHARACTER_TBL
where isblock='F'
and serverindex =@serverindex
group by account,playerslot
having count(account)>1)
)
and isblock = 'F'
and serverindex =@serverindex
RETURN
GO
--
-- Definition for stored procedure GET_INSERT_SCRIPT_STR :
--
GO
/****** Object: Stored Procedure dbo.GET_INSERT_SCRIPT_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].GET_INSERT_SCRIPT_STR
@tbl_name VARCHAR(128)
AS
/*
email :
beatchoi@yahoo.co.kr
Program : Sukjoon Choi
Date : 2005.4.29
*/
-- DECLARE @tbl_name VARCHAR(128)
--
-- SET @tbl_name = 'sysfiles'
DECLARE @column VARCHAR(8000)
DECLARE @value VARCHAR(8000)
DECLARE @columnsStr VARCHAR(8000)
DECLARE @valuesStr VARCHAR(8000)
SET @column = ''
SET @value = ''
SET @columnsStr = ''
SET @valuesStr = ''
DECLARE TBL_Column_Cursor CURSOR
FOR
SELECT
[column] = QUOTENAME(a.name) + ',',
[value] = CASE WHEN b.xprec = 23
THEN ''''''' + CONVERT(VARCHAR,(ISNULL('+ a.name + ',''''))) + '''''
WHEN b.xprec = 0
THEN ''''''' + ISNULL('+ a.name + ','''') + '''''
ELSE ''' + CONVERT(VARCHAR,(ISNULL('+ a.name + ',''''))) + '
END +''','
FROM syscolumns a,systypes b
WHERE a.id = object_id( @tbl_name)
AND a.xtype = b.xtype
ORDER BY a.colid
OPEN TBL_Column_Cursor
FETCH NEXT FROM TBL_Column_Cursor
INTO @column,@value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @columnsStr = @columnsStr + @column
SET @valuesStr = @valuesStr + @value
FETCH NEXT FROM TBL_Column_Cursor
INTO @column,@value
END
SET @columnsStr = LEFT(@columnsStr,LEN(@columnsStr) -1)
SET @valuesStr = LEFT(@valuesStr,LEN(@valuesStr) -1)
CLOSE TBL_Column_Cursor
DEALLOCATE TBL_Column_Cursor
EXEC('SELECT ''INSERT INTO ' + @tbl_name + ' (' + @columnsStr + '''' +' + '') VALUES (' + @valuesStr + ')''
FROM ' + @tbl_name)
RETURN
GO
--
-- Definition for stored procedure GUILD_BANK_STR :
--
GO
/****** Object: Stored Procedure dbo.GUILD_BANK_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].GUILD_BANK_STR
@iGu CHAR(2) ,
@im_idGuild CHAR(6) ,
@iserverindex CHAR(2) ,
@im_nGuildGold INT = 0,
@im_apIndex VARCHAR(215) = '',
@im_dwObjIndex VARCHAR(215) = '',
@im_GuildBank TEXT = '',
@im_idPlayer CHAR(7) = '',
@im_extGuildBank varchar(2000) = '',
@im_GuildBankPiercing varchar(2000) = ''
, @iszGuildBankPet varchar(2688) = '$'
AS
set nocount on
IF @iGu = 'S1'
BEGIN
SELECT A.m_idGuild, A.serverindex, B.m_nGuildGold, A.m_apIndex, A.m_dwObjIndex, A.m_GuildBank,C.m_extGuildBank,C.m_GuildBankPiercing, C.szGuildBankPet
FROM GUILD_BANK_TBL A, GUILD_TBL B, GUILD_BANK_EXT_TBL C
WHERE A.m_idGuild = B.m_idGuild
AND B.m_idGuild = C.m_idGuild
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
RETURN
END
/*
GUILD BANK 전체 불러오기
ex )
GUILD_BANK_STR 'S1',@im_idGuild,@iserverindex
GUILD_BANK_STR 'S1','000000','01'
*/
IF @iGu = 'U1'
BEGIN
DECLARE @om_nGuildGold INT
SELECT @om_nGuildGold = m_nGuildGold - @im_nGuildGold
FROM GUILD_TBL
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_BANK_TBL
SET m_apIndex = @im_apIndex,
m_dwObjIndex = @im_dwObjIndex,
m_GuildBank = @im_GuildBank
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_BANK_EXT_TBL
SET m_extGuildBank = @im_extGuildBank,
m_GuildBankPiercing = @im_GuildBankPiercing
, szGuildBankPet = @iszGuildBankPet
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_TBL
SET m_nGuildGold = @im_nGuildGold
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
IF (@om_nGuildGold > 0 and @im_idPlayer <> '0000000')
BEGIN
UPDATE GUILD_MEMBER_TBL
SET m_nGiveGold = m_nGiveGold - @om_nGuildGold
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
END
RETURN
END
/*
GUILD BANK 저장하기
ex )
GUILD_BANK_STR 'U1',@im_idGuild,@iserverindex,@im_nGuildGold,@im_apIndex,@im_dwObjIndex,@im_GuildBank,@im_idPlayer
GUILD_BANK_STR 'U1','000001','01',0,'$','$','$', 1000
GUILD_BANK_STR 'U1','000001','01',0,'$','$','$', 0
*/
set nocount off
GO
--
-- Definition for stored procedure GUILD_QUEST_STR :
--
GO
/****** Object: Stored Procedure dbo.GUILD_QUEST_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].GUILD_QUEST_STR
@iGu CHAR(2) = 'S1',
@im_idGuild CHAR(6) = '000001',
@iserverindex CHAR(2) = '01',
@in_Id INT = 0,
@in_State INT = 0
AS
set nocount on
DECLARE @odate CHAR(14)
SELECT @odate = CONVERT(CHAR(8),GETDATE(),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(ss,GETDATE())),2)
IF @iGu = 'S1'
BEGIN
SELECT m_idGuild,serverindex,n_Id,nState,s_date,e_date
FROM GUILD_QUEST_TBL
WHERE serverindex = @iserverindex
ORDER BY m_idGuild,serverindex,n_Id
RETURN
END
/*
길드 퀘스트 다 불러오기
ex )
GUILD_QUEST_STR 'S1', @im_idGuild,@iserverindex
GUILD_QUEST_STR 'S1', '','01'
*/
ELSE
IF @iGu = 'A1'
BEGIN
IF NOT EXISTS(SELECT * FROM GUILD_QUEST_TBL WHERE m_idGuild= @im_idGuild AND serverindex = @iserverindex AND n_Id = @in_Id)
INSERT GUILD_QUEST_TBL
(m_idGuild,serverindex,n_Id,nState,s_date,e_date)
VALUES
(@im_idGuild,@iserverindex,@in_Id,0,@odate,'')
RETURN
END
/*
길드 퀘스트 시작
ex )
GUILD_QUEST_STR 'A1', @im_idGuild,@iserverindex,@in_Id
GUILD_QUEST_STR 'A1', '000001','01',0
*/
ELSE
IF @iGu = 'U1'
BEGIN
UPDATE GUILD_QUEST_TBL
SET nState = @in_State,
e_date = @odate
WHERE m_idGuild= @im_idGuild
AND serverindex = @iserverindex
AND n_Id = @in_Id
RETURN
END
/*
길드 퀘스트 종료
ex )
GUILD_QUEST_STR 'U1', @im_idGuild,@iserverindex,@in_Id,@in_State
GUILD_QUEST_STR 'U1', '000001','01',0,1
*/
ELSE
IF @iGu = 'D1'
BEGIN
DELETE GUILD_QUEST_TBL
WHERE m_idGuild= @im_idGuild
AND serverindex = @iserverindex
AND n_Id = @in_Id
RETURN
END
/*
길드 퀘스트 삭제
ex )
GUILD_QUEST_STR 'D1', @im_idGuild,@iserverindex,@in_Id,@in_State
GUILD_QUEST_STR 'D1', '000001','01',0,1
*/
set nocount off
GO
--
-- Definition for stored procedure GUILD_STR :
--
GO
/****** Object: Stored Procedure dbo.GUILD_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].GUILD_STR
@iGu CHAR(2) = 'S1',
@im_idPlayer CHAR(7) = '0000001',
@iserverindex CHAR(2) = '01',
@im_idGuild CHAR(6) = '000001',
@im_szGuild varCHAR(48) ='',
@iLv_1 INT =1,
@iLv_2 INT =1,
@iLv_3 INT =1,
@iLv_4 INT =1,
@im_nLevel INT =1,
@im_nGuildGold INT =0,
@im_nGuildPxp INT =0,
@im_nWin INT =0,
@im_nLose INT =0,
@im_nSurrender INT =0,
@im_dwLogo INT =0,
@im_szNotice VARCHAR(127) ='',
@im_nClass INT =0
AS
set nocount on
IF @iGu = 'S1'
BEGIN
SELECT m_idGuild,serverindex,Lv_1,Lv_2,Lv_3,Lv_4,Pay_0,Pay_1,Pay_2,Pay_3,Pay_4,m_szGuild,m_nLevel,
m_nGuildGold,m_nGuildPxp,m_nWin,m_nLose,m_nSurrender,m_nWinPoint,
m_dwLogo,m_szNotice
FROM GUILD_TBL
WHERE serverindex = @iserverindex
AND isuse='T'
ORDER BY m_idGuild
RETURN
END
/*
SELECT * FROM GUILD_TBL
SELECT * FROM GUILD_MEMBER_TBL
SELECT * FROM GUILD_BANK_TBL WHERE serverindex ='07'
DELETE GUILD_TBL WHERE serverindex ='07'
DELETE GUILD_MEMBER_TBL WHERE serverindex ='07'
DELETE GUILD_BANK_TBL WHERE serverindex ='07'
GUILD 전체 불러오기 - 강현민
ex )
GUILD_STR 'S1',@im_idPlayer,@iserverindex
GUILD_STR 'S1','','01'
*/
ELSE
IF @iGu = 'S2'
BEGIN
SELECT A.m_idPlayer,A.serverindex,A.m_idGuild,A.m_szAlias,A.m_nWin,A.m_nLose,A.m_nSurrender,
A.m_nMemberLv,A.m_nClass,A.m_nGiveGold,A.m_nGivePxp,B.m_nJob,B.m_nLevel,B.m_dwSex,
m_idWar=ISNULL(A.m_idWar,0),m_idVote=ISNULL(A.m_idVote,0)
FROM GUILD_MEMBER_TBL A, CHARACTER_TBL B
WHERE A.m_idPlayer = B.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = @iserverindex
-- AND B.isblock='F'
AND A.isuse = 'T'
ORDER BY A.m_idPlayer
RETURN
END
/*
GUILD 멤버 전체 불러오기- 강현민
ex )
GUILD_STR 'S2',@im_idPlayer,@iserverindex
GUILD_STR 'S2','','01'
*/
ELSE
IF @iGu = 'A1'
BEGIN
-- IF NOT EXISTS (SELECT * FROM GUILD_TBL WHERE m_szGuild = @im_szGuild AND serverindex = @iserverindex)
-- BEGIN
IF (NOT EXISTS (SELECT * FROM GUILD_TBL WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex) and NOT EXISTS (SELECT * FROM GUILD_BANK_TBL WHERE m_idGuild = @im_szGuild AND serverindex = @iserverindex))
BEGIN
INSERT GUILD_TBL
(
m_idGuild,serverindex,Lv_1,Lv_2,Lv_3,Lv_4,Pay_0,Pay_1,Pay_2,Pay_3,Pay_4,
m_szGuild,m_nLevel,m_nGuildGold,m_nGuildPxp,
m_nWin,m_nLose,m_nSurrender,m_nWinPoint,m_dwLogo,
m_szNotice,isuse,CreateTime
)
VALUES
(
@im_idGuild,@iserverindex,@iLv_1,@iLv_2,@iLv_3,@iLv_4,0,0,0,0,0,
@im_szGuild,1,0,0,
0,0,0,0,@im_dwLogo,
@im_szNotice,'T',GETDATE()
)
INSERT GUILD_BANK_TBL
(
m_idGuild,serverindex,m_apIndex,
m_dwObjIndex,m_GuildBank
)
VALUES
(
@im_idGuild,@iserverindex,'$',
'$','$'
)
INSERT GUILD_BANK_EXT_TBL
(
m_idGuild,serverindex,m_extGuildBank,m_GuildBankPiercing
)
VALUES
(
@im_idGuild,@iserverindex,'$','$'
)
-- IF @@SERVERNAME='CHAR-DB1'
-- BEGIN
-- INSERT [LOG].LOG_DBF.dbo.GUILD_TBL --로그 디비에 길드 생성 정진형 팀장 요청사항
-- (
-- m_idGuild,serverindex,m_szGuild,isuse
-- )
-- VALUES
-- (
-- @im_idGuild,@iserverindex,@im_szGuild,'T'
-- )
-- END
SELECT nError = '1', fText = '길드 생성 OK'
END
ELSE
BEGIN
SELECT nError = '2', fText = '길드 아이디 중복'
END
-- END
-- ELSE
-- BEGIN
-- SELECT nError = '3', fText = '길드 이름 중복'
-- END
-- RETURN
END
/*
GUILD 생성- 강현민
ex )
GUILD_STR 'A1',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel,@im_nGuildGold,
@im_nGuildPxp,@im_nWin,@im_nLose,@im_nSurrender,@im_dwLogo,@im_szNotice
GUILD_STR 'A1','000000','01','000004','멋쟁이길드',1,1,1,1,1,0,
0,0,0,0,0,'안녕하십니까? 길드원이 되신걸 환영합니다.'
*/
ELSE
IF @iGu = 'A2'
BEGIN
IF NOT EXISTS( SELECT * FROM GUILD_MEMBER_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND m_idGuild = @im_idGuild)
INSERT GUILD_MEMBER_TBL
(
m_idPlayer,serverindex,m_idGuild,m_szAlias,
m_nWin,m_nLose,m_nSurrender,m_nMemberLv,m_nClass,
m_nGiveGold,m_nGivePxp,m_idWar,m_idVote,isuse,CreateTime
)
VALUES
(
@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild, --(@im_szGuild = m_szAlias)
0,0,0,@im_nLevel, 0, --(@iLv_1 = m_nPay , @im_nLevel = m_nMemberLv)
@im_nGuildGold,@im_nGuildPxp,0,'','T',GETDATE() --(@im_nGuildGold = m_nGiveGold,@im_nGuildPxp= m_nGivePxp)
)
RETURN
END
/*
GUILD 가입
ex )
GUILD_STR 'A2',@im_idPlayer,@iserverindex,@im_idGuild(@im_szGuild = m_szAlias),@im_szGuild,@iLv_1(@iLv_1 = m_nPay),
@iLv_2,@iLv_3,@iLv_4,@im_nLevel(@im_nLevel = m_nMemberLv),@im_nGuildGold(@im_nGuildGold = m_nGiveGold),
@im_nGuildPxp(@im_nGuildPxp= m_nGivePxp)
GUILD_STR 'A2','000023','01','000001','',1,
0,0,0,0,1,0,
0
*/
ELSE
IF @iGu = 'U1'
BEGIN
UPDATE GUILD_TBL
SET Lv_1 = @iLv_1,
Lv_2 = @iLv_2,
Lv_3 = @iLv_3,
Lv_4 = @iLv_4
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND isuse = 'T'
RETURN
END
/*
GUILD 권한 변경 - 송현석
ex )
GUILD_STR 'U1',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4
GUILD_STR 'U1','000000','01','000001','',0,0,0,0,0
*/
ELSE
IF @iGu = 'U2'
BEGIN
UPDATE GUILD_MEMBER_TBL
SET m_nMemberLv = @im_nLevel,
m_nClass = 0 -- 송현석 요청사항
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND isuse = 'T'
RETURN
END
/*
GUILD 멤버 권한 변경- 강현민
ex )
GUILD_STR 'U2',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel
GUILD_STR 'U2','000000','01','000001','',0,0,0,0,0,12
*/
ELSE
IF @iGu = 'U3'
BEGIN
UPDATE GUILD_TBL
SET m_dwLogo = @im_dwLogo
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 로고 변경하기- 이혁재
ex )
GUILD_STR 'U3',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel,@im_nGuildGold,
@im_nGuildPxp,@im_nWin,@im_nLose,@im_nSurrender,@im_dwLogo,@im_szNotice
GUILD_STR 'U3','000000','01','000001','',0,0,0,0,0,0,
0,0,0,0,0,123,@im_szNotice
*/
ELSE
IF @iGu = 'U4'
BEGIN
UPDATE GUILD_TBL
SET m_nLevel = @im_nLevel,
m_nGuildGold = @im_nGuildGold,
m_nGuildPxp = @im_nGuildPxp
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_MEMBER_TBL
SET m_nGiveGold = m_nGiveGold + @iLv_1,
m_nGivePxp = m_nGivePxp + @iLv_2
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND m_idPlayer = @im_idPlayer
RETURN
END
/*
GUILD 공헌 - 이혁재
ex )
GUILD_STR 'U4',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel,@im_nGuildGold,
@im_nGuildPxp
GUILD_STR 'U4','000000','01','000001','',0,0,0,0,16,1000,
14
*/
ELSE
IF @iGu = 'U5'
BEGIN
UPDATE GUILD_TBL
SET m_szNotice = @im_szNotice
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 공지변경 - 이혁재
ex )
GUILD_STR 'U5',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel,@im_nGuildGold,
@im_nGuildPxp,@im_nWin,@im_nLose,@im_nSurrender,@im_dwLogo,@im_szNotice
GUILD_STR 'U5','000000','01','000001','',0,0,0,0,0,0,
0,0,0,0,0,0,'안녕하십니까? 길드원이 되신걸 환영합니다.'
*/
ELSE
IF @iGu = 'U6'
BEGIN
UPDATE GUILD_TBL
SET Pay_0 = @im_dwLogo,
Pay_1 = @iLv_1,
Pay_2 = @iLv_2,
Pay_3 = @iLv_3,
Pay_4 = @iLv_4
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 월급지정 - 송현석
ex )
GUILD_STR 'U6',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel,@im_nGuildGold,
@im_nGuildPxp,@im_nWin,@im_nLose,@im_nSurrender,@im_dwLogo
GUILD_STR 'U6','000000','01','000001','',****80,60,40,0,0,
0,0,0,0,0,20
*/
ELSE
IF @iGu = 'U7'
BEGIN
INSERT ITEM_SEND_TBL
(m_idPlayer,serverindex,Item_Name,Item_count,m_nAbilityOption,End_Time)
SELECT A.m_idPlayer,
A.serverindex,
'penya',
CASE A.m_nMemberLv WHEN 0 THEN B.Pay_0
WHEN 1 THEN B.Pay_1
WHEN 2 THEN B.Pay_2
WHEN 3 THEN B.Pay_3
WHEN 4 THEN B.Pay_4
ELSE 0 END ,
0,
NULL
FROM GUILD_MEMBER_TBL A,GUILD_TBL B
WHERE A.m_idGuild =B.m_idGuild
AND B.m_idGuild = @im_idGuild
AND A.serverindex = B.serverindex
AND B.serverindex = @iserverindex
AND A.isuse = 'T'
AND (
(A.m_nMemberLv = 0 AND B.Pay_0 > 0) OR
(A.m_nMemberLv = 1 AND B.Pay_1 > 0) OR
(A.m_nMemberLv = 2 AND B.Pay_2 > 0) OR
(A.m_nMemberLv = 3 AND B.Pay_3 > 0) OR
(A.m_nMemberLv = 4 AND B.Pay_4 > 0)
)
UPDATE GUILD_TBL
SET m_nGuildGold = @iLv_1
WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex
SELECT nError = '1',fText ='OK'
RETURN
END
/*
GUILD 월급지급 - 송현석
ex )
GUILD_STR 'U7',@im_idPlayer,@iserverindex,@im_idGuild,'',@iLv_1
GUILD_STR 'U7','000000','02','000029','',10000
*/
ELSE
IF @iGu = 'U8'
BEGIN
UPDATE GUILD_TBL
SET m_szGuild = @im_szGuild
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
-- IF @@SERVERNAME='CHAR-DB1'
-- BEGIN
-- UPDATE [LOG].LOG_DBF.dbo.GUILD_TBL --로그 디비에 이름 변경 정진형 팀장 요청사항
-- SET m_szGuild = @im_szGuild
-- WHERE m_idGuild = @im_idGuild
-- AND serverindex = @iserverindex
-- END
RETURN
END
/*
GUILD 이름 변경 - 송현석
ex )
GUILD_STR 'U8',@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild
GUILD_STR 'U8', '000000', '01', '000001', 'asasas'
*/
ELSE
IF @iGu = 'U9'
BEGIN
UPDATE GUILD_MEMBER_TBL
SET m_nClass = @im_nClass
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
RETURN
END
/*
GUILD원 등급 변경 - 송현석
ex )
GUILD_STR 'U9',,@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild,@iLv_1,@iLv_2,@iLv_3,@iLv_4,@im_nLevel,@im_nGuildGold,
@im_nGuildPxp,@im_nWin,@im_nLose,@im_nSurrender,@im_dwLogo,@im_nClass
GUILD_STR 'U9','000000','01','000001','',****80,60,40,0,0,
0,0,0,0,0,20,2
*/
ELSE
IF @iGu = 'UA'
BEGIN
UPDATE GUILD_MEMBER_TBL
SET m_szAlias = @im_szGuild --m_szAlias
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
RETURN
END
/*
GUILD원 별칭 변경 - 송현석
ex )
GUILD_STR 'UA',,@im_idPlayer,@iserverindex,@im_idGuild,@im_szGuild(m_szAlias)
GUILD_STR 'UA','000000','01','000001','시다바리'
*/
ELSE
IF @iGu = 'D1'
BEGIN
UPDATE CHARACTER_TBL
SET m_tGuildMember = CONVERT(CHAR(8),DATEADD(d,2,GETDATE()),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,DATEADD(d,2,GETDATE()))),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,DATEADD(d,2,GETDATE()))),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(ss,DATEADD(d,2,GETDATE()))),2)
FROM CHARACTER_TBL A,GUILD_MEMBER_TBL B
WHERE A.m_idPlayer = B.m_idPlayer
AND B.m_idGuild = @im_idGuild
AND A.serverindex = B.serverindex
AND B.serverindex = @iserverindex
DELETE GUILD_TBL
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
DELETE GUILD_MEMBER_TBL
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
DELETE GUILD_BANK_TBL
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
DELETE GUILD_BANK_EXT_TBL
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
-- IF @@SERVERNAME='CHAR-DB1'
-- BEGIN
-- UPDATE [LOG].LOG_DBF.dbo.GUILD_TBL --로그 디비에 이름 변경 정진형 팀장 요청사항
-- SET isuse = 'D'
-- WHERE m_idGuild = @im_idGuild
-- AND serverindex = @iserverindex
--
-- END
-- 임시로 딜리트 (강현민 요청사항)
-- UPDATE GUILD_TBL
-- SET isuse = 'F'
-- WHERE m_idGuild = @im_idGuild
-- AND serverindex = @iserverindex
--
-- UPDATE GUILD_MEMBER_TBL
-- SET isuse = 'F'
-- WHERE m_idGuild = @im_idGuild
-- AND serverindex = @iserverindex
RETURN
END
/*
GUILD 해체- 강현민
ex )
GUILD_STR 'D1',@im_idPlayer,@iserverindex,@im_idGuild
GUILD_STR 'D1','000000','01','000001'
*/
ELSE
IF @iGu = 'D2'
BEGIN
UPDATE CHARACTER_TBL
SET m_tGuildMember = CONVERT(CHAR(8),DATEADD(d,2,GETDATE()),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,DATEADD(d,2,GETDATE()))),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,DATEADD(d,2,GETDATE()))),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(ss,DATEADD(d,2,GETDATE()))),2)
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
DELETE GUILD_MEMBER_TBL
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
-- 임시로 딜리트 (강현민 요청사항)
-- UPDATE GUILD_MEMBER_TBL
-- SET isuse = 'F'
-- WHERE m_idGuild = @im_idGuild
-- AND serverindex = @iserverindex
RETURN
END
/*
GUILD 탈퇴/추방 - 강현민
ex )
GUILD_STR 'D2',@im_idPlayer,@iserverindex,@im_idGuild
GUILD_STR 'D2','000000','01','000001'
*/
set nocount off
GO
--
-- Definition for stored procedure GUILD_VOTE_STR :
--
GO
/****** Object: Stored Procedure dbo.GUILD_VOTE_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].GUILD_VOTE_STR
@iGu CHAR(2) ='S1',
@iserverindex CHAR(2) = '01',
@im_idGuild CHAR(6) = '000000',
@im_idVote INT = 0,
@im_szTitle VARCHAR(128) ='',
@im_szQuestion VARCHAR(255) = '',
@im_szString1 VARCHAR(32) = '',
@im_szString2 VARCHAR(32) = '',
@im_szString3 VARCHAR(32) = '',
@im_szString4 VARCHAR(32) = '',
@im_cbCount INT = 0
AS
set nocount on
IF @iGu = 'S1'
BEGIN
SELECT m_idGuild,serverindex,m_idVote,m_cbStatus,m_szTitle,m_szQuestion,
m_szString1,m_szString2,m_szString3,m_szString4,
m_cbCount1,m_cbCount2,m_cbCount3,m_cbCount4
FROM GUILD_VOTE_TBL
WHERE serverindex = @iserverindex
AND m_cbStatus IN ('1','2')
ORDER BY m_idGuild,m_idVote DESC
RETURN
END
/*
GUILD 투표 전부 가져오기
ex )
GUILD_VOTE_STR 'S1', @iserverindex
GUILD_VOTE_STR 'S1', '01'
*/
ELSE
IF @iGu = 'A1'
BEGIN
DECLARE @om_idVote INT,@oCount INT,@om_idVoteMin INT
SELECT @om_idVote = ISNULL(MAX(m_idVote),0) + 1
FROM GUILD_VOTE_TBL
WHERE serverindex = @iserverindex
SELECT @oCount = COUNT(m_idGuild),@om_idVoteMin = MIN(m_idVote)
FROM GUILD_VOTE_TBL
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND m_cbStatus IN ('1','2')
IF @oCount >= 20
UPDATE GUILD_VOTE_TBL
SET m_cbStatus = '3'
WHERE m_idVote = @om_idVoteMin
AND m_idGuild = @im_idGuild
INSERT GUILD_VOTE_TBL
(
m_idGuild,serverindex,m_idVote,m_cbStatus,m_szTitle,m_szQuestion,
m_szString1,m_szString2,m_szString3,m_szString4,
m_cbCount1,m_cbCount2,m_cbCount3,m_cbCount4,CreateTime
)
VALUES
(
@im_idGuild,@iserverindex,@om_idVote,'1',@im_szTitle,@im_szQuestion,
@im_szString1,@im_szString2,@im_szString3,@im_szString4,
0,0,0,0,GETDATE()
)
SELECT m_idVote = @om_idVote
RETURN
END
/*
GUILD 투표 저장하기
ex )
GUILD_VOTE_STR 'A1', @iserverindex,@im_idGuild,@im_idVote,@im_szTitle,@im_szQuestion,
@im_szString1,@im_szString2,@im_szString3,@im_szString4
GUILD_VOTE_STR 'A1', '01','000001',0,'노무현 탄핵?','탄핵이 옳을까요? 아닐까요?',
'옳다','아니다','모르겠다',''
*/
ELSE
IF @iGu = 'U1'
BEGIN
IF EXISTS(SELECT * FROM GUILD_MEMBER_TBL WHERE m_idPlayer= @im_idGuild AND serverindex = @iserverindex AND m_idVote = @im_idVote)
SELECT n_Error = '2',f_Text = '이미 투표하셨습니다.'
ELSE
BEGIN
DECLARE @om_cbCount CHAR(1)
SET @om_cbCount = CONVERT(CHAR(1),@im_cbCount)
EXEC('
UPDATE GUILD_VOTE_TBL
SET m_cbCount ' + @om_cbCount + ' = m_cbCount' + @om_cbCount + ' + 1
WHERE m_idVote = + ' + @im_idVote + '
UPDATE GUILD_MEMBER_TBL
SET m_idVote = ''' + @im_idVote + '''
WHERE m_idPlayer= ''' + @im_idGuild + '''
AND serverindex = ''' + @iserverindex + ''''
)
SELECT n_Error = '1',f_Text = 'OK.'
END
RETURN
END
/*
GUILD 투표 하기
ex )
GUILD_VOTE_STR 'U1', @iserverindex,@im_idGuild,@im_idVote,@im_szTitle,@im_szQuestion,
@im_szString1,@im_szString2,@im_szString3,@im_szString4,
@im_cbCount
GUILD_VOTE_STR 'U1', '01','000001',0,'노무현 탄핵?','탄핵이 옳을까요? 아닐까요?',
'옳다','아니다','모르겠다','',
0
*/
IF @iGu = 'U2'
BEGIN
UPDATE GUILD_VOTE_TBL
SET m_cbStatus = '2'
WHERE m_idVote = @im_idVote
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 투표 마감하기
ex )
GUILD_VOTE_STR 'U2',@iserverindex,@im_idGuild,@im_idVote
GUILD_VOTE_STR 'U2','01','000000',123
*/
IF @iGu = 'D1'
BEGIN
UPDATE GUILD_VOTE_TBL
SET m_cbStatus = '3'
WHERE m_idVote = @im_idVote
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 투표 삭제하기
ex )
GUILD_VOTE_STR 'D1',@iserverindex,@im_idGuild,@im_idVote
GUILD_VOTE_STR 'D1','01','000000',123
*/
set nocount off
GO
--
-- Definition for stored procedure GUILD_WAR_STR :
--
GO
/****** Object: Stored Procedure dbo.GUILD_WAR_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].GUILD_WAR_STR
@iGu CHAR(2) ,
@im_idGuild CHAR(6) ,
@iserverindex CHAR(2) ,
@im_idWar INT =0,
@iState CHAR(1) = '', -- 0:진행 1:개최측일반승 2:개최측기권승 3:개최측일반패 4:개최측기권패 9:무승부
@if_idGuild CHAR(7) = '',
@im_idPlayer CHAR(7)='',
@im_nWinPoint INT =0,
@if_nWinPoint INT =0
AS
set nocount on
IF @iGu = 'S1'
BEGIN
SELECT m_idGuild,serverindex,f_idGuild,m_idWar,m_nDeath,m_nSurrender,m_nCount,m_nAbsent,f_nDeath,f_nSurrender,f_nCount,f_nAbsent,State,StartTime
FROM GUILD_WAR_TBL
WHERE serverindex = @iserverindex
AND State = '0'
-- UNION ALL
-- SELECT m_idGuild=f_idGuild,serverindex,m_idWar,m_nDeath=f_nDeath,m_nSurrender=f_nSurrender,m_nCount=f_nCount,State,StartTime
-- FROM GUILD_WAR_TBL
-- WHERE serverindex = @iserverindex
-- AND State = '0'
ORDER BY m_idWar,serverindex
RETURN
END
/*
GUILD WAR 전체 가져오기
ex )
GUILD_WAR_STR 'S1',@im_idGuild,@iserverindex,@im_idWar,@iState,@if_idGuild
GUILD_WAR_STR 'S1','000000','01'
*/
ELSE
IF @iGu = 'S2'
BEGIN
SELECT Max_m_idWar = ISNULL(MAX(m_idWar),0)
FROM GUILD_WAR_TBL
WHERE serverindex = @iserverindex
RETURN
END
/*
Max m_idWAR 가져오기
ex )
GUILD_WAR_STR 'S2','',@iserverindex
GUILD_WAR_STR 'S2','','01'
*/
ELSE
IF @iGu = 'A1'
BEGIN
DECLARE @currDate char(12),@om_nCount INT,@of_nCount INT
SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)
SELECT @om_nCount = COUNT(m_idGuild) FROM GUILD_MEMBER_TBL WHERE m_idGuild = @im_idGuild AND isuse = 'T'
SELECT @of_nCount = COUNT(m_idGuild) FROM GUILD_MEMBER_TBL WHERE m_idGuild = @if_idGuild AND isuse = 'T'
INSERT GUILD_WAR_TBL
(
m_idGuild,serverindex,m_idWar,f_idGuild,m_nDeath,m_nSurrender,m_nCount,m_nAbsent,f_nDeath,f_nSurrender,f_nCount,f_nAbsent,State,StartTime
)
VALUES
(
@im_idGuild,@iserverindex,@im_idWar,@if_idGuild,0,0,@om_nCount,0,0,0,@of_nCount,0,'0',@currDate
)
UPDATE GUILD_MEMBER_TBL
SET m_idWar = @im_idWar
WHERE m_idGuild IN (@im_idGuild,@if_idGuild)
AND serverindex = @iserverindex
AND isuse = 'T'
RETURN
END
/*
GUILD 전 생성하기
ex )
GUILD_WAR_STR 'A1',@im_idGuild,@iserverindex,@im_idWar,@iState,@if_idGuild
GUILD_WAR_STR 'A1','000001','01',123,'','000002'
*/
ELSE
IF @iGu = 'U1'
BEGIN
UPDATE GUILD_WAR_TBL
SET m_nSurrender = m_nSurrender + 1
WHERE m_idWar = @im_idWar
AND m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND State = '0'
UPDATE GUILD_WAR_TBL
SET f_nSurrender = f_nSurrender + 1
WHERE m_idWar = @im_idWar
AND f_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND State = '0'
UPDATE GUILD_MEMBER_TBL
SET m_idWar = 0,
m_nSurrender = m_nSurrender + 1
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND m_idWar > 0
RETURN
END
/*
GUILD 개인 항복
ex )
GUILD_WAR_STR 'U1',@im_idGuild,@iserverindex,@im_idWar,@iState,@if_idGuild,@im_idPlayer
GUILD_WAR_STR 'U1','000001','01',123,'','000002'
*/
ELSE
IF @iGu = 'U2'
BEGIN
UPDATE GUILD_WAR_TBL
SET m_nDeath = m_nDeath + 1
WHERE m_idWar = @im_idWar
AND m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_WAR_TBL
SET f_nDeath = f_nDeath + 1
WHERE m_idWar = @im_idWar
AND f_idGuild = @im_idGuild
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 개인 사망
ex )
GUILD_WAR_STR 'U2',@im_idGuild,@iserverindex,@im_idWar,@iState,@if_idGuild (m_idPlayer)
GUILD_WAR_STR 'U2','000001','01',123,'','000002'
*/
ELSE
IF @iGu = 'U3'
BEGIN
UPDATE GUILD_WAR_TBL
SET State = @iState
WHERE m_idWar = @im_idWar
AND serverindex = serverindex
IF @iState = '1' -- 개최측 일반승리
BEGIN
UPDATE GUILD_TBL
SET m_nWin = m_nWin + 1,m_nWinPoint = @im_nWinPoint
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_TBL
SET m_nLose = m_nLose + 1,m_nWinPoint = @if_nWinPoint
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_MEMBER_TBL
SET m_nWin = m_nWin + 1
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
UPDATE GUILD_MEMBER_TBL
SET m_nLose = m_nLose + 1
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
END
ELSE
IF @iState = '2' -- 개최측 기권승리
BEGIN
UPDATE GUILD_TBL
SET m_nWin = m_nWin + 1,m_nWinPoint = @im_nWinPoint
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_TBL
SET m_nSurrender = m_nSurrender + 1,m_nWinPoint = @if_nWinPoint
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_MEMBER_TBL
SET m_nWin = m_nWin + 1
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
UPDATE GUILD_MEMBER_TBL
SET m_nLose = m_nLose + 1
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
END
ELSE
IF @iState = '3' -- 개최측 일반패배
BEGIN
UPDATE GUILD_TBL
SET m_nLose = m_nLose + 1,m_nWinPoint = @im_nWinPoint
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_TBL
SET m_nWin = m_nWin + 1,m_nWinPoint = @if_nWinPoint
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_MEMBER_TBL
SET m_nLose = m_nLose + 1
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
UPDATE GUILD_MEMBER_TBL
SET m_nWin = m_nWin + 1
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
END
ELSE
IF @iState = '4' -- 개최측 기권패배
BEGIN
UPDATE GUILD_TBL
SET m_nSurrender = m_nSurrender + 1,m_nWinPoint = @im_nWinPoint
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_TBL
SET m_nWin = m_nWin + 1,m_nWinPoint = @if_nWinPoint
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_MEMBER_TBL
SET m_nLose = m_nLose + 1
WHERE m_idGuild = @im_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
UPDATE GUILD_MEMBER_TBL
SET m_nWin = m_nWin + 1
WHERE m_idGuild = @if_idGuild
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
END
UPDATE GUILD_MEMBER_TBL
SET m_idWar = 0
WHERE m_idWar = @im_idWar
AND serverindex = @iserverindex
AND m_idWar > 0
AND isuse='T'
RETURN
END
/*
GUILD 전 종료
ex )
GUILD_WAR_STR 'U3',@im_idGuild,@iserverindex,@im_idWar,@iState,@if_idGuild
GUILD_WAR_STR 'U3','000001','01',123,'','000002'
*/
ELSE
IF @iGu = 'U4'
BEGIN
UPDATE GUILD_WAR_TBL
SET m_nAbsent=m_nAbsent+1
WHERE m_idWar = @im_idWar
AND m_idGuild = @im_idGuild
AND serverindex = @iserverindex
UPDATE GUILD_WAR_TBL
SET f_nAbsent=f_nAbsent+1
WHERE m_idWar = @im_idWar
AND f_idGuild = @im_idGuild
AND serverindex = @iserverindex
RETURN
END
/*
GUILD 마스터 부재
ex )
GUILD_WAR_STR 'U4',@im_idGuild,@iserverindex,@im_idWar,@iState,@if_idGuild (m_idPlayer)
GUILD_WAR_STR 'U4','000001','01',123,'','000002'
*/
set nocount off
GO
--
-- Definition for stored procedure G_TOP_CHARACTERS :
--
GO
/****** Object: Stored Procedure dbo.G_TOP_CHARACTERS Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROCEDURE [dbo].G_TOP_CHARACTERS
--CREATE PROCEDURE G_TOP_CHARACTERS
@topNum INT = 50,
@topLevel INT = 50
AS
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET NOCOUNT ON
DECLARE @currDate AS CHAR(8)
DECLARE @QRY AS VARCHAR(480)
SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
SET @QRY = "INSERT INTO G_TOP_CHARACTER SELECT TOP " + CAST(@topNum AS VARCHAR(3)) + " a.m_szName, a.m_nLevel" +
" FROM CHARACTER_TBL a JOIN" +
" ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL b" +
" ON a.account = b.account WHERE" +
" a.account NOT IN (SELECT * FROM G_TOP_VOID_ACCOUNT) AND" +
" a.m_szName NOT IN (SELECT * FROM G_TOP_VOID_CHAR) AND" +
" a.m_nLevel >= " + CAST(@topLevel AS VARCHAR(3)) + " AND a.m_chAuthority='F' AND" +
" b.m_chLoginAuthority='F' AND" +
" (b.BlockTime <= " + @currDate + " AND b.WebTime <= " + @currDate +
") ORDER BY a.m_nLevel DESC, a.TotalPlayTime ASC"
--SET @QRY="SELECT TOP " + CAST(@topNum AS VARCHAR(3)) + " a.m_szName, a.m_nLevel" +
-- " FROM CHARACTER_TBL a JOIN" +
-- " ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL b" +
-- " ON a.account = b.account WHERE" +
-- " a.account NOT IN (SELECT * FROM G_TOP_VOID_ACCOUNT) AND" +
-- " a.m_szName NOT IN (SELECT * FROM G_TOP_VOID_CHAR) AND" +
-- " a.m_nLevel >= " + CAST(@topLevel AS VARCHAR(3)) + " AND a.m_chAuthority='F' AND" +
-- " b.m_chLoginAuthority='F' AND" +
-- " (b.BlockTime <= " + @currDate + " AND b.WebTime <= " + @currDate +
-- ") ORDER BY a.m_nLevel DESC, a.TotalPlayTime ASC"
--PRINT @QRY
--DELETE FROM G_TOP_CHARACTER
EXECUTE(@QRY)
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure G_TOP_CHARACTERS :
--
GO
CREATE PROCEDURE [dbo].G_TOP_CHARACTERS;2
--CREATE PROCEDURE G_TOP_CHARACTERS;2
@topNum INT = 50,
@topLevel INT = 50
AS
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET NOCOUNT ON
DECLARE @currDate AS CHAR(8)
DECLARE @QRY AS VARCHAR(480)
SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
--SET @QRY = "INSERT INTO G_TOP_CHARACTER SELECT TOP " + CAST(@topNum AS VARCHAR(3)) + " a.m_szName, a.m_nLevel" +
-- " FROM CHARACTER_TBL a JOIN" +
-- " ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL b" +
-- " ON a.account = b.account WHERE" +
-- " a.account NOT IN (SELECT * FROM G_TOP_VOID_ACCOUNT) AND" +
-- " a.m_szName NOT IN (SELECT * FROM G_TOP_VOID_CHAR) AND" +
-- " a.m_nLevel >= " + CAST(@topLevel AS VARCHAR(3)) + " AND a.m_chAuthority='F' AND" +
-- " b.m_chLoginAuthority='F' AND" +
-- " (b.BlockTime <= " + @currDate + " AND b.WebTime <= " + @currDate +
-- ") ORDER BY a.m_nLevel DESC, a.TotalPlayTime ASC"
SET @QRY="SELECT TOP " + CAST(@topNum AS VARCHAR(3)) + " a.m_szName, a.m_nLevel, a.m_nJob" +
" FROM CHARACTER_TBL a JOIN" +
" ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL b" +
" ON a.account = b.account WHERE" +
" a.account NOT IN (SELECT * FROM G_TOP_VOID_ACCOUNT) AND" +
" a.m_szName NOT IN (SELECT * FROM G_TOP_VOID_CHAR) AND" +
" a.m_nLevel >= " + CAST(@topLevel AS VARCHAR(3)) + " AND a.m_chAuthority='F' AND" +
" b.m_chLoginAuthority='F' AND" +
" (b.BlockTime <= " + @currDate + " AND b.WebTime <= " + @currDate +
") ORDER BY a.m_nLevel DESC, a.TotalPlayTime ASC"
--PRINT @QRY
--DELETE FROM G_TOP_CHARACTER
EXECUTE(@QRY)
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure MAIL_STR :
--
GO
/****** Object: Stored Procedure dbo.MAIL_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE proc [dbo].MAIL_STR
@iGu CHAR(2),
@nMail INT,
@serverindex CHAR(2),
@idReceiver CHAR(7) = '0000000',
@idSender CHAR(7) = '0000000',
@nGold INT = 0,
@tmCreate INT = 0,
@byRead INT = 0,
@szTitle VARCHAR(128) = '',
@szText VARCHAR(1024) = '',
@dwItemId INT = 0,
@nItemNum INT = 0,
@nRepairNumber INT = 0,
@nHitPoint INT = 0,
@nMaxHitPoint INT = 0,
@nMaterial INT = 0,
@byFlag INT = 0,
@dwSerialNumber INT = 0,
@nOption INT = 0,
@bItemResist INT = 0,
@nResistAbilityOption INT = 0,
@idGuild INT = 0,
@nResistSMItemId INT = 0,
@bCharged INT = 0,
@dwKeepTime INT = 0,
@nRandomOptItemId bigINT = 0,
@nPiercedSize INT = 0,
@dwItemId1 INT = 0,
@dwItemId2 INT = 0,
@dwItemId3 INT = 0,
@dwItemId4 INT = 0
------------------- Version9 Pet
,@bPet int = 0,
@nKind int = 0,
@nLevel int = 0,
@dwExp int = 0,
@wEnergy int = 0,
@wLife int = 0,
@anAvailLevel_D int = 0,
@anAvailLevel_C int = 0,
@anAvailLevel_B int = 0,
@anAvailLevel_A int = 0,
@anAvailLevel_S int = 0,
@dwItemId5 int = 0
AS
set nocount on
IF @iGu = 'S1'
BEGIN
SELECT * FROM MAIL_TBL WHERE serverindex = @serverindex AND byRead<90 order by nMail
RETURN
END
ELSE
IF @iGu = 'A1'
BEGIN
INSERT MAIL_TBL
(
nMail,
serverindex,
idReceiver,
idSender,
nGold,
tmCreate,
byRead,
szTitle,
szText,
dwItemId,
nItemNum,
nRepairNumber,
nHitPoint,
nMaxHitPoint,
nMaterial,
byFlag,
dwSerialNumber,
nOption,
bItemResist,
nResistAbilityOption,
idGuild,
nResistSMItemId,
bCharged,
dwKeepTime,
nRandomOptItemId,
nPiercedSize,
dwItemId1,
dwItemId2,
dwItemId3,
dwItemId4,
SendDt
,bPet, nKind, nLevel, dwExp, wEnergy, wLife, anAvailLevel_D, anAvailLevel_C, anAvailLevel_B, anAvailLevel_A, anAvailLevel_S, dwItemId5
)
VALUES
(
@nMail,
@serverindex,
@idReceiver,
@idSender,
@nGold,
@tmCreate,
@byRead,
@szTitle,
@szText,
@dwItemId,
@nItemNum,
@nRepairNumber,
@nHitPoint,
@nMaxHitPoint,
@nMaterial,
@byFlag,
@dwSerialNumber,
@nOption,
@bItemResist,
@nResistAbilityOption,
@idGuild,
@nResistSMItemId,
@bCharged,
@dwKeepTime,
@nRandomOptItemId,
@nPiercedSize,
@dwItemId1,
@dwItemId2,
@dwItemId3,
@dwItemId4,
getdate()
,@bPet, @nKind, @nLevel, @dwExp, @wEnergy, @wLife, @anAvailLevel_D, @anAvailLevel_C, @anAvailLevel_B, @anAvailLevel_A, @anAvailLevel_S ,@dwItemId5
)
RETURN
END
IF @iGu = 'D1'
BEGIN
UPDATE MAIL_TBL SET byRead=90, DeleteDt=getdate() WHERE nMail = @nMail AND serverindex = @serverindex
RETURN
END
IF @iGu = 'U1'
BEGIN
UPDATE MAIL_TBL SET
ItemFlag=90, ItemReceiveDt=getdate()
WHERE nMail = @nMail AND serverindex = @serverindex
RETURN
END
IF @iGu = 'U2'
BEGIN
UPDATE MAIL_TBL SET GoldFlag=90, GetGoldDt=getdate() WHERE nMail = @nMail AND serverindex = @serverindex
RETURN
END
IF @iGu = 'U3'
BEGIN
UPDATE MAIL_TBL SET byRead = 1, ReadDt=getdate() WHERE nMail = @nMail AND serverindex = @serverindex
RETURN
END
set nocount off
GO
--
-- Definition for stored procedure MAIL_STR_REALTIME :
--
GO
/****** Object: Stored Procedure dbo.MAIL_STR_REALTIME Script Date: 7/29/2008 3:34:04 AM ******/
create proc [dbo].MAIL_STR_REALTIME
@iGu CHAR(2),
@serverindex CHAR(2),
@nMail_Before INT = 0,
@nMail_After INT = 0,
@idPlayer CHAR(7) = '0000000',
@nLevel INT = 0,
@iaccount VARCHAR(32) = '',
@tmCreate INT = 0,
@dwSerialNumber INT = 0,
@nHitPoint INT = 0
as
set nocount on
declare @sdate datetime
declare @edate datetime
declare @iserverindex char(2)
set @sdate = '2007-07-18 00:00:00'--'2007-07-10 00:00:00' -- '2007-07-18 00:00:00'
set @edate = '2007-08-31 23:59:00'--'2007-07-10 23:00:00' -- '2007-08-31 23:59:00'
set @iserverindex = cast((cast(@serverindex as int) + 50) as char(2))
IF @iGu = 'S1'
BEGIN
SELECT * FROM MAIL_TBL
WHERE serverindex = @iserverindex AND byRead<90
ORDER BY nMail
RETURN
END
ELSE
IF @iGu = 'U1'
BEGIN
UPDATE MAIL_TBL SET nMail = @nMail_After, serverindex = @serverindex, dwSerialNumber = @dwSerialNumber, nHitPoint = @nHitPoint
WHERE serverindex = @iserverindex and nMail = @nMail_Before
RETURN
END
ELSE
IF @iGu = 'I1'
BEGIN
-- 추천 이벤트 기간인지 확인
IF(getdate() < @sdate or getdate() > @edate) BEGIN
RETURN
END
-- 추천인 인지 확인 -- select top 10 * from ACCOUNT_DBF.dbo.tblEventRecommend
-- IF( NOT EXISTS(SELECT * FROM ACCOUNT_DBF.dbo.tblEventRecommend where byaccount = @iaccount ) ) BEGIN
-- RETURN
-- END
-- 받을수 있는 아이템이 있는지 확인
DECLARE @ItemID int
DECLARE @ItemNum int
DECLARE @bBinds int
SET @ItemID = 0
SET @ItemNum = 1
SET @bBinds = 2
IF( @nLevel = 20 ) BEGIN
SET @ItemID = 26112
END
ELSE IF( @nLevel = 40 ) BEGIN
SET @ItemID = 26211
END
ELSE IF( @nLevel = 60 ) BEGIN
SET @ItemID = 26103
END
ELSE IF( @nLevel = 80 ) BEGIN
SET @ItemID = 30135
END
ELSE IF( @nLevel = 100 ) BEGIN
SET @ItemID = 5800
SET @bBinds = 0
END
ELSE IF( @nLevel = 120 ) BEGIN
SET @ItemID = 4703
SET @bBinds = 0
END
IF( @ItemID = 0 ) BEGIN
RETURN
END
-- 메일 아이디 할당 하기
DECLARE @nMaxMailID int
SELECT @nMaxMailID = MAX(nMail)+1 from MAIL_TBL where serverindex = @iserverindex
SET @nMaxMailID = ISNULL( @nMaxMailID, 0 )
-- 아이템 주기
DECLARE @szTitle VARCHAR(128)
DECLARE @szText VARCHAR(1024)
SET @szTitle = '레벨 업을 축하 드립니다!'
SET @szText = '캐릭터 레벨 업 기념 상품을 보내드립니다 ^ㅁ^* 프리프와 함께 즐거운 하루 보내시기 바랍니다. 감사합니다.'
EXEC dbo.MAIL_STR 'A1', @nMaxMailID, @iserverindex, @idPlayer, '0000000', 0, @tmCreate, 0, @szTitle, @szText,@ItemID, @ItemNum, 0, 0, 0, 0, @bBinds
RETURN
END
GO
--
-- Definition for stored procedure MAKE_RANKING_STR :
--
GO
/****** Object: Stored Procedure dbo.MAKE_RANKING_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].MAKE_RANKING_STR
@iserverindex CHAR(2) = '01'
AS
set nocount on
DECLARE @currDate char(10),@om_nCount INT,@of_nCount INT
SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2)
-- + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)
--R1 : 최강길드
--R2 : 최다승
--R3 : 최다패
--R4 : 최다항복패
--R5 : 최고결속력
--R6 : 최고자금
--R7 : 평균고랩
--R8 : 최대플레이
--SELECT * FROM RANKING_DBF.dbo.RANKING_TBL
--DELETE RANKING_DBF.dbo.RANKING_TBL
--R1 : 최강길드
IF EXISTS(SELECT * FROM RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex)
BEGIN
DELETE RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex
END
BEGIN
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R1',s_date = @currDate,-- m_Title = '최강길드',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority ='F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_nWinPoint DESC,m_nWin DESC
--R2 : 최다승
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R2',s_date = @currDate,--m_Title = '최다승',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_nWin DESC,CreateTime
--R3 : 최다패
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R3',s_date = @currDate,-- m_Title = '최다패',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_nLose DESC,m_nSurrender DESC
--R4 : 최다항복패
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R4',s_date = @currDate,-- m_Title = '최다항복패',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_nSurrender DESC,m_nLose DESC
--R5 : 최고결속력
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R5',s_date = @currDate,-- m_Title = '최고결속력',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_MaximumUnity DESC,CreateTime
--R6 : 최고자금
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R6',s_date = @currDate,-- m_Title = '최고자금',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_nGuildGold DESC,CreateTime
--R7 : 평균고랩
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R7',s_date = @currDate,-- m_Title = '평균고랩',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND A.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_AvgLevel DESC,CreateTime
--R8 : 최대플레이
INSERT RANKING_DBF.dbo.RANKING_TBL
(
Gu, s_date,
serverindex,
m_dwLogo,
m_idGuild,
m_szGuild,
m_szName,
m_nWin,
m_nLose,
m_nSurrender,
m_MaximumUnity,
m_AvgLevel,
m_nGuildGold,
m_nWinPoint,
m_nPlayTime,
CreateTime
)
SELECT TOP 20 Gu = 'R8',s_date = @currDate,-- m_Title = '평균고랩',
serverindex = MAX(A.serverindex),
m_dwLogo = MAX(A.m_dwLogo),
B.m_idGuild,
m_szGuild = MAX(A.m_szGuild),
m_szName = MAX(C.m_szName),
m_nWin = MAX(A.m_nWin),
m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
m_nSurrender = MAX(A.m_nSurrender),
m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
CreateTime = MAX(A.CreateTime)
FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
WHERE A.m_idGuild = B.m_idGuild
AND C.serverindex = E.serverindex
AND C.m_idPlayer = E.m_idPlayer
AND B.m_nMemberLv = 0
AND B.m_idPlayer = C.m_idPlayer
AND A.serverindex = B.serverindex
AND B.serverindex = C.serverindex
AND C.serverindex = @iserverindex
AND D.m_chLoginAuthority = 'F'
AND C.account = D.account
GROUP BY B.m_idGuild
ORDER BY m_nPlayTime DESC,CreateTime
END
set nocount off
GO
--
-- Definition for stored procedure MESSENGER_STR :
--
GO
/****** Object: Stored Procedure dbo.MESSENGER_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure dbo.MESSENGER_STR
@iGu char(2) = 'S1',
@im_idPlayer char(7) = '0000001',
@iserverindex char(2) = '01',
@if_idPlayer char(7) = '',
@im_dwSex int = 0,
@im_nJob int = 0,
@iState char(1) ='',
@im_dwState int = 0
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql=''
IF @iGu = 'A1'
BEGIN
IF EXISTS(SELECT * FROM MESSENGER_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND f_idPlayer = @if_idPlayer)
UPDATE MESSENGER_TBL
SET State = 'T',
m_dwState = @im_dwState
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND f_idPlayer = @if_idPlayer
ELSE
INSERT MESSENGER_TBL
(m_idPlayer,serverindex,f_idPlayer,m_nJob,m_dwSex,m_dwState,State,CreateTime)
VALUES
(@im_idPlayer,@iserverindex,@if_idPlayer,@im_nJob,@im_dwSex,0,'T',GETDATE())
RETURN
END
/*
메신저 친구 저장하기
ex )
MESSENGER_STR 'A1',@im_idPlayer,@iserverindex,@if_idPlayer,@im_nJob,@im_dwSex
MESSENGER_STR 'A1','000001','01','000002',1,1
*/
ELSE
----------------------------------------
-- 메신저 리스트 가져오기
-- MESSENGER_STR 'S1','000001','01'
IF @iGu = 'S1' BEGIN
SET @sql=N'
EXEC uspLoadMessengerList @pserverindex, @pPlayerID
'
EXECUTE sp_executesql @sql,
N'@pPlayerID char(7), @pserverindex char(2)',
@im_idPlayer, @iserverindex
RETURN
END
----------------------------------------
-- 나를 등록한 메신저 리스트 가져오기
-- MESSENGER_STR 'S2','000001','02'
ELSE
IF @iGu = 'S2' BEGIN
SET @sql=N'
EXEC uspLoadMessengerListRegisterMe @pserverindex, @pPlayerID
'
EXECUTE sp_executesql @sql,
N'@pPlayerID char(7), @pserverindex char(2)',
@im_idPlayer, @iserverindex
RETURN
END
ELSE
IF @iGu = 'D1'
BEGIN
UPDATE MESSENGER_TBL
SET State = 'D'
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND f_idPlayer = @if_idPlayer
AND State = 'T'
RETURN
END
ELSE
IF @iGu = 'D2'
BEGIN
DELETE MESSENGER_TBL
WHERE m_idPlayer NOT IN
(SELECT m_idPlayer
FROM CHARACTER_TBL where serverindex = @iserverindex)
and serverindex = @iserverindex
DELETE MESSENGER_TBL
WHERE f_idPlayer NOT IN
(SELECT m_idPlayer
FROM CHARACTER_TBL where serverindex = @iserverindex)
and serverindex = @iserverindex
DELETE MESSENGER_TBL
WHERE State = 'D'
and serverindex = @iserverindex
RETURN
END
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure MoveUserToTown :
--
GO
/****** Object: Stored Procedure dbo.MoveUserToTown Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROCEDURE dbo.MoveUserToTown
@pName varchar(32),
@serverindex char(2),
@pDestination int = 0
AS
SET NOCOUNT ON
DECLARE @PosX REAL, @PosY REAL, @PosZ REAL
IF NOT EXISTS ( SELECT * FROM CHARACTER_TBL WHERE m_szName=@pName ) BEGIN
SELECT retValue = 9001, retMsg='There are no user'
RETURN
END
IF @pDestination=2 BEGIN
SET @PosX=3896
SET @PosY=59
SET @PosZ=4378
END
ELSE IF @pDestination=1 BEGIN
SET @PosX=8321
SET @PosY=100
SET @PosZ=3720
END
ELSE BEGIN
SET @PosX=7161
SET @PosY=100
SET @PosZ=3264
END
UPDATE CHARACTER_TBL SET dwWorldID=1, m_vPos_x=@PosX, m_vPos_y=@PosY, m_vPos_z=@PosZ, m_idCompany=1
WHERE m_szName=@pName
IF @@ROWCOUNT=0
SELECT retValue=9002, retMsg='Error during update data'
ELSE
SELECT retValue=1, retMsg='Move user success'
RETURN
GO
--
-- Definition for stored procedure NEW_MESSENGER_STR :
--
GO
/****** Object: Stored Procedure dbo.NEW_MESSENGER_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure dbo.NEW_MESSENGER_STR
@iGu char(2) = 'S1',
@im_idPlayer char(7) = '0000001',
@iserverindex char(2) = '01',
@if_idPlayer char(7) = '',
@im_dwSex int = 0,
@im_nJob int = 0,
@iState char(1) ='',
@im_dwState int = 0
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql=''
IF @iGu = 'A1'
BEGIN
IF EXISTS(SELECT * FROM MESSENGER_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex AND f_idPlayer = @if_idPlayer)
UPDATE MESSENGER_TBL
SET State = 'T',
m_dwState = @im_dwState
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND f_idPlayer = @if_idPlayer
ELSE
INSERT MESSENGER_TBL
(m_idPlayer,serverindex,f_idPlayer,m_nJob,m_dwSex,m_dwState,State,CreateTime)
VALUES
(@im_idPlayer,@iserverindex,@if_idPlayer,@im_nJob,@im_dwSex,0,'T',GETDATE())
RETURN
END
/*
메신저 친구 저장하기
ex )
NEW_MESSENGER_STR 'A1',@im_idPlayer,@iserverindex,@if_idPlayer,@im_nJob,@im_dwSex
NEW_MESSENGER_STR 'A1','000001','01','000002',1,1
*/
ELSE
----------------------------------------
-- 메신저 리스트 가져오기
-- NEW_MESSENGER_STR 'S1','000001','01'
IF @iGu = 'S1' BEGIN
SET @sql=N'
EXEC uspLoadMessengerList @pserverindex, @pPlayerID
'
EXECUTE sp_executesql @sql,
N'@pPlayerID char(7), @pserverindex char(2)',
@im_idPlayer, @iserverindex
RETURN
END
----------------------------------------
-- 나를 등록한 메신저 리스트 가져오기
-- NEW_MESSENGER_STR 'S2','000001','02'
ELSE
IF @iGu = 'S2' BEGIN
SET @sql=N'
EXEC uspLoadMessengerListRegisterMe @pserverindex, @pPlayerID
'
EXECUTE sp_executesql @sql,
N'@pPlayerID char(7), @pserverindex char(2)',
@im_idPlayer, @iserverindex
RETURN
END
ELSE
IF @iGu = 'D1'
BEGIN
UPDATE MESSENGER_TBL
SET State = 'D'
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND f_idPlayer = @if_idPlayer
AND State = 'T'
RETURN
END
ELSE
IF @iGu = 'D2'
BEGIN
DELETE MESSENGER_TBL
WHERE m_idPlayer NOT IN
(SELECT m_idPlayer
FROM CHARACTER_TBL where serverindex = @iserverindex)
and serverindex = @iserverindex
DELETE MESSENGER_TBL
WHERE f_idPlayer NOT IN
(SELECT m_idPlayer
FROM CHARACTER_TBL where serverindex = @iserverindex)
and serverindex = @iserverindex
DELETE MESSENGER_TBL
WHERE State = 'D'
and serverindex = @iserverindex
RETURN
END
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure PARTY_STR :
--
GO
/****** Object: Stored Procedure dbo.PARTY_STR Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROC [dbo].PARTY_STR
@iGu CHAR(2) = 'S1',
@im_idPlayer CHAR(7) = '0000001',
@iserverindex CHAR(2) = '01',
@ipartyname VARCHAR(16) = ''
AS
set nocount on
IF @iGu = 'A1'
BEGIN
IF EXISTS(SELECT * FROM PARTY_TBL WHERE m_idPlayer = @im_idPlayer AND serverindex = @iserverindex)
UPDATE PARTY_TBL
SET partyname = @ipartyname
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
ELSE
INSERT PARTY_TBL
(m_idPlayer,serverindex,partyname)
VALUES
(@im_idPlayer,@iserverindex,@ipartyname)
RETURN
END
/*
파티이름 저장하기
ex )
PARTY_STR 'A1',@im_idPlayer,@iserverindex,@ipartyname
PARTY_STR 'A1','000001','01','불타는닭갈비'
*/
ELSE
IF @iGu = 'S1'
BEGIN
SELECT partyname
FROM PARTY_TBL
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
RETURN
END
/*
나의 파티이름 가져오기
ex )
PARTY_STR 'S1',@im_idPlayer,@iserverindex
PARTY_STR 'S1','000001','01'
*/
ELSE
IF @iGu = 'S2'
BEGIN
SELECT partyname,m_idPlayer
FROM PARTY_TBL
WHERE serverindex = @iserverindex
ORDER BY partyname
RETURN
END
/*
나의 파티이름 전체 가져오기
ex )
PARTY_STR 'S2','',@iserverindex
PARTY_STR 'S2','','01'
*/
set nocount off
GO
--
-- Definition for stored procedure QUEST_STR :
--
GO
/****** Object: Stored Procedure dbo.QUEST_STR Script Date: 7/29/2008 3:34:04 AM ******/
-- select m_idPlayer,serverindex,m_wId from QUEST_TBL
-- GROUP BY m_idPlayer,serverindex,m_wId
--
-- QUEST_STR 'D1','001011','02',36
-- QUEST_STR 'S1','001022','01'
CREATE PROC [dbo].QUEST_STR
@iGu CHAR(2) = 'S1',
@m_idPlayer CHAR(7) = '0000001',
@serverindex CHAR(2) = '01',
@m_wId int = null,
@m_nState tinyint = null,
@m_wTime int = null,
@m_nKillNPCNum_0 tinyint = null,
@m_nKillNPCNum_1 tinyint = null,
@m_bPatrol tinyint = null,
@m_bDialog tinyint = null,
@m_bReserve3 tinyint = null,
@m_bReserve4 tinyint = null,
@m_bReserve5 tinyint = null,
@m_bReserve6 tinyint = null,
@m_bReserve7 tinyint = null,
@m_bReserve8 tinyint = null,
@m_Inventory varchar(6940) = '',
@m_apIndex varchar(345) = '',
@m_adwEquipment varchar(135) = '',
@m_dwObjIndex varchar(345) = ''
AS
IF @iGu = 'S1'
BEGIN
SELECT m_idPlayer,serverindex,m_wId,m_nState,m_wTime,m_nKillNPCNum_0,m_nKillNPCNum_1,
m_bPatrol,m_bDialog,m_bReserve3,m_bReserve4,m_bReserve5,m_bReserve6,m_bReserve7,m_bReserve8
FROM QUEST_TBL
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
ORDER BY m_idPlayer,serverindex,m_wId
END
/*
퀘스트 전체 가져오기
ex )
QUEST_STR 'S1',@m_idPlayer,@serverindex
QUEST_STR 'S1','000001','01'
*/
IF @iGu = 'A1'
BEGIN
IF NOT EXISTS(SELECT * FROM QUEST_TBL WHERE m_idPlayer = @m_idPlayer AND serverindex = @serverindex AND m_wId = @m_wId)
INSERT QUEST_TBL
( m_idPlayer,serverindex,m_wId,m_nState,m_wTime,m_nKillNPCNum_0,m_nKillNPCNum_1,
m_bPatrol,m_bDialog,m_bReserve3,m_bReserve4,m_bReserve5,m_bReserve6,m_bReserve7,m_bReserve8)
VALUES
(@m_idPlayer,@serverindex,@m_wId,@m_nState,@m_wTime,@m_nKillNPCNum_0,@m_nKillNPCNum_1,
@m_bPatrol,@m_bDialog,@m_bReserve3,@m_bReserve4,@m_bReserve5,@m_bReserve6,@m_bReserve7,@m_bReserve8)
ELSE
UPDATE QUEST_TBL
SET m_nState = @m_nState,
m_wTime = @m_wTime,
m_nKillNPCNum_0 = @m_nKillNPCNum_0,
m_nKillNPCNum_1 = @m_nKillNPCNum_1,
m_bPatrol = @m_bPatrol,
m_bDialog = @m_bDialog,
m_bReserve3 = @m_bReserve3,
m_bReserve4 = @m_bReserve4,
m_bReserve5 = @m_bReserve5,
m_bReserve6 = @m_bReserve6,
m_bReserve7 = @m_bReserve7,
m_bReserve8 = @m_bReserve8
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
AND m_wId = @m_wId
IF @m_Inventory <> ''
UPDATE INVENTORY_TBL
SET m_Inventory = @m_Inventory,
m_apIndex = @m_apIndex,
m_adwEquipment = @m_adwEquipment,
m_dwObjIndex = @m_dwObjIndex
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
END
/*
퀘스트 저장하기
ex )
QUEST_STR 'A1',@m_idPlayer,@serverindex,@m_wId,@m_nState,@m_wTime,@m_nKillNPCNum_0,@m_nKillNPCNum_1,@m_bPatrol,@m_bDialog,
@m_bReserve3,@m_bReserve4,@m_bReserve5,@m_bReserve6,@m_bReserve7,@m_bReserve8,@m_Inventory, @m_apIndex, @m_adwEquipmen, @m_dwObjIndex
QUEST_STR 'A1',@m_idPlayer,@serverindex,@m_wId = 1,@m_nState = 0,@m_wTime = 65535,@m_nKillNPCNum_0 = 0,@m_nKillNPCNum_1 = 0,@m_bPatrol = 0,@m_bDialog = 0,
@m_bReserve3 = 0,@m_bReserve4 = 0,@m_bReserve5 = 0,@m_bReserve6 = 0,@m_bReserve7 = 0,@m_bReserve8 = 0,@m_Inventory = '$', @m_apIndex = '$', @m_adwEquipmen = '$', @m_dwObjIndex = '$'
*/
IF @iGu = 'U1'
BEGIN
UPDATE QUEST_TBL
SET m_nState = @m_nState,
m_wTime = @m_wTime,
m_nKillNPCNum_0 = @m_nKillNPCNum_0,
m_nKillNPCNum_1 = @m_nKillNPCNum_1,
m_bPatrol = @m_bPatrol,
m_bDialog = @m_bDialog,
m_bReserve3 = @m_bReserve3,
m_bReserve4 = @m_bReserve4,
m_bReserve5 = @m_bReserve5,
m_bReserve6 = @m_bReserve6,
m_bReserve7 = @m_bReserve7,
m_bReserve8 = @m_bReserve8
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
AND m_wId = @m_wId
IF @m_Inventory <> ''
UPDATE INVENTORY_TBL
SET m_Inventory = @m_Inventory,
m_apIndex = @m_apIndex,
m_adwEquipment = @m_adwEquipment,
m_dwObjIndex = @m_dwObjIndex
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
END
/*
퀘스트 수정하기
ex )
QUEST_STR 'U1',@m_idPlayer,@serverindex,@m_wId,@m_nState,@m_wTime,@m_nKillNPCNum_0,@m_nKillNPCNum_1,@m_bPatrol,@m_bDialog,
@m_bReserve3,@m_bReserve4,@m_bReserve5,@m_bReserve6,@m_bReserve7,@m_bReserve8,@m_Inventory, @m_apIndex, @m_adwEquipmen, @m_dwObjIndex
QUEST_STR 'U1',@m_idPlayer,@serverindex,@m_wId = 1,@m_nState = 0,@m_wTime = 65535,@m_nKillNPCNum_0 = 0,@m_nKillNPCNum_1 = 0,@m_bPatrol = 0,@m_bDialog = 0,
@m_bReserve3 = 0,@m_bReserve4 = 0,@m_bReserve5 = 0,@m_bReserve6 = 0,@m_bReserve7 = 0,@m_bReserve8 = 0,@m_Inventory = '$', @m_apIndex = '$', @m_adwEquipmen = '$', @m_dwObjIndex = '$'
*/
IF @iGu = 'D1'
BEGIN
DELETE QUEST_TBL
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
AND m_wId = @m_wId
IF @m_Inventory <> ''
UPDATE INVENTORY_TBL
SET m_Inventory = @m_Inventory,
m_apIndex = @m_apIndex,
m_adwEquipment = @m_adwEquipment,
m_dwObjIndex = @m_dwObjIndex
WHERE m_idPlayer = @m_idPlayer
AND serverindex = @serverindex
END
/*
퀘스트 삭제하기
ex )
QUEST_STR 'D1',@m_idPlayer,@serverindex,@m_wId,@m_nState,@m_wTime,@m_nKillNPCNum_0,@m_nKillNPCNum_1,@m_bPatrol,@m_bDialog,
@m_bReserve3,@m_bReserve4,@m_bReserve5,@m_bReserve6,@m_bReserve7,@m_bReserve8,@m_Inventory, @m_apIndex, @m_adwEquipmen, @m_dwObjIndex
QUEST_STR 'D1',@m_idPlayer,@serverindex,@m_wId = 1,@m_nState = 0,@m_wTime = 65535,@m_nKillNPCNum_0 = 0,@m_nKillNPCNum_1 = 0,@m_bPatrol = 0,@m_bDialog = 0,
@m_bReserve3 = 0,@m_bReserve4 = 0,@m_bReserve5 = 0,@m_bReserve6 = 0,@m_bReserve7 = 0,@m_bReserve8 = 0,@m_Inventory = '$', @m_apIndex = '$', @m_adwEquipmen = '$', @m_dwObjIndex = '$'
*/
RETURN
GO
--
-- Definition for stored procedure TAG_STR :
--
GO
/****** Object: Stored Procedure dbo.TAG_STR Script Date: 7/29/2008 3:34:05 AM ******/
CREATE PROC [dbo].TAG_STR
@iGu CHAR(2) = 'S1',
@im_idPlayer CHAR(7) = '0000001',
@iserverindex CHAR(2) = '01',
@if_idPlayer CHAR(7) = '',
@im_Message VARCHAR(255) = ''
AS
set nocount on
IF @iGu = 'A1'
BEGIN
DECLARE @count int
SELECT @count = COUNT(*)
FROM TAG_TBL
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
AND State = 'T'
IF @count < 20
BEGIN
INSERT TAG_TBL
(m_idPlayer,serverindex,f_idPlayer,m_Message,State,CreateTime)
VALUES
(@im_idPlayer,@iserverindex,@if_idPlayer,@im_Message,'T',GETDATE())
SELECT fError='0'
RETURN
END
ELSE
BEGIN
SELECT fError='1'
RETURN
END
END
/*
쪽지 저장하기
ex )
TAG_STR 'A1',@im_idPlayer,@iserverindex,@if_idPlayer,@im_Message
TAG_STR 'A1','000001','01','000002','안녕하세요?'
*/
ELSE
IF @iGu = 'S1'
BEGIN
SELECT a.f_idPlayer, b.m_szName, a.m_Message,CreateTime=CONVERT(CHAR(8),a.CreateTime,112)
FROM TAG_TBL a, CHARACTER_TBL b
WHERE a.m_idPlayer = @im_idPlayer
AND a.m_idPlayer = b.m_idPlayer
AND a.serverindex = @iserverindex
AND a.serverindex = b.serverindex
AND a.State = 'T'
ORDER BY a.CreateTime
UPDATE TAG_TBL
SET State = 'D'
WHERE m_idPlayer = @im_idPlayer
AND serverindex = @iserverindex
SELECT fError='0'
RETURN
END
/*
쪽지 가져오기
ex )
TAG_STR 'S1',@im_idPlayer,@iserverindex
TAG_STR 'S1','000001','01'
*/
ELSE
IF @iGu = 'D1'
BEGIN
DELETE TAG_TBL
WHERE m_idPlayer NOT IN
(SELECT m_idPlayer
FROM CHARACTER_TBL)
DELETE TAG_TBL
WHERE f_idPlayer NOT IN
(SELECT m_idPlayer
FROM CHARACTER_TBL)
DELETE TAG_TBL
WHERE State = 'D'
AND CreateTime < DATEADD(d,-7,GETDATE())
RETURN
END
/*
쪽지 삭제하기( 캐릭터 삭제했을시, 일정시간이 지났을때 )
ex )
TAG_STR 'D1'
TAG_STR 'D1'
*/
set nocount off
GO
--
-- Definition for stored procedure WANTED_STR :
--
GO
/****** Object: Stored Procedure dbo.WANTED_STR Script Date: 7/29/2008 3:34:05 AM ******/
CREATE PROC [dbo].WANTED_STR
@iGu char(2) = 'S1',
@im_idPlayer CHAR(7) = '',
@iserverindex CHAR(2) = '',
@ipenya INT = 0,
@iszMsg VARCHAR(40) = ''
AS
SET NOCOUNT ON
IF @iGu = 'A1'
BEGIN
IF EXISTS(SELECT m_idPlayer FROM WANTED_TBL WHERE m_idPlayer =@im_idPlayer AND serverindex = @iserverindex)
UPDATE WANTED_TBL
SET penya = penya + @ipenya,
szMsg = CASE WHEN @iszMsg = '' THEN szMsg ELSE @iszMsg END,
s_date = CONVERT(CHAR(8),DATEADD(d,30,GETDATE()),112) + '2359'
WHERE m_idPlayer =@im_idPlayer
AND serverindex = @iserverindex
ELSE
INSERT WANTED_TBL
(m_idPlayer,serverindex,penya,szMsg,s_date,CreateTime)
VALUES
(@im_idPlayer,@iserverindex,@ipenya, @iszMsg,CONVERT(CHAR(8),DATEADD(d,30,GETDATE()),112) + '2359',GETDATE())
END
/*
현상금 걸기
ex )
WANTED_STR 'A1',@im_idPlayer,@iserverindex,@ipenya,@iszMsg
WANTED_STR 'A1','000001','01',1000,1
*/
ELSE
IF @iGu = 'S1'
BEGIN
IF EXISTS(SELECT m_idPlayer FROM WANTED_TBL WHERE s_date <= CONVERT(CHAR(8),GETDATE(),112) + '2359' AND serverindex = @iserverindex)
DELETE WANTED_TBL
WHERE s_date <= CONVERT(CHAR(8),GETDATE(),112) + '2359'
AND serverindex = @iserverindex
SELECT A.m_idPlayer,A.serverindex,B.m_szName,szMsg = ISNULL(A.szMsg,''),A.penya,A.s_date
FROM WANTED_TBL A,CHARACTER_TBL B
WHERE A.m_idPlayer = B.m_idPlayer
AND A.serverindex = B.serverindex
END
/*
현상금 리스트 가져오기
ex )
WANTED_STR 'S1',@im_idPlayer,@iserverindex
WANTED_STR 'S1','','01'
*/
ELSE
IF @iGu = 'D1'
BEGIN
DELETE WANTED_TBL
WHERE m_idPlayer =@im_idPlayer
AND serverindex = @iserverindex
END
/*
현상금 삭제
ex )
WANTED_STR 'D1',@im_idPlayer,@iserverindex
WANTED_STR 'D1','000001','01'
*/
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspAddMessenger :
--
GO
/****** Object: Stored Procedure dbo.uspAddMessenger Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspAddMessenger
@serverindex char(2),
@idPlayer char(7),
@idFriend char(7)
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM tblMessenger WHERE serverindex = @serverindex AND idPlayer = @idPlayer AND idFriend = @idFriend)
BEGIN
UPDATE tblMessenger SET chUse = 'T', bBlock = 0
WHERE serverindex = @serverindex AND idPlayer = @idPlayer AND idFriend = @idFriend
END
ELSE
BEGIN
INSERT tblMessenger (serverindex, idPlayer, idFriend) VALUES (@serverindex, @idPlayer, @idFriend)
END
IF EXISTS(SELECT * FROM tblMessenger WHERE serverindex = @serverindex AND idPlayer = @idFriend AND idFriend = @idPlayer)
BEGIN
UPDATE tblMessenger SET chUse = 'T', bBlock = 0
WHERE serverindex = @serverindex AND idPlayer = @idFriend AND idFriend = @idPlayer
END
ELSE
BEGIN
INSERT tblMessenger (serverindex, idPlayer, idFriend) VALUES (@serverindex, @idFriend, @idPlayer)
END
GO
--
-- Definition for stored procedure uspAddNewCombat :
--
GO
/****** Object: Stored Procedure dbo.uspAddNewCombat Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspAddNewCombat
@pCombatID int,
@pserverindex char(2),
@pComment varchar(1000)=''
AS
SET NOCOUNT ON
INSERT INTO tblCombatInfo(CombatID,serverindex,Status,StartDt,EndDt,Comment)
VALUES (@pCombatID, @pserverindex, '10', NULL, NULL, @pComment)
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspCancelGuildToCombat :
--
GO
/****** Object: Stored Procedure dbo.uspCancelGuildToCombat Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspCancelGuildToCombat
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6)
AS
SET NOCOUNT ON
UPDATE tblCombatJoinGuild SET Status='11' WHERE CombatID=@pCombatID AND serverindex=@pserverindex AND GuildID=@pGuildID
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspChangeMultiServer :
--
GO
/****** Object: Stored Procedure dbo.uspChangeMultiServer Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROCEDURE [dbo].uspChangeMultiServer
@pserverindex char(2),
@pidPlayer char(7),
@pMultiServer int
AS
SET NOCOUNT ON
/* UPDATE CHARACTER_TBL SET MultiServer=@pMultiServer
WHERE m_idPlayer=@pidPlayer and serverindex=@pserverindex
IF @@ROWCOUNT=0 BEGIN
SELECT 9001
RETURN
END
*/ SELECT 1
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspCombatContinue :
--
GO
/****** Object: Stored Procedure dbo.uspCombatContinue Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspCombatContinue
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6),
@pStraightWin int
AS
SET NOCOUNT ON
UPDATE dbo.tblCombatJoinGuild
SET StraightWin = @pStraightWin
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspConvertCharacterSkill :
--
GO
/****** Object: Stored Procedure dbo.uspConvertCharacterSkill Script Date: 7/29/2008 3:34:05 AM ******/
CREATE proc dbo.uspConvertCharacterSkill
@serverindex char(2),
@pPlayerID char(7)='',
@pEndPlayerID char(7)='',
@pSkillList varchar(5000)=''
AS
SET NOCOUNT ON
DECLARE @SkillExp int,
@SkillLevel int,
@SkillID int,
@SkillStatus int
DECLARE @SkillString varchar(500),
@SkillStringLength int
DECLARE @SkillSetStartPos int,
@SkillSetEndPos int,
@SkillSetLength int
DECLARE @SkillElemStartPos int,
@SkillElemEndPos int,
@SkillElemLength int,
@SkillElemIndex int,
@SkillElemString int
SELECT -1 as SkillExp,
-1 as SkillLevel,
-1 as SkillID,
-1 as SkillStatus
INTO #TMP
DECLARE @CharacterLevel int,
@Job int,
@CharExp bigint
DECLARE @ExtraPointForJob int,
@ExtraPointForLevelExp int,
@LevelExpRatio int,
@FinalSkillPoint int
IF @pPlayerID='' BEGIN
DELETE FROM tblSkillPoint WHERE serverindex=@serverindex
END
ELSE BEGIN
DELETE FROM tblSkillPoint WHERE serverindex=@serverindex and PlayerID BETWEEN @pPlayerID and @pEndPlayerID
END
IF @pPlayerID='' BEGIN
DECLARE curCharacter CURSOR FOR
SELECT m_idPlayer, m_nJob, m_nLevel, m_aJobSkill, m_nExp1 FROM CHARACTER_TBL WHERE serverindex=@serverindex AND isblock<>'D'
END
ELSE BEGIN
DECLARE curCharacter CURSOR FOR
SELECT m_idPlayer, m_nJob, m_nLevel, m_aJobSkill, m_nExp1 FROM CHARACTER_TBL WHERE serverindex=@serverindex and m_idPlayer BETWEEN @pPlayerID and @pEndPlayerID
END
OPEN curCharacter
FETCH NEXT FROM curCharacter INTO @pPlayerID, @Job, @CharacterLevel, @pSkillList, @CharExp
--PRINT 'START'
WHILE(@@FETCH_STATUS=0) BEGIN
--PRINT 'START'
-- READ FROM TABLE
--SELECT @CharacterLevel=m_nLevel , @pSkillList=m_aJobSkill FROM CHARACTER_TBL WHERE m_idPlayer=@pPlayerID and serverindex=@serverindex
SELECT @SkillStringLength = LEN(@pSkillList)
SELECT @SkillSetStartPos = 0
SELECT @SkillSetEndPos = -1
DECLARE @SkillPosition int, @SkillPoint int, @Point int
SET @SkillPosition=0
SET @SkillPoint=0
SET @Point=0
SET @ExtraPointForJob =0
SET @ExtraPointForLevelExp =0
SET @LevelExpRatio =0
SET @FinalSkillPoint=0
--PRINT @SkillSetEndPos
SET @SkillPosition=-1
SET @ExtraPointForJob=0
SET @ExtraPointForLevelExp=0
SET @LevelExpRatio=0
SET @SkillPoint=0
WHILE ( @SkillSetEndPos <> 0 ) BEGIN
SET @Point=0
SET @SkillPosition = @SkillPosition + 1
SELECT @SkillSetStartPos = @SkillSetEndPos+1
SELECT @SkillSetEndPos = CHARINDEX('/', @pSkillList, @SkillSetStartPos)
--PRINT @SkillSetEndPos
IF @SkillSetEndPos=0 BREAK
SELECT @SkillSetLength = @SkillSetEndPos - @SkillSetStartPos
IF (@SkillSetEndPos>@SkillStringLength)
BREAK
ELSE BEGIN
SELECT @SkillString = SUBSTRING(@pSkillList, @SkillSetStartPos, @SkillSetLength) + ','
--PRINT @SkillString
SET @SkillElemStartPos = 1
SET @SkillElemEndPos = CHARINDEX(',', @SkillString, @SkillElemStartPos)
SET @SkillElemLength = @SkillElemEndPos-@SkillElemStartPos
SET @SkillExp = SUBSTRING(@SkillString, @SkillElemStartPos, @SkillElemLength)
SET @SkillElemStartPos=@SkillElemEndPos+1
SET @SkillElemEndPos=CHARINDEX(',',@SkillString, @SkillElemStartPos)
SET @SkillElemLength = @SkillElemEndPos-@SkillElemStartPos
SET @SkillLevel = SUBSTRING(@SkillString, @SkillElemStartPos, @SkillElemLength)
SET @SkillElemStartPos=@SkillElemEndPos+1
SET @SkillElemEndPos=CHARINDEX(',',@SkillString, @SkillElemStartPos)
SET @SkillElemLength = @SkillElemEndPos-@SkillElemStartPos
SET @SkillID = SUBSTRING(@SkillString, @SkillElemStartPos, @SkillElemLength)
SET @SkillElemStartPos=@SkillElemEndPos+1
SET @SkillElemEndPos=CHARINDEX(',',@SkillString, @SkillElemStartPos)
SET @SkillElemLength = @SkillElemEndPos-@SkillElemStartPos
SET @SkillStatus = SUBSTRING(@SkillString, @SkillElemStartPos, @SkillElemLength)
--SELECT @SkillPosition as 'Skill Position', @SkillExp as 'Skill Exp', @SkillLevel as 'Skill Level', @SkillID as 'Skill ID', @SkillStatus as 'Skill Status'
END
SELECT @Point=CASE WHEN job=0 THEN 1
WHEN job in (1,2,3,4,5) THEN 2
WHEN job>5 THEN 3
ELSE 0 END * @SkillLevel
FROM MANAGE_DBF.dbo.SKILL_TBL WITH (NOLOCK)
WHERE [Index]=@SkillID
--PRINT @Point
SET @SkillPoint = @SkillPoint + @Point
--PRINT @SkillPoint
IF @SkillID > -1 BEGIN
INSERT INTO tblSkillPoint(serverindex, SkillPosition, PlayerID, SkillID, SkillLv)
SELECT @serverindex, @SkillPosition, @pPlayerID, @SkillID, 0
END
END -- END OF WHILE
-- EXTRA POINT FOR JOB
SELECT @ExtraPointForJob=CASE WHEN @Job=0 THEN 0
WHEN @Job in (1,2,3,4,5) THEN 30
WHEN @Job>5 THEN 60
ELSE 0 END
-- EXTRA POINT FOR LEVEL EXP
SELECT @LevelExpRatio = MANAGE_DBF.dbo.fn_GetExpRatio(@CharacterLevel, @CharExp)
SELECT @ExtraPointForLevelExp = CASE WHEN @LevelExpRatio < 33 THEN 0
WHEN @LevelExpRatio BETWEEN 33 AND 65 THEN 1
WHEN @LevelExpRatio BETWEEN 66 AND 98 THEN 2
WHEN @LevelExpRatio BETWEEN 99 AND 100 THEN 3
ELSE 0 END
IF @SkillPoint<(@CharacterLevel-1)*3
SET @SkillPoint = (@CharacterLevel-1)*3
UPDATE CHARACTER_TBL SET SkillExp =0,
SkillPoint =@SkillPoint +@ExtraPointForLevelExp + @ExtraPointForJob,
SkillLv =@SkillPoint +@ExtraPointForLevelExp
WHERE m_idPlayer=@pPlayerID AND serverindex=@serverindex
FETCH NEXT FROM curCharacter INTO @pPlayerID, @Job, @CharacterLevel, @pSkillList, @CharExp
END -- END OF CURSOR
CLOSE curCharacter
DEALLOCATE curCharacter
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspDeleteMessenger :
--
GO
/****** Object: Stored Procedure dbo.uspDeleteMessenger Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspDeleteMessenger
@serverindex char(2),
@idPlayer char(7),
@idFriend char(7)
AS
SET NOCOUNT ON
UPDATE tblMessenger
SET chUse = 'F'
WHERE serverindex = @serverindex
AND ( ( idPlayer = @idPlayer AND idFriend = @idFriend ) OR ( idPlayer = @idFriend AND idFriend = @idPlayer ) )
GO
--
-- Definition for stored procedure uspEndCombat :
--
GO
/****** Object: Stored Procedure dbo.uspEndCombat Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspEndCombat
@pCombatID int,
@pserverindex char(2)
AS
SET NOCOUNT ON
UPDATE tblCombatInfo SET Status='30',EndDt=getdate() WHERE CombatID=@pCombatID AND serverindex=@pserverindex
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN 1
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspExpireCombatReward :
--
GO
/****** Object: Stored Procedure dbo.uspExpireCombatReward Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure [dbo].uspExpireCombatReward
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6),
@pPlayerID char(7) = '0000000'
AS
SET NOCOUNT ON
IF @pPlayerID='0000000' BEGIN
UPDATE tblCombatJoinGuild SET Status='32'
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
END
ELSE BEGIN
UPDATE tblCombatJoinPlayer SET Status='32'
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
AND PlayerID=@pPlayerID
IF @@ROWCOUNT=0 BEGIN
SELECT 9002 as retValue
RETURN
END
END
SELECT 1 as retValue
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspGradeCombatGuild :
--
GO
/****** Object: Stored Procedure dbo.uspGradeCombatGuild Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspGradeCombatGuild
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6),
@pPoint int,
@pReturnCombatFee bigint=0,
@pReward bigint=0,
@pStraightWin int=0
AS
SET NOCOUNT ON
DECLARE @Status varchar(3)
IF @pReward=0 AND @pReturnCombatFee=0
SET @Status='31'
ELSE
SET @Status='30'
UPDATE tblCombatJoinGuild
SET Point=@pPoint,
ReturnCombatFee=@pReturnCombatFee,
Reward=@pReward,
StraightWin=@pStraightWin,
Status=@Status
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspGradeCombatPlayer :
--
GO
/****** Object: Stored Procedure dbo.uspGradeCombatPlayer Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure [dbo].uspGradeCombatPlayer
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6),
@pPlayerID char(7),
@pPoint int,
@pReward bigint=0
AS
SET NOCOUNT ON
INSERT INTO tblCombatJoinPlayer(CombatID, serverindex, GuildID, PlayerID, Point, Reward, Status)
VALUES (@pCombatID, @pserverindex, @pGuildID, @pPlayerID, @pPoint, @pReward, DEFAULT)
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspInitMultiServer :
--
GO
/****** Object: Stored Procedure dbo.uspInitMultiServer Script Date: 7/29/2008 3:34:04 AM ******/
CREATE PROCEDURE [dbo].uspInitMultiServer
@pserverindex char(2),
@pMultiServer int
AS
SET NOCOUNT ON
/* UPDATE CHARACTER_TBL SET MultiServer=0
WHERE serverindex=@pserverindex AND MultiServer=@pMultiServer
*/ SELECT 1
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspJoinGuildToCombat :
--
GO
/****** Object: Stored Procedure dbo.uspJoinGuildToCombat Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspJoinGuildToCombat
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6),
@pCombatFee bigint = 0,
@pStraightWin int = 0
AS
SET NOCOUNT ON
IF EXISTS ( SELECT * FROM tblCombatJoinGuild WHERE CombatID=@pCombatID and GuildID=@pGuildID and serverindex=@pserverindex) BEGIN
UPDATE tblCombatJoinGuild SET Status='12', CombatFee=@pCombatFee, ApplyDt=getdate(), CancelDt=getdate(), StraightWin=@pStraightWin
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
IF @@ROWCOUNT=0 BEGIN
SELECT 9003 as retValue
RETURN
END
END
ELSE BEGIN
INSERT INTO tblCombatJoinGuild(CombatID, serverindex, GuildID, Status, ApplyDt, CombatFee, ReturnCombatFee, Reward, Point, RewardDt, CancelDt, StraightWin)
VALUES (@pCombatID, @pserverindex, @pGuildID, '10', getdate(), @pCombatFee, 0, 0, 0, NULL, NULL, @pStraightWin)
END
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLearnSkill :
--
GO
/****** Object: Stored Procedure dbo.uspLearnSkill Script Date: 7/29/2008 3:34:05 AM ******/
CREATE PROCEDURE dbo.uspLearnSkill
@serverindex char(2),
@pPlayerID char(7),
@pSkillID int,
@pSkillLv int,
@pSkillPosition int
AS
SET NOCOUNT ON
IF @pSkillID=-1 BEGIN
IF EXISTS ( SELECT * FROM dbo.tblSkillPoint WHERE serverindex=@serverindex AND PlayerID=@pPlayerID AND SkillPosition=@pSkillPosition) BEGIN
DELETE tblSkillPoint
WHERE serverindex=@serverindex AND PlayerID=@pPlayerID and SkillPosition=@pSkillPosition
END
END
ELSE BEGIN
IF EXISTS ( SELECT * FROM dbo.tblSkillPoint WHERE serverindex=@serverindex AND PlayerID=@pPlayerID AND SkillPosition=@pSkillPosition) BEGIN
UPDATE tblSkillPoint
SET SkillID=@pSkillID,SkillLv=@pSkillLv
WHERE serverindex=@serverindex AND PlayerID=@pPlayerID AND SkillPosition=@pSkillPosition
END
ELSE BEGIN
INSERT INTO tblSkillPoint VALUES (@serverindex, @pPlayerID, @pSkillID, @pSkillLv, @pSkillPosition)
END
END
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCharacterSkill :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCharacterSkill Script Date: 7/29/2008 3:34:05 AM ******/
CREATE PROCEDURE dbo.uspLoadCharacterSkill
@serverindex char(2),
@pPlayerID char(7)
AS
SET NOCOUNT ON
SELECT PlayerID, SkillID, SkillLv, SkillPosition FROM tblSkillPoint
WHERE PlayerID=@pPlayerID
and serverindex=@serverindex
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCombatBestPlayer :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCombatBestPlayer Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadCombatBestPlayer
@pCombatID int,
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT TOP 1 PlayerID
FROM tblCombatJoinPlayer
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND Reward>0
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCombatGuildInfo :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCombatGuildInfo Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadCombatGuildInfo
@pserverindex char(2),
@pGuildID char(6)
AS
SET NOCOUNT ON
SELECT a.CombatID, a.GuildID, a.Status, a.CombatFee, a.ReturnCombatFee, a.Point, a.Reward
FROM tblCombatJoinGuild a
WHERE a.serverindex=@pserverindex
AND a.GuildID=@pGuildID
AND a.RewardDt is NULL
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCombatGuildList :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCombatGuildList Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure [dbo].uspLoadCombatGuildList
@pCombatID int,
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT CombatID, serverindex, GuildID, Status, ApplyDt, CombatFee, StraightWin, Reward, Point
FROM tblCombatJoinGuild
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
order by ApplyDt
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCombatInfo :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCombatInfo Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadCombatInfo
@pCombatID int,
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT CombatID, serverindex, Status, StartDt, EndDt, Comment
FROM tblCombatInfo
WHERE serverindex=@pserverindex
AND CombatID=@pCombatID
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCombatList :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCombatList Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadCombatList
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT CombatID, serverindex, Status, StartDt, EndDt, Comment
FROM tblCombatInfo
WHERE serverindex=@pserverindex
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadCombatUnpaidList :
--
GO
/****** Object: Stored Procedure dbo.uspLoadCombatUnpaidList Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure [dbo].uspLoadCombatUnpaidList
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT 'G' as Flag, CombatID, serverindex, GuildID, Status, '0000000' as PlayerID, CombatFee, ReturnCombatFee, Reward
FROM tblCombatJoinGuild
WHERE serverindex=@pserverindex
AND Status='30'
UNION ALL
SELECT 'P' as Flag, CombatID, serverindex, GuildID, Status, PlayerID, 0 as CombatFee, 0 as ReturnCombatFee,Reward
FROM tblCombatJoinPlayer
WHERE serverindex=@pserverindex
AND Status='30'
AND Reward>0
ORDER BY CombatID, Flag, Reward, ReturnCombatFee, GuildID
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadGuildMember :
--
GO
/****** Object: Stored Procedure dbo.uspLoadGuildMember Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadGuildMember
@pserverindex char(2),
@pGuildID char(6)
AS
SET NOCOUNT ON
SELECT m_idPlayer
FROM GUILD_MEMBER_TBL
WHERE m_idGuild=@pGuildID
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadMaxCombatID :
--
GO
/****** Object: Stored Procedure dbo.uspLoadMaxCombatID Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadMaxCombatID
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT ISNULL(MAX(CombatID),0) as MaxNum FROM tblCombatInfo
WHERE serverindex=@pserverindex
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadMaxMailID :
--
GO
/****** Object: Stored Procedure dbo.uspLoadMaxMailID Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure dbo.uspLoadMaxMailID
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT MAX(nMail) as MaxMailID
FROM dbo.MAIL_TBL
WHERE serverindex=@pserverindex
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadMessenger :
--
GO
/****** Object: Stored Procedure dbo.uspLoadMessenger Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspLoadMessenger
@serverindex char(2),
@idPlayer char(7)
AS
SET NOCOUNT ON
SELECT idPlayer, idFriend, bBlock FROM tblMessenger
WHERE serverindex = @serverindex AND idPlayer = @idPlayer AND chUse = 'T'
GO
--
-- Definition for stored procedure uspLoadMessengerList :
--
GO
/****** Object: Stored Procedure dbo.uspLoadMessengerList Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure dbo.uspLoadMessengerList
@pserverindex char(2),
@pPlayerID char(7)
AS
SET NOCOUNT ON
--select f_idPlayer, m_nJob, m_dwSex, m_dwState
--from MESSENGER_TBL_sos
SELECT a.f_idPlayer, b.m_nJob, b.m_dwSex, a.m_dwState
FROM dbo.MESSENGER_TBL a
INNER JOIN dbo.CHARACTER_TBL b
ON (a.serverindex=b.serverindex AND a.f_idPlayer=b.m_idPlayer)
WHERE a.serverindex=@pserverindex
AND a.m_idPlayer=@pPlayerID
AND a.State='T'
AND b.isblock='F'
ORDER BY a.CreateTime
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadMessengerListRegisterMe :
--
GO
/****** Object: Stored Procedure dbo.uspLoadMessengerListRegisterMe Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure dbo.uspLoadMessengerListRegisterMe
@pserverindex char(2),
@pPlayerID char(7)
AS
SET NOCOUNT ON
--select f_idPlayer, m_dwState
--from MESSENGER_TBL_sos
SELECT a.f_idPlayer, count(a.f_idPlayer)
FROM MESSENGER_TBL a, MESSENGER_TBL b
WHERE a.f_idPlayer=b.m_idPlayer
AND a.serverindex=b.serverindex
AND a.m_idPlayer = @pPlayerID
AND a.serverindex = @pserverindex
AND a.State = 'T'
AND b.State = 'T'
GROUP BY a.f_idPlayer
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadPlayerData :
--
GO
/****** Object: Stored Procedure dbo.uspLoadPlayerData Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspLoadPlayerData
@iserverindex char(2)
AS
SET NOCOUNT ON
SELECT m_idPlayer, m_szName, m_dwSex, m_nLevel, m_nJob, m_nMessengerState
FROM CHARACTER_TBL
WHERE serverindex = @iserverindex ORDER BY m_idPlayer
GO
--
-- Definition for stored procedure uspLoadRemoveItemFromGuildBank :
--
GO
/****** Object: Stored Procedure dbo.uspLoadRemoveItemFromGuildBank Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadRemoveItemFromGuildBank
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT nNum,
idGuild,
ItemIndex,
ItemSerialNum,
ItemCnt,
DeleteCnt,
DeleteRequestCnt
FROM tblRemoveItemFromGuildBank
WHERE serverindex=@pserverindex
AND ItemFlag=0
-- Error Handling
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspLoadSealChar :
--
GO
/****** Object: Stored Procedure dbo.uspLoadSealChar Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspLoadSealChar
@serverindex char(2),
@account VARCHAR(32) = ''
AS
SET NOCOUNT ON
select TOP 3 playerslot,m_idPlayer,m_szName
from CHARACTER_TBL
where serverindex=@serverindex and account= @account and isblock='F'
order by playerslot
GO
--
-- Definition for stored procedure uspLoadWinnerGuildInfo :
--
GO
/****** Object: Stored Procedure dbo.uspLoadWinnerGuildInfo Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspLoadWinnerGuildInfo
@pCombatID int,
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT CombatID, serverindex, GuildID, Status, ApplyDt, CombatFee, StraightWin, Reward, Point
FROM tblCombatJoinGuild
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND StraightWin>0
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspProvideItemToCharacter :
--
GO
/****** Object: Stored Procedure dbo.uspProvideItemToCharacter Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure dbo.uspProvideItemToCharacter
@pPlayerID char(7),
@pserverindex char(2),
@pItemIndex int,
@pItemCnt int=1,
@pAbilityOption int=0,
@pEndTime char(8)=NULL,
@pItemResist int=0,
@pResAbilityOpt int=0,
@pCharged int=0,
@pSender char(7)='0000000',
@pRandomOption int=0,
@pPiercedSize int=0,
@pPierceID1 int=0,
@pPierceID2 int=0,
@pPierceID3 int=0,
@pPierceID4 int=0,
@pKeepTime int=0
AS
SET NOCOUNT ON
DECLARE @ItemName varchar(32)
IF @pItemIndex=12 OR @pItemIndex=13 OR @pItemIndex=14 OR @pItemIndex=15 BEGIN
SET @ItemName='penya'
END
ELSE BEGIN
SET @ItemName=@pItemIndex
END
INSERT INTO ITEM_SEND_TBL
(m_idPlayer, serverindex,
Item_Name, Item_count, m_nAbilityOption, End_Time, m_bItemResist, m_nResistAbilityOption, nRandomOptItemId,
m_bCharged, idSender,
nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3,
m_dwKeepTime)
VALUES
(@pPlayerID, @pserverindex,
@ItemName, @pItemCnt, @pAbilityOption, @pEndTime, @pItemResist, @pResAbilityOpt, @pRandomOption,
@pCharged, @pSender,
@pPiercedSize, @pPierceID1, @pPierceID2, @pPierceID3, @pPierceID4,
@pKeepTime)
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspRankGuildCombatPlayer :
--
GO
/****** Object: Stored Procedure dbo.uspRankGuildCombatPlayer Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspRankGuildCombatPlayer
@pserverindex char(2)
AS
SET NOCOUNT ON
SELECT a.PlayerID, max(b.m_nJob) as Job, sum(a.Point) as PointSummary
FROM tblCombatJoinPlayer a INNER JOIN CHARACTER_TBL b ON (a.PlayerID=b.m_idPlayer)
GROUP BY a.PlayerID
HAVING sum(a.Point)>0
ORDER BY sum(a.Point)
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspRemoveItemFromGuildBank :
--
GO
/****** Object: Stored Procedure dbo.uspRemoveItemFromGuildBank Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspRemoveItemFromGuildBank
@pserverindex char(2),
@pNum int,
@pDeleteCnt int
AS
SET NOCOUNT ON
UPDATE tblRemoveItemFromGuildBank
SET ItemFlag=1, DeleteCnt=@pDeleteCnt, DeleteDt=getdate()
WHERE serverindex=@pserverindex
AND nNum=@pNum
IF @@ROWCOUNT=0 BEGIN
SELECT retValue ='0'
END
ELSE BEGIN
SELECT retValue = '1'
END
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspReservRemoveItemFromCharacter :
--
GO
/****** Object: Stored Procedure dbo.uspReservRemoveItemFromCharacter Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure dbo.uspReservRemoveItemFromCharacter
@pserverindex char(2),
@pPlayerID char(7),
@pItemIndex int,
@pItemCnt int=1,
@pStorage char(1)='I',
@pAbilityOpt int=0,
@pItemResist int=0,
@pResAbilityOpt int=0,
@pRandomOpt int=0,
@pPiercedSize int=0,
@pPierceID1 int=0,
@pPierceID2 int=0,
@pPierceID3 int=0,
@pPierceID4 int=0,
@pRequestUser varchar(32)='EoCRM'
AS
SET NOCOUNT ON
INSERT INTO ITEM_REMOVE_TBL
(serverindex, m_idPlayer,
Item_Name, Item_count, m_nAbilityOption, m_bItemResist, m_nResistAbilityOption, RandomOption,
State,
RequestUser)
VALUES
(@pserverindex, @pPlayerID,
@pItemIndex, @pItemCnt, @pAbilityOpt, @pItemResist, @pResAbilityOpt, @pRandomOpt,
@pStorage,
@pRequestUser)
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspReservRemoveItemFromCharacter_1 :
--
GO
/****** Object: Stored Procedure dbo.uspReservRemoveItemFromCharacter_1 Script Date: 7/29/2008 3:34:05 AM ******/
CREATE proc [dbo].uspReservRemoveItemFromCharacter_1
@pserverindex char(2),
@pPlayerID char(7),
@pItemIndex varchar(32),
@pItemCnt int=1,
@pStorage char(1)='I',
@pAbilityOpt varchar(20),
@pItemResist int=0,
@pResAbilityOpt varchar(20),
@pRandomOpt int=0,
@pPiercedSize int=0,
@pPierceID1 int=0,
@pPierceID2 int=0,
@pPierceID3 int=0,
@pPierceID4 int=0,
@pRequestUser varchar(32)='EoCRM'
AS
SET NOCOUNT ON
if @pAbilityOpt = '&4' or @pResAbilityOpt = '&8'
select @pAbilityOpt = 0, @pResAbilityOpt = 0, @pItemResist = 0, @pRandomOpt = 0
INSERT INTO ITEM_REMOVE_TBL
(serverindex, m_idPlayer, Item_Name, Item_count, m_nAbilityOption, m_bItemResist, m_nResistAbilityOption, RandomOption, State, RequestUser)
VALUES
(@pserverindex, @pPlayerID, @pItemIndex, @pItemCnt, cast(@pAbilityOpt as int), @pItemResist, cast(@pResAbilityOpt as int), @pRandomOpt, @pStorage, @pRequestUser)
GO
--
-- Definition for stored procedure uspRewardCombatGuild :
--
GO
/****** Object: Stored Procedure dbo.uspRewardCombatGuild Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspRewardCombatGuild
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6)
AS
SET NOCOUNT ON
UPDATE tblCombatJoinGuild
SET RewardDt=getdate(),
Status='31'
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspRewardCombatPlayer :
--
GO
/****** Object: Stored Procedure dbo.uspRewardCombatPlayer Script Date: 7/29/2008 3:34:05 AM ******/
CREATE Procedure [dbo].uspRewardCombatPlayer
@pCombatID int,
@pserverindex char(2),
@pGuildID char(6),
@pPlayerID char(7)
AS
SET NOCOUNT ON
UPDATE tblCombatJoinPlayer
SET RewardDt=getdate(),
Status='31'
WHERE CombatID=@pCombatID
AND serverindex=@pserverindex
AND GuildID=@pGuildID
AND PlayerID=@pPlayerID
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspSavePocket :
--
GO
/****** Object: Stored Procedure dbo.uspSavePocket Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspSavePocket
@serverindex CHAR(2),
@idPlayer CHAR(7),
@nPocket int,
@pszItem VARCHAR(4290) = '$',
@pszIndex VARCHAR(215) = '$',
@pszObjIndex VARCHAR(215) = '$',
@pszExt VARCHAR(2000) = '$',
@pszPiercing VARCHAR(2000) = '$',
@pszPet VARCHAR(2689) = '$',
@bExpired int = 1,
@tExpirationDate int = 0
AS
set nocount on
UPDATE tblPocket
SET szItem = @pszItem,
szIndex = @pszIndex,
szObjIndex = @pszIndex,
bExpired = @bExpired,
tExpirationDate = @tExpirationDate
WHERE serverindex = @serverindex AND idPlayer = @idPlayer AND nPocket = @nPocket
UPDATE tblPocketExt
SET szExt = @pszExt,
szPiercing = @pszPiercing,
szPet = @pszPet
WHERE serverindex = @serverindex AND idPlayer = @idPlayer AND nPocket = @nPocket
GO
--
-- Definition for stored procedure uspSetSealChar :
--
GO
/****** Object: Stored Procedure dbo.uspSetSealChar Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspSetSealChar
@serverindex char(2),
@im_idPlayer CHAR(7) = ''
AS
SET NOCOUNT ON
UPDATE CHARACTER_TBL
SET isblock = 'S'
WHERE m_idPlayer = @im_idPlayer AND serverindex = @serverindex
UPDATE MESSENGER_TBL
SET State = 'D'
WHERE m_idPlayer = @im_idPlayer AND serverindex = @serverindex AND State = 'T'
UPDATE MAIL_TBL
SET byRead=90, DeleteDt = getdate()
WHERE idReceiver = @im_idPlayer AND serverindex = @serverindex AND byRead<>90
RETURN
GO
--
-- Definition for stored procedure uspSetSealCharUpdate :
--
GO
/****** Object: Stored Procedure dbo.uspSetSealCharUpdate Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspSetSealCharUpdate
@serverindex char(2),
@im_idPlayer CHAR(7) = '',
@account VARCHAR(32) = '',
@nPlayerSlot INT =0,
@im_idPlayerW CHAR(7) = ''
AS
SET NOCOUNT ON
UPDATE CHARACTER_TBL
SET isblock = 'F' ,account = @account, playerslot = @nPlayerSlot
WHERE m_idPlayer = @im_idPlayerW AND serverindex = @serverindex AND isblock = 'S'
RETURN
GO
--
-- Definition for stored procedure uspStartCombat :
--
GO
/****** Object: Stored Procedure dbo.uspStartCombat Script Date: 7/29/2008 3:34:04 AM ******/
CREATE Procedure [dbo].uspStartCombat
@pCombatID int,
@pserverindex char(2)
AS
SET NOCOUNT ON
UPDATE tblCombatInfo SET Status='20' WHERE CombatID=@pCombatID AND serverindex=@pserverindex AND Status='10'
IF @@ROWCOUNT=0 BEGIN
SELECT 9001 as retValue
RETURN
END
UPDATE tblCombatJoinGuild SET Status='20' WHERE CombatID=@pCombatID AND serverindex=@pserverindex AND Status='10'
IF @@ROWCOUNT=0 BEGIN
SELECT 9002 as retValue
RETURN
END
SELECT 1 as retValue
RETURN
SET NOCOUNT OFF
GO
--
-- Definition for stored procedure uspUpdateMessenger :
--
GO
/****** Object: Stored Procedure dbo.uspUpdateMessenger Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].uspUpdateMessenger
@serverindex char(2),
@idPlayer char(7),
@idFriend char(7),
@bBlock int
AS
SET NOCOUNT ON
UPDATE tblMessenger
SET bBlock = @bBlock
WHERE serverindex = @serverindex
AND idPlayer = @idPlayer
AND idFriend = @idFriend
GO
--
-- Definition for stored procedure usp_Guild_Combat_1to1_Battle :
--
GO
/****** Object: Stored Procedure dbo.usp_Guild_Combat_1to1_Battle Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].usp_Guild_Combat_1to1_Battle
@serverindex char(2),
@combatID int,
@m_idWorld int,
@m_idGuild_1st char(6),
@m_idGuild_2nd char(6),
@State char(1),
@iGu char(2)
as
set nocount on
set xact_abort on
if @iGu = 'I1'
begin
delete from GUILD_COMBAT_1TO1_BATTLE_TBL
where serverindex = @serverindex and combatID = @combatID and m_idWorld = @m_idWorld
insert into GUILD_COMBAT_1TO1_BATTLE_TBL(serverindex, combatID, m_idWorld, m_idGuild_1st, m_idGuild_2nd, State)
select @serverindex, @combatID, @m_idWorld, @m_idGuild_1st, @m_idGuild_2nd, @State
end
if @iGu = 'U1'
begin
update GUILD_COMBAT_1TO1_BATTLE_TBL
set End_Time = getdate(), State = @State
where serverindex = @serverindex and combatID = @combatID and m_idWorld = @m_idWorld
end
GO
--
-- Definition for stored procedure usp_Guild_Combat_1to1_Battle_Person :
--
GO
/****** Object: Stored Procedure dbo.usp_Guild_Combat_1to1_Battle_Person Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].usp_Guild_Combat_1to1_Battle_Person
@serverindex char(2),
@combatID int,
@m_idGuild char(6),
@m_idPlayer char(7),
@m_nSeq int,
@State char(1),
@iGu char(2)
as
set nocount on
set xact_abort on
if @iGu = 'I1'
begin
insert into GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL (serverindex, combatID, m_idGuild, m_idPlayer, m_nSeq, State)
select @serverindex, @combatID, @m_idGuild, @m_idPlayer, @m_nSeq, @State
end
if @iGu = 'U1'
begin
if @State = 'N'
begin
update GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL
set Start_Time = getdate(), State = @State
where serverindex = @serverindex and combatID = @combatID and m_idGuild = @m_idGuild and m_idPlayer = @m_idPlayer
end
else
begin
update GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL
set End_Time = getdate(), State = @State
where serverindex = @serverindex and combatID = @combatID and m_idGuild = @m_idGuild and m_idPlayer = @m_idPlayer
end
end
if @iGu = 'S1'
begin
select m_idPlayer
from GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL
where serverindex = @serverindex and combatID = @combatID and m_idGuild = @m_idGuild
order by m_nSeq
end
if @iGu = 'D1'
begin
delete from GUILD_COMBAT_1TO1_BATTLE_PERSON_TBL
where serverindex = @serverindex and combatID = @combatID and m_idGuild = @m_idGuild
end
GO
--
-- Definition for stored procedure usp_Guild_Combat_1to1_CombatID :
--
GO
/****** Object: Stored Procedure dbo.usp_Guild_Combat_1to1_CombatID Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].usp_Guild_Combat_1to1_CombatID
@serverindex char(2)
as
set nocount on
if not exists (select * from GUILD_COMBAT_1TO1_TENDER_TBL where serverindex = @serverindex)
begin
select 0 as combatID
end
else
begin
select max(combatID) as combatID
from GUILD_COMBAT_1TO1_TENDER_TBL
where serverindex = @serverindex
end
GO
--
-- Definition for stored procedure usp_Guild_Combat_1to1_Tender :
--
GO
/****** Object: Stored Procedure dbo.usp_Guild_Combat_1to1_Tender Script Date: 7/29/2008 3:34:05 AM ******/
create proc [dbo].usp_Guild_Combat_1to1_Tender
@serverindex char(2),
@combatID int,
@m_idGuild char(6),
@m_nPenya int,
@State char(1),
@iGu char(2)
as
set nocount on
set xact_abort on
if @iGu = 'I1'
begin
insert into GUILD_COMBAT_1TO1_TENDER_TBL(serverindex, combatID, m_idGuild, m_nPenya, State)
select @serverindex, @combatID, @m_idGuild, @m_nPenya, @State
end
-- T : 입찰 및 추가 입찰
-- F : 입찰 실패
-- C : 입찰 취소
-- E : 대전 종료
-- G : 입찰 실패 길드 신청금 수령 완료
-- N : 입찰 실패 길드 신청금 수령 없이 다음 1:1길드대전 오픈
if @iGu = 'U1'
begin
if @State = 'F' or @State = 'C' or @State = 'E'
begin
update GUILD_COMBAT_1TO1_TENDER_TBL
set m_nPenya = @m_nPenya, State = @State, s_date = getdate()
where serverindex = @serverindex and combatID = @combatID and m_idGuild = @m_idGuild and State = 'T'
end
else if @State ='N' or @State = 'G'
begin
update GUILD_COMBAT_1TO1_TENDER_TBL
set m_nPenya = @m_nPenya, State = @State, s_date = getdate()
where serverindex = @serverindex and m_idGuild = @m_idGuild and State = 'F'
end
else
begin
update GUILD_COMBAT_1TO1_TENDER_TBL
set m_nPenya = @m_nPenya, State = @State, s_date = getdate()
where serverindex = @serverindex and combatID = @combatID and m_idGuild = @m_idGuild
end
end
-- 입찰 길드 목록
if @iGu = 'S1'
begin
select m_idGuild, m_nPenya
from GUILD_COMBAT_1TO1_TENDER_TBL
where serverindex = @serverindex and combatID = @combatID and State = 'T'
order by m_nPenya desc, s_date asc
end
-- 입찰 실패 길드 목록
if @iGu = 'S2'
begin
select m_idGuild, m_nPenya
from GUILD_COMBAT_1TO1_TENDER_TBL
where serverindex = @serverindex and State = 'F'
-- where combatID in (select max(combatID), (max(combatID) - 1) from GUILD_COMBAT_1TO1_TENDER_TBL where State = 'F') and State = 'F'
order by m_nPenya desc
end
GO
--
-- Definition for stored procedure usp_get_item_dupes :
--
GO
/****** Object: Stored Procedure dbo.usp_get_item_dupes Script Date: 7/29/2008 4:44:43 AM ******/
CREATE proc [dbo].[usp_get_item_dupes]
@server char(2)
as
set nocount on
Create table #scantemp(
ObjectID bigint,
ItemIndex bigint,
ItemName varchar(500),
ItemCount bigint,
ItemSerialNum bigint not null,
ItemAbilityOpt bigint,
ItemResist bigint,
ItemResistAbility bigint,
RandomOption bigint,
Ultimate varchar(5000))
Create table #chartemp(
ItemSerialNum bigint not null,
ItemIndex int,
ItemCnt int,
m_szName varchar(40))
create index sid on #chartemp (ItemSerialNum)
create index sid on #scantemp (ItemSerialNum)
create table #names (
m_szName varchar(40),
m_Inventory varchar(5000))
exec('insert into #names select m_szName,m_Inventory from openquery(,''select top 5000 m_szName,m_Inventory from INVENTORY_TBL i with(nolock) left join CHARACTER_TBL c with(nolock) on c.m_idPlayer=i.m_idPlayer'')')
declare tmp cursor for
select * from #names
open tmp
declare @m_Inv varchar(5000)
declare @m_szName varchar(40)
fetch next from tmp into @m_szName, @m_Inv
while(@@fetch_status=0) begin
insert into #scantemp exec uspInquiryItemList_New @m_Inv, '', '', ''
insert into #chartemp select ItemSerialNum,ItemIndex,ItemCount,@m_szName from #scantemp
truncate table #scantemp
fetch next from tmp into @m_szName,@m_Inv
if(@@fetch_status<>0) begin
break
end
end
close tmp
deallocate tmp
delete from #chartemp where ItemIndex in (select distinct(ItemIndex) from #chartemp where ItemCnt>1)
--select ItemSerialNum from #scantemp
select m_szName,ItemSerialNum,ItemIndex,szNameEn,(select count(*)from #chartemp p where c.ItemSerialNum=p.ItemSerialNum) as count
from #chartemp c left join ITEM_DBF.dbo.ITEM_TBL i on i.[Index]=c.ItemIndex where ItemSerialNum in (
select sid from (select count(*) count,ItemSerialNum sid,ItemIndex from #chartemp group by ItemSerialNum,ItemIndex)a where count>1 and sid!=0
)
order by count desc, ItemSerialNum
drop table #chartemp
drop table #scantemp
drop table #names
GO
--
-- Definition for stored procedure usp_ranking :
--
GO
/****** Object: Stored Procedure dbo.usp_ranking Script Date: 7/29/2008 3:34:04 AM ******/
CREATE proc [dbo].usp_ranking
as
/**************************************************
created by
soondai@aeonsoft.co.kr
서버별 일일 개인 랭킹순의 집계
집계순위 : 레벨↑, 경험치↑, 플레이시간↓
***************************************************/
set nocount on
set xact_abort on
declare @RankDt smalldatetime
set @RankDt = getdate()
select account into #tmpaccount
from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority <> 'F'
insert into #tmpaccount select account from byGMCharacter
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp1
from CHARACTER_01_DBF.dbo.CHARACTER_TBL
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp2
from CHARACTER_02_DBF.dbo.CHARACTER_TBL
--where m_chAuthority ='F' and account in (select account from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority = 'F')
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp3
from CHARACTER_03_DBF.dbo.CHARACTER_TBL
--where m_chAuthority ='F' and account in (select account from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority = 'F')
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp4
from CHARACTER_04_DBF.dbo.CHARACTER_TBL
--where m_chAuthority ='F' and account in (select account from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority = 'F')
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp5
from CHARACTER_05_DBF.dbo.CHARACTER_TBL
--where m_chAuthority ='F' and account in (select account from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority = 'F')
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp6
from CHARACTER_06_DBF.dbo.CHARACTER_TBL
--where m_chAuthority ='F' and account in (select account from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority = 'F')
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
select top 100 serverindex, m_szName, account, m_nLevel, m_nExp1, TotalPlayTime, @RankDt as RankDt, m_nJob into #tmp7
from CHARACTER_07_DBF.dbo.CHARACTER_TBL
--where m_chAuthority ='F' and account in (select account from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where m_chLoginAuthority = 'F')
where m_chAuthority ='F' and account not in (select account from #tmpaccount)
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
truncate table tblCharacterRank
insert into tblCharacterRank
select * from #tmp1
union all
select * from #tmp2
union all
select * from #tmp3
union all
select * from #tmp4
union all
select * from #tmp5
union all
select * from #tmp6
union all
select * from #tmp7
order by m_nLevel desc, m_nExp1 desc, TotalPlayTime asc
drop table #tmp1, #tmp2, #tmp3, #tmp4, #tmp5, #tmp6, #tmp7, #tmpaccount
set nocount off
set xact_abort off
GO
--
-- Data for table dbo.BANK_EXT_TBL (LIMIT 0,500)
--
INSERT INTO [dbo].[BANK_EXT_TBL] ([m_idPlayer], [serverindex], [m_extBank], [m_BankPiercing], [szBankPet])
VALUES
(N'0000002', N'01', N'0,0,3015168/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/1,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,21/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,36257565639719936/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/0,0,0/$', N'4,3217,3217,3217,3217/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/$', N'0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/1,5,1,9841,178,1,1/0/0/0/0/0/0/0/0/0/0/$')
GO
![[Help]Official Files Database server](http://ragezone.com/hyper728.png)