USE [MuOnline]
GO
/****** Object: ForeignKey [FK_MEMB_DETA_MEMB_INFO] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[MEMB_DETA] DROP CONSTRAINT [FK_MEMB_DETA_MEMB_INFO]
GO
/****** Object: Table [dbo].[MEMB_DETA] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[MEMB_DETA] DROP CONSTRAINT [FK_MEMB_DETA_MEMB_INFO]
GO
DROP TABLE [dbo].[MEMB_DETA]
GO
/****** Object: StoredProcedure [dbo].[USP_Block_CanCel] Script Date: 04/10/2014 21:57:11 ******/
DROP PROCEDURE [dbo].[USP_Block_CanCel]
GO
/****** Object: StoredProcedure [dbo].[WZ_CONNECT_MEMB] Script Date: 04/10/2014 21:57:11 ******/
DROP PROCEDURE [dbo].[WZ_CONNECT_MEMB]
GO
/****** Object: StoredProcedure [dbo].[WZ_DISCONNECT_MEMB] Script Date: 04/10/2014 21:57:11 ******/
DROP PROCEDURE [dbo].[WZ_DISCONNECT_MEMB]
GO
/****** Object: Table [dbo].[BLOCKING] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[BLOCKING] DROP CONSTRAINT [DF_BLOCKING_ctl1_code]
GO
DROP TABLE [dbo].[BLOCKING]
GO
/****** Object: Table [dbo].[BLOCKING_LOG] Script Date: 04/10/2014 21:57:11 ******/
DROP TABLE [dbo].[BLOCKING_LOG]
GO
/****** Object: Table [dbo].[LuckyCoinsRank] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[LuckyCoinsRank] DROP CONSTRAINT [DF_LuckyCoinsRank_LuckyCoins]
GO
DROP TABLE [dbo].[LuckyCoinsRank]
GO
/****** Object: Table [dbo].[MEMB_INFO] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF_MEMB_INFO_mail_chek]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF_MEMB_INFO_ctl1_code]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__Gobli__0519C6AF]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__GoldC__060DEAE8]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__SCFEx__07020F21]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__SCFIs__07F6335A]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__SCFLu__08EA5793]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__SCFVi__09DE7BCC]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__SCFVi__0AD2A005]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__SCFWa__0BC6C43E]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__WCoin__0CBAE877]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF__MEMB_INFO__WCoin__0DAF0CB0]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF_MEMB_INFO_Admin]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF_MEMB_INFO_core_admin]
GO
ALTER TABLE [dbo].[MEMB_INFO] DROP CONSTRAINT [DF_MEMB_INFO_AutorizaVipMoney]
GO
DROP TABLE [dbo].[MEMB_INFO]
GO
/****** Object: Table [dbo].[MEMB_STAT] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[MEMB_STAT] DROP CONSTRAINT [DF_MEMB_STAT_ConnectStat]
GO
ALTER TABLE [dbo].[MEMB_STAT] DROP CONSTRAINT [DF_MEMB_STAT_OnlineHours]
GO
DROP TABLE [dbo].[MEMB_STAT]
GO
/****** Object: Table [dbo].[MEMB_STAT_GJ] Script Date: 04/10/2014 21:57:11 ******/
DROP TABLE [dbo].[MEMB_STAT_GJ]
GO
/****** Object: Table [dbo].[MEMB_STAT_GJ] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MEMB_STAT_GJ](
[memb___id] [char](10) COLLATE Modern_Spanish_CI_AS NOT NULL,
[name] [char](10) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ServerName] [varchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[ServerCode] [int] NULL,
[IP] [varchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[TM] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[MEMB_STAT] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MEMB_STAT](
[memb___id] [varchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ConnectStat] [tinyint] NOT NULL CONSTRAINT [DF_MEMB_STAT_ConnectStat] DEFAULT ((0)),
[ServerName] [varchar](30) COLLATE Modern_Spanish_CI_AS NULL,
[IP] [varchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[ConnectTM] [smalldatetime] NULL,
[DisConnectTM] [smalldatetime] NULL,
[OnlineHours] [int] NULL CONSTRAINT [DF_MEMB_STAT_OnlineHours] DEFAULT ((0)),
CONSTRAINT [PK_MEMB_STAT] PRIMARY KEY CLUSTERED
(
[memb___id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[MEMB_INFO] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MEMB_INFO](
[memb_guid] [int] IDENTITY(1,1) NOT NULL,
[memb___id] [varchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL,
[memb__pwd] [varchar](12) COLLATE Modern_Spanish_CI_AS NOT NULL,
[memb_name] [varchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL,
[sno__numb] [char](18) COLLATE Modern_Spanish_CI_AS NOT NULL,
[post_code] [char](6) COLLATE Modern_Spanish_CI_AS NULL,
[addr_info] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[addr_deta] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[tel__numb] [varchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[phon_numb] [varchar](15) COLLATE Modern_Spanish_CI_AS NULL,
[mail_addr] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[fpas_ques] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[fpas_answ] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[job__code] [char](2) COLLATE Modern_Spanish_CI_AS NULL,
[appl_days] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[modi_days] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[out__days] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[true_days] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[mail_chek] [char](1) COLLATE Modern_Spanish_CI_AS NOT NULL CONSTRAINT [DF_MEMB_INFO_mail_chek] DEFAULT ((0)),
[bloc_code] [char](1) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ctl1_code] [char](1) COLLATE Modern_Spanish_CI_AS NOT NULL CONSTRAINT [DF_MEMB_INFO_ctl1_code] DEFAULT ((0)),
[GoblinCoin] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__Gobli__0519C6AF] DEFAULT ((0)),
[GoldChannelTime] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__GoldC__060DEAE8] DEFAULT ((0)),
[MuItemShopGiftList] [varbinary](720) NULL,
[MuItemShopGiftMsg] [varbinary](4995) NULL,
[MuItemShopList] [varbinary](720) NULL,
[SCFExtWarehouse] [tinyint] NOT NULL CONSTRAINT [DF__MEMB_INFO__SCFEx__07020F21] DEFAULT ((0)),
[SCFIsVip] [smallint] NOT NULL CONSTRAINT [DF__MEMB_INFO__SCFIs__07F6335A] DEFAULT ((0)),
[SCFLuckyCoins] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__SCFLu__08EA5793] DEFAULT ((0)),
[SCFVipDays] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__SCFVi__09DE7BCC] DEFAULT ((0)),
[SCFVipMoney] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__SCFVi__0AD2A005] DEFAULT ((0)),
[SCFWareVipCount] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__SCFWa__0BC6C43E] DEFAULT ((0)),
[WCoin] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__WCoin__0CBAE877] DEFAULT ((0)),
[WCoinP] [int] NOT NULL CONSTRAINT [DF__MEMB_INFO__WCoin__0DAF0CB0] DEFAULT ((0)),
[Admin] [tinyint] NOT NULL CONSTRAINT [DF_MEMB_INFO_Admin] DEFAULT ((0)),
[core_admin] [tinyint] NOT NULL CONSTRAINT [DF_MEMB_INFO_core_admin] DEFAULT ((0)),
[activation_id] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[confirmed] [int] NULL,
[Country] [int] NULL,
[Gender] [int] NULL,
[SecretAnswer] [varchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[SecretQuestion] [int] NULL,
[AutorizaVipMoney] [tinyint] NOT NULL CONSTRAINT [DF_MEMB_INFO_AutorizaVipMoney] DEFAULT ((0)),
CONSTRAINT [PK_MEMB_INFO_1] PRIMARY KEY NONCLUSTERED
(
[memb_guid] DESC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [IX_MEMB_INFO_1] ON [dbo].[MEMB_INFO]
(
[memb___id] DESC
)WITH FILLFACTOR = 70 ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MEMB_DETAIL] ON [dbo].[MEMB_INFO]
(
[sno__numb] DESC,
[memb_name] DESC
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[LuckyCoinsRank] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LuckyCoinsRank](
[id] [int] IDENTITY(1,1) NOT NULL,
[Account] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NULL,
[LuckyCoins] [int] NOT NULL CONSTRAINT [DF_LuckyCoinsRank_LuckyCoins] DEFAULT ((0)),
[Date] [smalldatetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[BLOCKING_LOG] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BLOCKING_LOG](
[appl_days] [datetime] NOT NULL,
[admin_guid] [int] NOT NULL,
[block_guid] [int] NOT NULL,
[dist_code] [char](2) COLLATE Modern_Spanish_CI_AS NOT NULL,
[admin_name] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_BLOCKING_LOG] PRIMARY KEY CLUSTERED
(
[appl_days] DESC,
[admin_guid] DESC,
[dist_code] DESC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BLOCKING] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BLOCKING](
[block_guid] [int] IDENTITY(1,1) NOT NULL,
[memb_guid] [int] NOT NULL,
[serv_guid] [int] NOT NULL,
[char_name] [varchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[take_code] [char](1) COLLATE Modern_Spanish_CI_AS NOT NULL,
[take_cont] [varchar](1000) COLLATE Modern_Spanish_CI_AS NOT NULL,
[memb_cont] [varchar](2000) COLLATE Modern_Spanish_CI_AS NULL,
[appl_days] [char](8) COLLATE Modern_Spanish_CI_AS NOT NULL,
[rels_days] [char](8) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ctl1_code] [char](1) COLLATE Modern_Spanish_CI_AS NOT NULL CONSTRAINT [DF_BLOCKING_ctl1_code] DEFAULT ((5)),
CONSTRAINT [PK_BLOCKING] PRIMARY KEY CLUSTERED
(
[block_guid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_BLOCKING] ON [dbo].[BLOCKING]
(
[char_name] DESC,
[take_code] DESC,
[appl_days] DESC,
[rels_days] DESC
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[WZ_DISCONNECT_MEMB] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WZ_DISCONNECT_MEMB]
@memb___id varchar(10)
AS
Begin
set nocount on
Declare @find_id varchar(10)
Declare @ConnectStat tinyint
Declare @FechaIngreso datetime
Declare @FechaEgreso datetime
Set @ConnectStat = 0 -- Fija ConnectStat a 0
Set @find_id = 'NOT'
select @find_id = S.memb___id from MEMB_STAT S INNER JOIN MEMB_INFO I ON S.memb___id = I.memb___id
where I.memb___id = @memb___id
if( @find_id <> 'NOT' ) -- Realizado por TheGhost -- CP Team Development Group
begin
update MEMB_STAT set ConnectStat = @ConnectStat, DisConnectTM = getdate()
where memb___id = @memb___id
select @FechaIngreso = (select ConnectTM from memb_stat where memb___id = @memb___id)
select @FechaEgreso = (select DisConnectTM from memb_stat where memb___id = @memb___id)
update MEMB_STAT set OnlineHours = OnlineHours + (DATEDIFF(MINUTE, @FechaIngreso, @FechaEgreso))
where memb___id = @memb___id
end
end
GO
/****** Object: StoredProcedure [dbo].[WZ_CONNECT_MEMB] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WZ_CONNECT_MEMB]
@uid varchar(10),
@server varchar(20),
@uip varchar(20)
AS
Begin
BEGIN TRANSACTION
SET NOCOUNT ON
IF EXISTS ( SELECT memb___id FROM MEMB_STAT WITH (READUNCOMMITTED)
WHERE memb___id = @uid )
Begin
UPDATE MEMB_STAT
SET ip = @uip , connectstat = 1 , servername = @server , ConnectTM = (getdate())
WHERE memb___id = @uid
End
ELSE
Begin
INSERT INTO MEMB_STAT ( memb___id,ip,connectstat,servername,OnlineHours) valueS (@uid,@uip,1,@server,0)
End
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
/****** Object: StoredProcedure [dbo].[USP_Block_CanCel] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Objeto: procedimiento almacenado dbo.USP_Block_CanCel fecha de la secuencia de comandos: 17/03/2013 08:16:21 p.m. ******/
--=======================================================================--
-- ½Ã ½º ÅÛ : ºí·°ÇØÁ¦
-- ÇÁ·ÎÁ§Æ® : ME_Muonline
-- ÇÔ ¼ö ¸í : USP_Block_CanCel
-- ³» ¿ë : ºí·°°Á¦ ÇØÁ¦
-- ¹Ý ȯ °ª :
-- ÀÛ ¼º ÀÚ : Ãß¼÷
-- ¼ö Á¤ ÀÏ : 2002³â 09¿ù 03ÀÏ
--=======================================================================--
CREATE PROCEDURE [dbo].[USP_Block_CanCel]
(
-- »ç¿ëÀÚ ¸Å°³º¯¼ö¼±¾ð
--========================================================================--
@block_guid int ,
@memb_guid int,
@rels_days char(8),
@hand_meth varchar(1000),
@func_guid int,
@func_name varchar(50),
@Result_Data TINYINT OUTPUT -- ¹ÝȯµÇ´Â »óÅ°ª (½ÇÆÐÄÚµå:9 ¼º°øÄÚµå:1)
--========================================================================-------------------------
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
--========================================================================--=====================================
-- T_Event_Drawing Å×ÀÌºí¿¡¼ ³»¿ë °¡Á®¿À±â
--========================================================================--=====================================
UPDATE BLOCKING
Set ctl1_code = '9',
rels_days = @rels_days ,
take_cont = @hand_meth
WHERE block_guid = @block_guid
UPDATE MEMB_INFO SET bloc_code ='0' WHERE memb_guid = @memb_guid
INSERT INTO BLOCKING_LOG(appl_days, admin_guid, block_guid, dist_code,admin_name)
VALUES(getdate(), @func_guid , @block_guid ,'88',@func_name)
--========================================================================--=====================================
-- ¿¡·¯ÄÚµå ¸®ÅÏÇϱâ 9 : ¿¡·¯ÄÚµå Á¤»óÄÚµå: 1
--========================================================================--=====================================
IF (@@ERROR <> 0)
BEGIN
SET @Result_Data = 9
ROLLBACK TRAN
END
ELSE
BEGIN
SET @Result_Data = 1
COMMIT TRAN
END
--========================================================================--=====================================
SET NOCOUNT OFF
--========================================================================--=====================================
END
GO
/****** Object: Table [dbo].[MEMB_DETA] Script Date: 04/10/2014 21:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MEMB_DETA](
[memb_guid] [int] NOT NULL,
[desc_text] [varchar](5000) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_MEMB_DETA_MEMB_INFO] Script Date: 04/10/2014 21:57:11 ******/
ALTER TABLE [dbo].[MEMB_DETA] WITH CHECK ADD CONSTRAINT [FK_MEMB_DETA_MEMB_INFO] FOREIGN KEY([memb_guid])
REFERENCES [dbo].[MEMB_INFO] ([memb_guid])
GO
ALTER TABLE [dbo].[MEMB_DETA] CHECK CONSTRAINT [FK_MEMB_DETA_MEMB_INFO]
GO
