[RELEASE]Custom GM commands

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Split]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Split]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
    @value varchar(8000),
    @bcontinue bit,
    @iStrike smallint,
    @iDelimlength tinyint

IF @sDelim = 'Space'
    BEGIN
    SET @sDelim = ' '
    END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
    BEGIN
    WHILE @bcontinue = 1
        BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
        IF CHARINDEX(@sDelim, @sText)>0
            BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            
--Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
        
            END
        ELSE
            BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            --Exit the WHILE loop.
SET @bcontinue = 0
            END
        END
    END
ELSE
    BEGIN
    WHILE @bcontinue=1
        BEGIN
        --If the delimiter is an empty string, check for remaining text
        --instead of a delimiter. Insert the first character into the
        --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
        IF DATALENGTH(@sText)>1
            BEGIN
            SET @value = SUBSTRING(@sText,1,1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
            
            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0    
            END
    END

END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

USE [PS_Chatlog]
GO

/****** Object:  Trigger [dbo].[Commands]    Script Date: 02/12/2011 20:46:10  v0.2******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Commands]
ON [PS_Chatlog].[dbo].[ChatLog]
FOR INSERT
AS

DECLARE
@ChatData varchar(50),
@UserUID varchar(10),
@cmd varchar(10),
@auth smallint


SELECT @ChatData=ChatData,@UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	BEGIN
        SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
        SELECT @cmd=value FROM #tmptable where idx=0
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.users_mASter AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
END

DROP TABLE #tmptable
GO

USE [PS_Chatlog]
GO
/****** Object:  StoredProcedure [dbo].[usp_Insert_Chat_Log_E]    Script Date: 02/14/2011 19:52:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.usp_Insert_Chat_Log_E    Script Date: 2008-6-7 18:30:55 ******/
ALTER     Proc [dbo].[usp_Insert_Chat_Log_E]

/* 
Created by humanws, 2005-10-14
채팅 로그 남기기
 */

@UserUID int,
@CharID int,
@ChatType tinyint,		-- 일반1, 귓말2, 길드3, 파티4, 거래5
@TargetName varchar(30),
@ChatData varchar(128),
@MapID smallint,
@ChatTime datetime

AS

DECLARE @Sql nvarchar(4000)
DECLARE @yyyy varchar(4)
DECLARE @mm varchar(2)
DECLARE @dd varchar(2)

DECLARE @cmd varchar(10)
DECLARE @auth smallint

SET @yyyy = DATEPART(yyyy, @ChatTime)
SET @mm = DATEPART(mm, @ChatTime)
SET @dd = DATEPART(dd, @ChatTime)


IF( LEN(@mm) = 1 )
BEGIN
	SET @mm = '0' + @mm
END

IF( LEN(@dd) = 1 )
BEGIN
	SET @dd = '0' + @dd
END

SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID
IF (@auth = 16)
	BEGIN
	SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
	SELECT @cmd=value FROM #tmptable where idx=0
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.users_mASter AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
	DROP TABLE #tmptable
END

SET @Sql = N'
INSERT INTO PS_ChatLog.dbo.ChatLog
(UserUID, CharID, ChatType, TargetName, ChatData, MapID, ChatTime)
VALUES(@UserUID, @CharID, @ChatType, @TargetName, @ChatData, @MapID, @ChatTime)'

EXEC sp_executesql @Sql, 
N'@UserUID int, @CharID int, @ChatType tinyint, @TargetName varchar(30), @ChatData varchar(128),@MapID smallint, @ChatTime datetime',
@UserUID, @CharID, @ChatType, @TargetName, @ChatData, @MapID, @ChatTime

-- snip --

SELECT @UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	SELECT @ChatData=ChatData from inserted
	SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
	SELECT @cmd=value FROM #tmptable where idx=0
	BEGIN
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.Users_Master AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
	DROP TABLE #tmptable
END

SELECT @ChatData=ChatData,@UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	BEGIN
		DECLARE @CharName varchar(15)
        SELECT @cmd= SUBSTRING(@ChatData,0, CHARINDEX(' ',@ChatData))
		SELECT @CharName = SUBSTRING(@ChatData, len(@cmd)+2, len(@ChatData))

		IF (@cmd='\ban')
		BEGIN	
			UPDATE um SET status=-5 FROM PS_UserData.dbo.users_mASter AS um 
			INNER JOIN PS_GameData.dbo.Chars AS c 
			ON um.UserUID=c.UserUID
			Where c.CharName = @CharName
		END
	END
GO