USE MuOnline
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_CC_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_CC_OFFLINE_GIFT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_DL_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_DL_OFFLINE_GIFT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_FRIEND_STONE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_FRIEND_STONE]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_RINGGIFT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_RINGGIFT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_RINGGIFT_TEMP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_RINGGIFT_TEMP]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_SERIAL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_SERIAL]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ShowResourceInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ShowResourceInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_EMP6]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_EMP6]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Admin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Admin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_BLOOD_CASTLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_BLOOD_CASTLE]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_BLOOD_PLAYCOUNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_BLOOD_PLAYCOUNT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CC_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_CC_OFFLINE_GIFT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CC_OFFLINE_GIFTNAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_CC_OFFLINE_GIFTNAME]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DL_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_DL_OFFLINE_GIFT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DL_OFFLINE_GIFTNAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_DL_OFFLINE_GIFTNAME]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FRIENDSHIP_SERVERRANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_FRIENDSHIP_SERVERRANK]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FRIENDSHIP_STONE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_FRIENDSHIP_STONE]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MU2003_EVENT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_MU2003_EVENT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RegCount_Check]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RegCount_Check]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Register_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Register_Info]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RingAttackEvent_Gift]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RingAttackEvent_Gift]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RingEvent_GiftName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RingEvent_GiftName]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RingEvent_OfflineGift]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RingEvent_OfflineGift]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Serial_Bank]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Serial_Bank]
GO
CREATE TABLE [dbo].[Admin] (
[Id] [char] (10) NOT NULL ,
[Pass] [char] (10) NULL ,
[AdminLevel] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_BLOOD_CASTLE] (
[AccountID] [varchar] (10) NOT NULL ,
[StoneCount] [int] NOT NULL ,
[Check_Code] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_BLOOD_PLAYCOUNT] (
[AccountId] [varchar] (50) NOT NULL ,
[PlayCount] [int] NOT NULL ,
[RecoverCheck] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_CC_OFFLINE_GIFT] (
[Guid] [int] IDENTITY (1, 1) NOT NULL ,
[Server] [int] NULL ,
[AccountID] [varchar] (10) NULL ,
[CharName] [varchar] (10) NULL ,
[GiftKind] [int] NOT NULL ,
[Date_Give] [smalldatetime] NOT NULL ,
[Date_Reg] [smalldatetime] NULL ,
[RegCheck] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_CC_OFFLINE_GIFTNAME] (
[GiftKind] [int] NOT NULL ,
[GiftName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_DL_OFFLINE_GIFT] (
[Guid] [int] IDENTITY (1, 1) NOT NULL ,
[Server] [int] NULL ,
[AccountID] [varchar] (10) NULL ,
[CharName] [varchar] (10) NULL ,
[GiftKind] [int] NOT NULL ,
[Date_Give] [smalldatetime] NOT NULL ,
[Date_Reg] [smalldatetime] NULL ,
[RegCheck] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_DL_OFFLINE_GIFTNAME] (
[GiftKind] [int] NOT NULL ,
[GiftName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_FRIENDSHIP_SERVERRANK] (
[ServerCode] [smallint] NOT NULL ,
[FriendShipStoneCount] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_FRIENDSHIP_STONE] (
[AccountID] [varchar] (10) NOT NULL ,
[ServerCode] [smallint] NOT NULL ,
[CharName] [varchar] (10) NOT NULL ,
[FriendShipStoneCount] [int] NOT NULL ,
[Check_Code] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_MU2003_EVENT] (
[AccountID] [varchar] (50) NOT NULL ,
[EventChips] [smallint] NOT NULL ,
[MuttoIndex] [int] NOT NULL ,
[MuttoNumber] [int] NOT NULL ,
[Check_Code] [char] (1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_RegCount_Check] (
[AccountID] [varchar] (10) NOT NULL ,
[RegCount] [int] NOT NULL ,
[RegAlready] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_Register_Info] (
[F_Register_Section] [smallint] NOT NULL ,
[F_Register_Name] [varchar] (50) NOT NULL ,
[F_Register_TotalCount] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_RingAttackEvent_Gift] (
[GUID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [varchar] (50) NOT NULL ,
[GiftKind] [tinyint] NOT NULL ,
[RegisterDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_RingEvent_GiftName] (
[GiftKind] [tinyint] NOT NULL ,
[GiftName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_RingEvent_OfflineGift] (
[GUID] [smallint] IDENTITY (1, 1) NOT NULL ,
[AccountID] [varchar] (10) NULL ,
[GiftSection] [tinyint] NOT NULL ,
[GiftKind] [tinyint] NOT NULL ,
[RegisterDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_Serial_Bank] (
[F_Serial_Guid] [int] IDENTITY (1, 1) NOT NULL ,
[P_Serial_1] [char] (4) NOT NULL ,
[P_Serial_2] [char] (4) NOT NULL ,
[P_Serial_3] [char] (4) NOT NULL ,
[F_Serial_Section] [smallint] NOT NULL ,
[F_Member_Guid] [int] NULL ,
[F_Member_Id] [char] (10) NULL ,
[F_Register_Section] [smallint] NULL ,
[F_Register_Date] [smalldatetime] NULL ,
[F_Create_Date] [smalldatetime] NOT NULL ,
[F_RegisterCheck] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_BLOOD_CASTLE] WITH NOCHECK ADD
CONSTRAINT [PK_T_BLOOD_CASTLE] PRIMARY KEY CLUSTERED
(
[AccountID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_BLOOD_PLAYCOUNT] WITH NOCHECK ADD
CONSTRAINT [PK_T_BLOOD_PLAYCOUNT] PRIMARY KEY CLUSTERED
(
[AccountId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_CC_OFFLINE_GIFTNAME] WITH NOCHECK ADD
CONSTRAINT [PK_T_CC_OFFLINE_GIFTNAME] PRIMARY KEY CLUSTERED
(
[GiftKind]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_DL_OFFLINE_GIFTNAME] WITH NOCHECK ADD
CONSTRAINT [PK_T_DL_OFFLINE_GIFTNAME] PRIMARY KEY CLUSTERED
(
[GiftKind]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_FRIENDSHIP_SERVERRANK] WITH NOCHECK ADD
CONSTRAINT [PK_T_FRIENDSHIP_SERVERRANK] PRIMARY KEY CLUSTERED
(
[ServerCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_FRIENDSHIP_STONE] WITH NOCHECK ADD
CONSTRAINT [PK_T_T] PRIMARY KEY CLUSTERED
(
[AccountID],
[ServerCode],
[CharName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_MU2003_EVENT] WITH NOCHECK ADD
CONSTRAINT [PK_T_MU2003_EVENT_1] PRIMARY KEY CLUSTERED
(
[AccountID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_RegCount_Check] WITH NOCHECK ADD
CONSTRAINT [PK_T_RegCount_Check] PRIMARY KEY CLUSTERED
(
[AccountID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_Register_Info] WITH NOCHECK ADD
CONSTRAINT [PK_T_Register_Info] PRIMARY KEY CLUSTERED
(
[F_Register_Section]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_RingAttackEvent_Gift] WITH NOCHECK ADD
CONSTRAINT [PK_T_RingAttackEvent_Gift] PRIMARY KEY CLUSTERED
(
[GUID],
[GiftKind],
[RegisterDate]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_RingEvent_OfflineGift] WITH NOCHECK ADD
CONSTRAINT [PK_T_RingEvent_OfflineGift] PRIMARY KEY CLUSTERED
(
[GUID],
[GiftSection]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_Serial_Bank] WITH NOCHECK ADD
CONSTRAINT [PK_T_Serial_Bank] PRIMARY KEY CLUSTERED
(
[P_Serial_1],
[P_Serial_2],
[P_Serial_3]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_T_CC_OFFLINE_GIFT] ON [dbo].[T_CC_OFFLINE_GIFT]([Date_Give]) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_T_DL_OFFLINE_GIFT] ON [dbo].[T_DL_OFFLINE_GIFT]([Date_Give]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_BLOOD_CASTLE] ADD
CONSTRAINT [DF_T_BLOOD_CASTLE_StoneCount] DEFAULT (0) FOR [StoneCount],
CONSTRAINT [DF_T_BLOOD_CASTLE_Check_Code] DEFAULT (0) FOR [Check_Code]
GO
ALTER TABLE [dbo].[T_CC_OFFLINE_GIFT] ADD
CONSTRAINT [DF_T_CC_OFFLINE_GIFT_Date_Reg] DEFAULT (getdate()) FOR [Date_Reg],
CONSTRAINT [DF_T_CC_OFFLINE_GIFT_RegCheck] DEFAULT (0) FOR [RegCheck],
CONSTRAINT [PK_T_CC_OFFLINE_GIFT] PRIMARY KEY NONCLUSTERED
(
[Guid]
) ON [PRIMARY]
GO
CREATE INDEX [IX_T_CC_OFFLINE_GIFT_1] ON [dbo].[T_CC_OFFLINE_GIFT]([AccountID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_DL_OFFLINE_GIFT] ADD
CONSTRAINT [DF_T_DL_OFFLINE_GIFT_Date_Reg] DEFAULT (getdate()) FOR [Date_Reg],
CONSTRAINT [DF_T_DL_OFFLINE_GIFT_RegCheck] DEFAULT (0) FOR [RegCheck],
CONSTRAINT [PK_T_DL_OFFLINE_GIFT] PRIMARY KEY NONCLUSTERED
(
[Guid]
) ON [PRIMARY]
GO
CREATE INDEX [IX_T_DL_OFFLINE_GIFT_1] ON [dbo].[T_DL_OFFLINE_GIFT]([AccountID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_FRIENDSHIP_SERVERRANK] ADD
CONSTRAINT [DF_T_FRIENDSHIP_SERVERRANK_FriendShipStoneCount] DEFAULT (0) FOR [FriendShipStoneCount]
GO
ALTER TABLE [dbo].[T_FRIENDSHIP_STONE] ADD
CONSTRAINT [DF_T_T_FriendShipStoneCount] DEFAULT (0) FOR [FriendShipStoneCount],
CONSTRAINT [DF_T_T_Check_Code] DEFAULT (0) FOR [Check_Code]
GO
ALTER TABLE [dbo].[T_MU2003_EVENT] ADD
CONSTRAINT [DF_T_MU2003_EVENT_EventChips_1] DEFAULT (0) FOR [EventChips],
CONSTRAINT [DF_T_MU2003_EVENT_MuttoIndex_1] DEFAULT ((-1)) FOR [MuttoIndex],
CONSTRAINT [DF_T_MU2003_EVENT_MuttoNumber_1] DEFAULT (0) FOR [MuttoNumber],
CONSTRAINT [DF_T_MU2003_EVENT_Check_Code] DEFAULT (0) FOR [Check_Code]
GO
ALTER TABLE [dbo].[T_RegCount_Check] ADD
CONSTRAINT [DF_T_RegCount_Check_RegCount] DEFAULT (1) FOR [RegCount],
CONSTRAINT [DF_T_RegCount_Check_RegAlready] DEFAULT (0) FOR [RegAlready]
GO
ALTER TABLE [dbo].[T_RingAttackEvent_Gift] ADD
CONSTRAINT [DF_T_RingAttackEvent_Gift_RegisterDate] DEFAULT (getdate()) FOR [RegisterDate]
GO
CREATE INDEX [IX_T_RingAttackEvent_Gift] ON [dbo].[T_RingAttackEvent_Gift]([AccountID]) ON [PRIMARY]
GO
CREATE INDEX [IX_T_RingEvent_OfflineGift] ON [dbo].[T_RingEvent_OfflineGift]([AccountID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_Serial_Bank] ADD
CONSTRAINT [DF_T_Serial_Bank_F_Create_Date] DEFAULT (getdate()) FOR [F_Create_Date],
CONSTRAINT [DF_T_Serial_Bank_F_RegisterCheck] DEFAULT (0) FOR [F_RegisterCheck]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--//************************************************************************
--// ³» ¿ë : ¹Â ¹ÝÁöÀ̺¥Æ® °æÇ°µî·Ï
--// ºÎ ¼ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2004.05.05
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--// ¸®ÅÏ°ª ¼³¸í
--//
--// ¼º°øÄÚµå : 0 : »óÇ°µî·Ï ½ÇÆÐ (¾øÀ½)
--// 1 : »óÇ°µî·Ï ¼º°ø
--// ¼±¹°À̸§ : T_CC_OFFLINE_GIFTNAME °ú GiftKind ¸¦ JOIN ÇÑ °á°ú
--//
--//************************************************************************
CREATE PROCEDURE SP_REG_CC_OFFLINE_GIFT
@AccountID varchar(10),
@CharName varchar(10),
@ServerCode int
As
Begin
BEGIN TRANSACTION
DECLARE @iGIFT_GUID INT -- ¹ÞÀ» »óÇ°ÀÇ GUID
DECLARE @iGIFT_KIND INT -- ¹ÞÀ» »óÇ°ÀÇ Á¾·ù
DECLARE @iGIFT_NAME VARCHAR(50) -- ¹ÞÀ» »óÇ°ÀÇ À̸§
SET NOCOUNT ON
IF EXISTS (SELECT AccountID Guid FROM T_CC_OFFLINE_GIFT WHERE AccountID = @AccountID)
BEGIN
-- ÀÌ »ç¶÷Àº ÀÌÀü¿¡ Çѹø ´ç÷µÈ »ç¶÷À̹ǷΠ¶Ç °æÇ°À» ÁÙ ¼ö ¾ø´Ù.
SELECT 0 As ResultCode, '' As GiftName
END
ELSE
BEGIN
IF EXISTS (SELECT TOP 1 Guid FROM T_CC_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = 0 ORDER BY Guid ASC)
BEGIN
-- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ ³²¾ÆÀÖ´Ù.
SELECT TOP 1 @iGIFT_GUID = Guid, @iGIFT_KIND = GiftKind FROM T_CC_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = 0 ORDER BY Guid ASC
-- ¿©±â¼ ÇØ´ç »ç¿ëÀÚÀÇ °æÇ°À¸·Î ÁöÁ¤ÇØ ÁØ´Ù.
UPDATE T_CC_OFFLINE_GIFT SET Server = @ServerCode, AccountID = @AccountID, CharName = @CharName, Date_Reg = GetDate(), RegCheck = 1 WHERE Guid = @iGIFT_GUID
SELECT @iGIFT_NAME = GiftName FROM T_CC_OFFLINE_GIFTNAME WHERE GiftKind = @iGIFT_KIND
SELECT 1 As ResultCode, @iGIFT_NAME As GiftName
END
ELSE
BEGIN
-- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ Çϳªµµ ¾ø´Ù.
SELECT 0 As ResultCode, '' As GiftName
END
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--//************************************************************************
--// ³» ¿ë : ´ÙÅ©·ÎµåÀÇ ¸¶À½ À̺¥Æ® °æÇ°µî·Ï
--// ºÎ ¼ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2004.08.11
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--// ¸®ÅÏ°ª ¼³¸í
--//
--// ¼º°øÄÚµå : 0 : »óÇ°µî·Ï ½ÇÆÐ (¾øÀ½)
--// 1 : »óÇ°µî·Ï ¼º°ø
--// ¼±¹°À̸§ : T_DL_OFFLINE_GIFTNAME °ú GiftKind ¸¦ JOIN ÇÑ °á°ú
--//
--//************************************************************************
CREATE PROCEDURE SP_REG_DL_OFFLINE_GIFT
@AccountID varchar(10),
@CharName varchar(10),
@ServerCode int
As
Begin
BEGIN TRANSACTION
DECLARE @iGIFT_GUID INT -- ¹ÞÀ» »óÇ°ÀÇ GUID
DECLARE @iGIFT_KIND INT -- ¹ÞÀ» »óÇ°ÀÇ Á¾·ù
DECLARE @iGIFT_NAME VARCHAR(50) -- ¹ÞÀ» »óÇ°ÀÇ À̸§
SET NOCOUNT ON
IF EXISTS (SELECT AccountID Guid FROM T_DL_OFFLINE_GIFT WHERE AccountID = @AccountID)
BEGIN
-- ÀÌ »ç¶÷Àº ÀÌÀü¿¡ Çѹø ´ç÷µÈ »ç¶÷À̹ǷΠ¶Ç °æÇ°À» ÁÙ ¼ö ¾ø´Ù.
SELECT 0 As ResultCode, '' As GiftName
END
ELSE
BEGIN
IF EXISTS (SELECT TOP 1 Guid FROM T_DL_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = CAST(0 AS BIT) ORDER BY Guid ASC)
BEGIN
-- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ ³²¾ÆÀÖ´Ù.
SELECT TOP 1 @iGIFT_GUID = Guid, @iGIFT_KIND = GiftKind FROM T_DL_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = CAST(0 AS BIT) ORDER BY Guid ASC
-- ¿©±â¼ ÇØ´ç »ç¿ëÀÚÀÇ °æÇ°À¸·Î ÁöÁ¤ÇØ ÁØ´Ù.
UPDATE T_DL_OFFLINE_GIFT SET Server = @ServerCode, AccountID = @AccountID, CharName = @CharName, Date_Reg = GetDate(), RegCheck = CAST(1 AS BIT) WHERE Guid = @iGIFT_GUID
SELECT @iGIFT_NAME = GiftName FROM T_DL_OFFLINE_GIFTNAME WHERE GiftKind = @iGIFT_KIND
SELECT 1 As ResultCode, @iGIFT_NAME As GiftName
END
ELSE
BEGIN
-- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ Çϳªµµ ¾ø´Ù.
SELECT 0 As ResultCode, '' As GiftName
END
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
--//************************************************************************
--// ³» ¿ë : ¹Â Ä£±¸À̺¥Æ®
--// ºÎ ¼ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2004.02.11
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************
CREATE PROCEDURE SP_REG_FRIEND_STONE
@AccountID varchar(10), -- °èÁ¤¸í
@ServerCode smallint, -- ¼¹öÄÚµå
@CharName varchar(10) -- Äɸ¯¸í
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON
-- 1 . ÇØ´ç °èÁ¤ÀÇ ½ºÅæ°³¼ö¸¦ Áõ°¡½ÃÅ´
IF NOT EXISTS (SELECT FriendShipStoneCount FROM T_FRIENDSHIP_STONE WHERE AccountID = @AccountID and ServerCode = @ServerCode and CharName = @CharName)
BEGIN
INSERT T_FRIENDSHIP_STONE (AccountID, ServerCode, CharName, FriendShipStoneCount) VALUES (@AccountID, @ServerCode, @CharName, 1)
END
ELSE
BEGIN
UPDATE T_FRIENDSHIP_STONE
SET FriendShipStoneCount = FriendShipStoneCount + 1
WHERE AccountID = @AccountID and ServerCode = @ServerCode and CharName = @CharName
END
-- 2 . ¼¹öº° ½ºÅæ°³¼ö Áõ°¡
IF NOT EXISTS (SELECT FriendShipStoneCount FROM T_FRIENDSHIP_SERVERRANK WHERE ServerCode = @ServerCode)
BEGIN
INSERT T_FRIENDSHIP_SERVERRANK VALUES (@ServerCode, 1)
END
ELSE
BEGIN
UPDATE T_FRIENDSHIP_SERVERRANK
SET FriendShipStoneCount = FriendShipStoneCount + 1
WHERE ServerCode = @ServerCode
END
SELECT FriendShipStoneCount FROM T_FRIENDSHIP_STONE WHERE AccountID = @AccountID and ServerCode = @ServerCode and CharName = @CharName
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--//************************************************************************
--// ³» ¿ë : ¹Â ¹ÝÁöÀ̺¥Æ® °æÇ°µî·Ï
--// ºÎ ¼ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2003.12.05
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************
CREATE PROCEDURE SP_REG_RINGGIFT
@AccountID varchar(10), -- °èÁ¤¸í
@GiftSection tinyint -- »óÇ°ÀÇ Á¾·ù (0:¿ë»çÀǹÝÁöµå·Ó, 1:¿ÀÅ©Á×ÀÓ)
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ToDay DATETIME
DECLARE @strToDayStart VARCHAR(32)
DECLARE @strToDayEnd VARCHAR(32)
DECLARE @iToDayRegCount INT
DECLARE @iMaxAllGiftNum INT -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¸ðµç °³¼ö
DECLARE @iMaxTodayGiftNum INT -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¿À´ÃÀÇ °³¼ö
DECLARE @iLeftGifts INT -- ³²Àº »óÇ° °³¼ö
DECLARE @iGUID INT -- GUID
DECLARE @iGiftKind INT -- ´ç÷µÈ »óÇ°¹øÈ£
IF @GiftSection = 0
BEGIN
SET @iMaxAllGiftNum = 80
SET @iMaxTodayGiftNum = 2
END
ELSE
BEGIN
SET @iMaxAllGiftNum = 160
SET @iMaxTodayGiftNum = 5
END
-- 1> . ÀÌÁ¦ ÇØ´ç °æÇ°Á¾·ùÀÇ ³²Àº °æÇ°ÀÌ ÀÖ´ÂÁö È®ÀÎÇÑ´Ù.
SELECT @iLeftGifts = COUNT(*) FROM T_RingEvent_OfflineGift WHERE RegisterDate IS NULL AND GiftSection = @GiftSection
IF @iLeftGifts = 0
BEGIN
SELECT 3 As RegResult, @AccountID, 0 As GiftKind -- ÀÌÁ¦ ´õ ÀÌ»ó µî·ÏÇÒ ¼ö ¾ø´Ù¸é ³ª°£´Ù.
END
ELSE
BEGIN
-- 2> . ¿À´Ã µî·ÏÇÒ ¼ö Àִ ȸ¼ö ÀÌ»óÀ¸·Î µî·Ï µÇ¾ú´ÂÁö üũ
SET @ToDay = GetDate()
SET @strToDayStart = CONVERT(char(4), YEAR(@ToDay)) + '-' + CONVERT(char(2), MONTH(@ToDay)) + '-' + CONVERT(char(2), DAY(@ToDay)) + ' 00:00:00'
SET @strToDayEnd = CONVERT(char(4), YEAR(@ToDay)) + '-' + CONVERT(char(2), MONTH(@ToDay)) + '-' + CONVERT(char(2), DAY(@ToDay)) + ' 23:59:59'
SELECT @iToDayRegCount = COUNT(*)
FROM T_RingEvent_OfflineGift
WHERE RegisterDate between @strToDayStart and @strToDayEnd
and @GiftSection = GiftSection
IF @iToDayRegCount >= @iMaxTodayGiftNum -- ¿À´ÃÀÇ ÃÖ´ë »óÇ° µî·Ï ¼ö¸¦ ÃÊ°úÇÏ¸é ³ª°£´Ù.
BEGIN
SELECT 3 As RegResult, @AccountID, 0 As GiftKind
END
ELSE
BEGIN
-- 3> . ÀÌ¹Ì µî·ÏµÈ °èÁ¤ÀÎÁö È®ÀÎÇÑ´Ù.
IF EXISTS ( SELECT * FROM T_RingEvent_OfflineGift WITH (READUNCOMMITTED)
WHERE AccountID = @AccountID)
BEGIN
-- ÀÌ¹Ì µî·ÏµÈ »ç¶÷À̹ǷΠ±×»ç¶÷ÀÇ °èÁ¤¸í Á¤µµ¸¸ º¸³½´Ù.
SELECT 2 As RegResult, @AccountID, 0 As GiftKind
END
ELSE
BEGIN
SELECT TOP 1 @iGUID = GUID, @iGiftKind = GiftKind
FROM T_RingEvent_OfflineGift
WHERE @GiftSection = GiftSection and RegisterDate IS NULL
ORDER BY GUID
UPDATE T_RingEvent_OfflineGift
SET AccountID=@AccountID, RegisterDate=GetDate()
WHERE @iGUID = GUID
-- »õ·Î µî·ÏÇÏ´Â »ç¶÷ÀÌ¸ç µî·Ï ¼º°ø
SELECT 1 As RegResult, @AccountID, @iGiftKind As GiftKind
END
END
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
-- ÀúÀå ÇÁ·Î½ÃÀú »ý¼º
--//************************************************************************
--// ³» ¿ë : ¹Â ¹ÝÁöÀ̺¥Æ® °æÇ°µî·Ï
--// ºÎ ¼ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2003.12.05
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************
CREATE PROCEDURE SP_REG_RINGGIFT_TEMP
@AccountID varchar(10), -- °èÁ¤¸í
@GiftKind tinyint -- »óÇ°ÀÇ Á¾·ù (0:¿ë»çÀǹÝÁöµå·Ó, 1:¿ÀÅ©Á×ÀÓ)
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ToDay DATETIME
DECLARE @strToDayStart VARCHAR(32)
DECLARE @strToDayEnd VARCHAR(32)
DECLARE @iToDayRegCount INT
DECLARE @iMaxAllGiftNum INT -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¸ðµç °³¼ö
DECLARE @iMaxTodayGiftNum INT -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¿À´ÃÀÇ °³¼ö
IF @GiftKind = 0
BEGIN
SET @iMaxAllGiftNum = 80
SET @iMaxTodayGiftNum = 2
END
ELSE
BEGIN
SET @iMaxAllGiftNum = 160
SET @iMaxTodayGiftNum = 3
END
SELECT @iToDayRegCount = COUNT(*)
FROM T_RingAttackEvent_Gift
WHERE @GiftKind = GiftKind
IF @iToDayRegCount >= @iMaxAllGiftNum -- Àüü ÃÖ´ë »óÇ° µî·Ï ¼ö¸¦ ÃÊ°úÇÏ¸é ³ª°£´Ù.
BEGIN
SELECT 3 As RegResult, @AccountID
END
ELSE
BEGIN
SET @ToDay = GetDate()
SET @strToDayStart = CONVERT(char(4), YEAR(@ToDay)) + '-' + CONVERT(char(2), MONTH(@ToDay)) + '-' + CONVERT(char(2), DAY(@ToDay)) + ' 00:00:00'
SET @strToDayEnd = CONVERT(char(4), YEAR(@ToDay)) + '-' + CONVERT(char(2), MONTH(@ToDay)) + '-' + CONVERT(char(2), DAY(@ToDay)) + ' 23:59:59'
SELECT @iToDayRegCount = COUNT(*)
FROM T_RingAttackEvent_Gift
WHERE RegisterDate between @strToDayStart and @strToDayEnd
and @GiftKind = GiftKind
IF @iToDayRegCount >= @iMaxTodayGiftNum -- ¿À´ÃÀÇ ÃÖ´ë »óÇ° µî·Ï ¼ö¸¦ ÃÊ°úÇÏ¸é ³ª°£´Ù.
BEGIN
SELECT 3 As RegResult, @AccountID
END
ELSE
BEGIN
IF EXISTS ( SELECT * FROM T_RingAttackEvent_Gift WITH (READUNCOMMITTED)
WHERE AccountID = @AccountID)
BEGIN
-- ÀÌ¹Ì µî·ÏµÈ »ç¶÷À̹ǷΠ±×»ç¶÷ÀÇ °èÁ¤¸í Á¤µµ¸¸ º¸³½´Ù.
SELECT 2 As RegResult, @AccountID
END
ELSE
BEGIN
INSERT INTO T_RingAttackEvent_Gift
VALUES (@AccountID, @GiftKind, default)
-- »õ·Î µî·ÏÇÏ´Â »ç¶÷ÀÌ¸ç µî·Ï ¼º°ø
SELECT 1 As RegResult, @AccountID
END
END
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
--//************************************************************************
--// ³» ¿ë : ¹Â »ó¿ëÈ 2Áֳ⠱â³ä º¹±Ç À̺¥Æ® °ü·Ã - ½Ã¸®¾ó µî·Ï
--// ºÎ ¼ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2003.10.20
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************
CREATE PROCEDURE SP_REG_SERIAL
@AccountID varchar(10), -- °èÁ¤¸í
@MembGUID int, -- GUID
@SERIAL1 varchar(4), -- ½Ã¸®¾ó1
@SERIAL2 varchar(4), -- ½Ã¸®¾ó2
@SERIAL3 varchar(4) -- ½Ã¸®¾ó3
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON
-- 1 . T_RegCount_Check Å×ÀÌºí¿¡¼ ÇØ´ç °èÁ¤ÀÌ ÀÌ¹Ì µî·ÏÇß´ÂÁö, Ä«¿îÆ®¸¦ ³Ñ¾ú´ÂÁö üũÇÑ´Ù.
DECLARE @MAX_REGCOUNT INT
DECLARE @iREG_COUNT INT
DECLARE @iREG_ALREADY BIT
SET @MAX_REGCOUNT = 20
SET @iREG_ALREADY = 0
IF EXISTS ( SELECT * FROM T_RegCount_Check WITH (READUNCOMMITTED)
WHERE AccountID = @AccountID)
BEGIN
-- T_RegCount_Check ¿¡´Â ÀÌ¹Ì °èÁ¤¸íÀÌ ÀÖÀ» °ÍÀ̹ǷΠº°µµÀÇ °ËÁõ¾øÀÌ µî·Ï¿©ºÎ¸¦ º¯°æÇÑ´Ù.
SELECT @iREG_ALREADY = RegAlready FROM T_RegCount_Check WHERE AccountID = @AccountID
/* IF (@iREG_ALREADY = 1 )
BEGIN
SELECT 5 As RegResult, 0 As F_Register_Section
END
ELSE
BEGIN
*/ SELECT @iREG_COUNT = RegCount
FROM T_RegCount_Check
WHERE AccountID = @AccountID
IF (@iREG_COUNT >= @MAX_REGCOUNT)
BEGIN
SET @iREG_ALREADY = 1
SELECT 2 As RegResult, 0 As F_Register_Section
END
ELSE
BEGIN
UPDATE T_RegCount_Check
SET RegCount = RegCount + 1
WHERE AccountID = @AccountID
END
-- END
END
ELSE
BEGIN
INSERT INTO T_RegCount_Check
VALUES (@AccountID, default, default)
END
/* IF (@iREG_ALREADY =1)
BEGIN
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
RETURN
END
*/
-- 2 . T_Serial_Bank Å×ÀÌºí¿¡ ½Ã¸®¾óÀ» µî·ÏÇÑ´Ù.
DECLARE @REG_CHECK BIT
IF EXISTS ( SELECT F_RegisterCheck FROM T_Serial_Bank WITH (READUNCOMMITTED)
WHERE P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3)
BEGIN
SELECT @REG_CHECK = F_RegisterCheck FROM T_Serial_Bank WITH (READUNCOMMITTED)
WHERE P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3
IF (@REG_CHECK = 0)
BEGIN
UPDATE T_Serial_Bank
SET F_Member_Guid = @MembGUID, F_Member_Id = @AccountID, F_RegisterCheck = 1, F_Register_Date = GetDate()
WHERE P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3
-- T_RegCount_Check ¿¡´Â ÀÌ¹Ì °èÁ¤¸íÀÌ ÀÖÀ» °ÍÀ̹ǷΠº°µµÀÇ °ËÁõ¾øÀÌ µî·Ï¿©ºÎ¸¦ º¯°æÇÑ´Ù.
UPDATE T_RegCount_Check
SET RegAlready = 1
WHERE AccountID = @AccountID
SELECT 0 As RegResult, F_Register_Section
FROM T_Serial_Bank
WHERE P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3
END
ELSE
BEGIN -- ÀÌ¹Ì µî·ÏµÇ¾ú´Ù.
SELECT 1 As RegResult, 0 As F_Register_Section
END
END
ELSE
BEGIN -- ÇØ´ç ½Ã¸®¾óÀº Á¸ÀçÇÏÁö ¾Ê´Â´Ù.
SELECT 3 As RegResult, 0 As F_Register_Section
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ShowResourceInfo
AS
SET NOCOUNT ON
print '*********************************'
print ' 5ÃÊ°£ »ç¿ëµÈ ½Ã½ºÅÛ ÀÚ¿ø·®ÀÔ´Ï´Ù.'
print '*********************************'
SELECT spid, cpu, physical_io
INTO #Temp1 FROM master..sysprocesses
WAITFOR DELAY '0:00:05'
SELECT P.spid, P.cpu-T.cpu AS 'cpu »ç¿ë·®(ms)',
P.physical_io-T.physical_io AS 'Disk R/W·®(Page)',
P.nt_username ' NT »ç¿ëÀÚ¸í', P.program_name 'ÀÀ¿ëÇÁ·Î±×·¥',
P.hostname, P.cmd
FROM master..sysprocesses P, #Temp1 T
WHERE P.spid=T.spid
AND P.cpu-T.cpu+P.physical_io-T.physical_io+P.memusage > 0
DROP TABLE #Temp1
Return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC UP_EMP6 @JOB VARCHAR(4), @YEAR CHAR(4), @SAL BIGINT
AS
SELECT * FROM EMP
WHERE JOB = @JOB AND DATEPART(YY,EDATE) = @YEAR
AND SAL > @SAL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Home
TOP Ranking MU
Magichand
Fanpage
Tags
Login
Register
Topic: Pack Golden Archer Para Version 97d+99¡ (Visto 4402 veces)
January 23, 2017, 02:10:14 AM