if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMS_DoingRestraintChar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMS_DoingRestraintChar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USPCheckRestraintChar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USPCheckRestraintChar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USPDeleteRestraintChar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USPDeleteRestraintChar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblChatBanList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblChatBanList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblGMLogMsg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblGMLogMsg]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblGMLogMsg_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblGMLogMsg_Log]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblInterestedUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblInterestedUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblRestraintChar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblRestraintChar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblAdminStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblAdminStore]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblDetailLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblDetailLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblToolAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblToolAdmin]
GO

CREATE TABLE [dbo].[TblChatBanList] (
	[CID] [int] NULL ,
	[ServerGroupID] [tinyint] NULL ,
	[RemainTime] [int] NULL ,
	[AdminCID] [int] NULL ,
	[StartTime] [smalldatetime] NULL ,
	[EndTime] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblGMLogMsg] (
	[LogID] [int] IDENTITY (1, 1) NOT NULL ,
	[UID] [int] NULL ,
	[CID] [int] NULL ,
	[ServerGroupID] [tinyint] NOT NULL ,
	[EditDate] [smalldatetime] NOT NULL ,
	[LogMsg] [varchar] (200)  NULL ,
	[AdminID] [varchar] (20)  NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblGMLogMsg_Log] (
	[LogID] [int] NOT NULL ,
	[UID] [int] NULL ,
	[CID] [int] NULL ,
	[ServerGroupID] [tinyint] NOT NULL ,
	[EditDate] [smalldatetime] NOT NULL ,
	[LogMsg] [varchar] (200)  NULL ,
	[AdminID] [varchar] (20)  NOT NULL ,
	[InsertTime] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblInterestedUser] (
	[strAccount] [varchar] (20) COLLATE NOT NULL ,
	[strReason] [varchar] (260) COLLATE NULL ,
	[intUID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblRestraintChar] (
	[tinyServerID] [tinyint] NOT NULL ,
	[UID] [int] NOT NULL ,
	[strClientID] [varchar] (20) NOT NULL ,
	[strCharName] [varchar] (20) NOT NULL ,
	[tinyType] [tinyint] NOT NULL ,
	[tinyKind] [tinyint] NOT NULL ,
	[tinyBlocked] [tinyint] NOT NULL ,
	[dateStartTime] [smalldatetime] NOT NULL ,
	[dateEndTime] [smalldatetime] NOT NULL ,
	[intEndTime] [int] NOT NULL ,
	[strAdminID] [varchar] (20) NULL ,
	[strDescription] [varchar] (30) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblAdminStore] (
	[uid] [int] IDENTITY (1, 1) NOT NULL ,
	[server] [tinyint] NOT NULL ,
	[owner] [varchar] (20) NOT NULL ,
	[ItemName] [varchar] (32) NOT NULL ,
	[ItemInfo] [binary] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblDetailLog] (
	[DetailLogID] [int] IDENTITY (1, 1) NOT NULL ,
	[LogID] [int] NOT NULL ,
	[UpdateDate] [smalldatetime] NOT NULL ,
	[UpdateType] [int] NULL ,
	[Before] [varchar] (200) NULL ,
	[After] [varchar] (200) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblLog] (
	[UID] [int] NOT NULL ,
	[LogID] [int] IDENTITY (1, 1) NOT NULL ,
	[Login] [smalldatetime] NOT NULL ,
	[Logout] [smalldatetime] NULL ,
	[UseIP] [varchar] (16) NOT NULL ,
	[UseMsg] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblToolAdmin] (
	[UID] [int] IDENTITY (1, 1) NOT NULL ,
	[strAdminID] [varchar] (20) NOT NULL ,
	[strPasswd] [varchar] (20) NOT NULL ,
	[strLevel] [varchar] (2) NOT NULL ,
	[strName] [varchar] (20) NOT NULL ,
	[strIP] [varchar] (16) NOT NULL ,
	[dateRegit] [smalldatetime] NOT NULL ,
	[binZone] [binary] (3400) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblGMLogMsg] WITH NOCHECK ADD 
	CONSTRAINT [PK_TblGMLogMsg] PRIMARY KEY  CLUSTERED 
	(
		[LogID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TblGMLogMsg_Log] WITH NOCHECK ADD 
	CONSTRAINT [PK_TblGMLogMsg_Log] PRIMARY KEY  CLUSTERED 
	(
		[LogID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TblDetailLog] WITH NOCHECK ADD 
	CONSTRAINT [PK_TblDetailLog] PRIMARY KEY  CLUSTERED 
	(
		[DetailLogID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TblLog] WITH NOCHECK ADD 
	CONSTRAINT [PK_TblLog] PRIMARY KEY  CLUSTERED 
	(
		[LogID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TblToolAdmin] WITH NOCHECK ADD 
	CONSTRAINT [PK_TblToolAdmin] PRIMARY KEY  CLUSTERED 
	(
		[strAdminID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [idx_TblChatBanList_cid] ON [dbo].[TblChatBanList]([CID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblGMLogMsg] ADD 
	CONSTRAINT [DF_TblGMLogMsg_UID] DEFAULT (0) FOR [UID],
	CONSTRAINT [DF_TblGMLogMsg_CID] DEFAULT (0) FOR [CID],
	CONSTRAINT [DF_TblGMLogMsg_EditDate] DEFAULT (getdate()) FOR [EditDate]
GO

ALTER TABLE [dbo].[TblGMLogMsg_Log] ADD 
	CONSTRAINT [DF_TblGMLogMsg_Log_UID] DEFAULT (0) FOR [UID],
	CONSTRAINT [DF_TblGMLogMsg_Log_CID] DEFAULT (0) FOR [CID],
	CONSTRAINT [DF_TblGMLogMsg_Log_EditDate] DEFAULT (getdate()) FOR [EditDate],
	CONSTRAINT [DF_TblGMLogMsg_Log_InsertTime] DEFAULT (getdate()) FOR [InsertTime]
GO

ALTER TABLE [dbo].[TblRestraintChar] ADD 
	CONSTRAINT [DF__TblRestra__strCh__11158940] DEFAULT ('ACCOUNT') FOR [strCharName],
	CONSTRAINT [DF__TblRestra__dateS__1209AD79] DEFAULT (getdate()) FOR [dateStartTime],
	CONSTRAINT [DF__TblRestra__intEn__12FDD1B2] DEFAULT (0) FOR [intEndTime]
GO

ALTER TABLE [dbo].[TblDetailLog] ADD 
	CONSTRAINT [DF_TblDetailLog_UpdateDate] DEFAULT (getdate()) FOR [UpdateDate]
GO

ALTER TABLE [dbo].[TblLog] ADD 
	CONSTRAINT [DF_TblLog_Login] DEFAULT (getdate()) FOR [Login]
GO

ALTER TABLE [dbo].[TblToolAdmin] ADD 
	CONSTRAINT [DF_TblToolAdmin_dateRegit] DEFAULT (getdate()) FOR [dateRegit]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


-- 블럭 하기
CREATE  PROC CMS_DoingRestraintChar @ServerID TINYINT,@UID INT, @strClientID VARCHAR(20), @strCharName varchar(16),@Type tinyint,@Kind tinyint,@EndDate varchar(10),
					@AdminID VARCHAR(20),@Description VARCHAR(50)
AS
	SET NOCOUNT ON

	DECLARE @EndDateTime INT

	IF @strCharName IS NULL
	BEGIN
		SET @strCharName = 'Unknown'
	END
	
	SET @EndDateTime = cast(@EndDate as INT)

	INSERT INTO TblRestraintChar (tinyServerID,UID,strClientID,strCharName,tinyType,tinyKind,tinyBlocked,dateEndTime,strAdminID,strDescription)
	VALUES (@ServerID,@UID,@strClientid,@strCharName,@Type,@Kind,1,GETDATE() + @EndDateTime,@AdminID,@Description)

	SELECT @@ROWCOUNT

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


-- 블럭 체크 
CREATE   Proc USPCheckRestraintChar @ServerID Tinyint,@UID INT
AS

	SET NOCOUNT ON

	DECLARE @tinyType TINYINT
	SET @tinyType = 0

	DECLARE @tinyKind TINYINT	-- 1:영구,0:임시
	DECLARE @EndTime INT 
	DECLARE @dateEndTime SMALLDATETIME
	DECLARE @intEndTime INT

	DECLARE @RETURNCHAR1 VARCHAR(20)
	DECLARE @RETURNCHAR2 VARCHAR(20)
	DECLARE @RETURNCHAR3 VARCHAR(20)
	DECLARE @RETURNCHAR4 VARCHAR(20)
	DECLARE @RETURNCHAR5 VARCHAR(20)

	DECLARE @strCharName VARCHAR(20)
	DECLARE @TIME INT

	SELECT @tinyType=tinyType,@tinyKind = tinykind,@EndTime =DATEDIFF(MINUTE,GETDATE(),dateEndTime) 
	FROM TblRestraintChar 
	WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
	ORDER BY tinyType

	IF @tinyType = 0 -- 블럭안함
	BEGIN
		SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
		RETURN
	END
	ELSE IF @tinyType = 1 -- 계정블럭
	BEGIN

		-- 기간여부체크
		IF (@EndTime <= 0 and @tinyKind = 0)  --블럭해제된 상태임
		BEGIN
			UPDATE TblRestraintChar
			SET tinyBlocked = 0
			WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1

			SET @tinyType = 0

			SELECT @tinyType=tinyType,@tinyKind = tinykind,@EndTime =DATEDIFF(MINUTE,GETDATE(),dateEndTime) 
			FROM TblRestraintChar 
			WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
			ORDER BY tinyType
			
			IF @tinyType = 2 --캐릭터블럭
			BEGIN
				SET @strCharName = NULL				
				SET @EndTime = 0
				SET @TIME = 1

				DECLARE Restraint_Cursor_A CURSOR FOR
				SELECT strCharName,DATEDIFF(MINUTE,GETDATE(),dateEndTime),tinyKind
				FROM TblRestraintChar
				WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1		
			
				OPEN Restraint_Cursor_A FETCH NEXT FROM Restraint_Cursor_A INTO @strCharName,@EndTime,@tinyKind
			
				WHILE @@FETCH_STATUS = 0
				BEGIN
					
					IF ((@EndTime > 0) or (@tinyKind = 1))
					BEGIN
		
						IF @TIME = 1
							SET @RETURNCHAR1 = @strCharName
						ELSE IF @TIME = 2
							SET @RETURNCHAR2 = @strCharName
						ELSE IF @TIME = 3
							SET @RETURNCHAR3 = @strCharName
						ELSE IF @TIME = 4
							SET @RETURNCHAR4 = @strCharName
						ELSE IF @TIME = 5
							SET @RETURNCHAR5 = @strCharName
					END
					ELSE
					BEGIN
						
						UPDATE TblRestraintChar
						SET tinyBlocked = 0
						WHERE tinyServerID=@ServerID AND UID = @UID AND strCharName = @strCharName AND tinyBlocked = 1
					END
		
		
					SET @TIME = @TIME + 1
					
			    		FETCH NEXT FROM Restraint_Cursor_A into @strCharName,@EndTime,@tinyKind
			
				END
		
				CLOSE Restraint_Cursor_A
				DEALLOCATE Restraint_Cursor_A
		
				IF ((@RETURNCHAR1 IS NULL) AND (@RETURNCHAR2 IS NULL) AND (@RETURNCHAR3 IS NULL) and (@RETURNCHAR4 IS NULL) and (@RETURNCHAR5 IS NULL))
				BEGIN
					SET @tinyType = 0
				END
		
				SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
				RETURN
			END

			SET @tinyType = 0
			SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
			RETURN
		END
		ELSE
		BEGIN
			SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
			RETURN
		END
	END
	ELSE IF @tinyType = 2 --캐릭터블럭
	BEGIN
		SET @strCharName = NULL
		SET @EndTime = 0
		SET @TIME = 1

		DECLARE Restraint_Cursor CURSOR FOR
		SELECT strCharName,DATEDIFF(MINUTE,GETDATE(),dateEndTime),tinyKind
		FROM TblRestraintChar
		WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1

	
		OPEN Restraint_Cursor FETCH NEXT FROM Restraint_Cursor INTO @strCharName,@EndTime,@tinyKind
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			IF ((@EndTime > 0) or (@tinyKind = 1))
			BEGIN

				IF @TIME = 1
					SET @RETURNCHAR1 = @strCharName
				ELSE IF @TIME = 2
					SET @RETURNCHAR2 = @strCharName
				ELSE IF @TIME = 3
					SET @RETURNCHAR3 = @strCharName
				ELSE IF @TIME = 4
					SET @RETURNCHAR4 = @strCharName
				ELSE IF @TIME = 5
					SET @RETURNCHAR5 = @strCharName
			END
			ELSE
			BEGIN
				
				UPDATE TblRestraintChar
				SET tinyBlocked = 0
				WHERE tinyServerID=@ServerID AND UID = @UID AND strCharName = @strCharName AND tinyBlocked = 1
			END


			SET @TIME = @TIME + 1
			
	    		FETCH NEXT FROM Restraint_Cursor into @strCharName,@EndTime,@tinyKind
	
		END

		CLOSE Restraint_Cursor
		DEALLOCATE Restraint_Cursor

		IF ((@RETURNCHAR1 IS NULL) AND (@RETURNCHAR2 IS NULL) AND (@RETURNCHAR3 IS NULL) and (@RETURNCHAR4 IS NULL) and (@RETURNCHAR5 IS NULL))
		BEGIN
			SET @tinyType = 0
		END

		SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
		RETURN
	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROC USPDeleteRestraintChar @SeverID tinyint,@UID INT,@Charname varchar(16)
AS


	SET NOCOUNT ON

	IF EXISTS (SELECT UID FROM TblRestraintChar WHERE tinyServerID=@SeverID AND UID = @UID AND strCharName = @Charname)
	BEGIN
		DELETE TblRestraintChar 
		WHERE tinyServerID=@SeverID AND UID = @UID AND strCharName = @Charname

		SELECT @@ROWCOUNT
	END
	ELSE
	BEGIN
		SELECT 0
	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO