[Fix] Automated Punishment 10 Minutes!

[Jangan]

This will fix this error:



The Way to do it:

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_RegisterAutomatedPunishment]    Script Date: 10/24/2011 05:46:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[_RegisterAutomatedPunishment]
    @Account    VARCHAR(128),
    @Type          TINYINT,
    @Executor    VARCHAR(128),
    @Guide        VARCHAR(512),
    @Description    VARCHAR(1024),
    @BlockTimeElapse    INT
--    @BlockStartTime    DATETIME,
--    @BlockEndTime    DATETIME
AS
   
    DECLARE @BlockStartTime    VARCHAR(128)
    DECLARE @BlockEndTime    VARCHAR(128)

    SET @BlockStartTime     = getdate()
    SET @BlockEndTime    = dateadd(minute, 10, getdate())
    ------------------------------------------------------------------------------------

    DECLARE @UserJID INT
    SET @UserJID = 0
    DECLARE @NewSerialNo int    
    SET @NewSerialNo = 0
    -- Á¸ÀçÇϴ ij¸¯ÅÍ À̸§ÀÎÁö? JID ¾÷¾î¿ÀÀÚ.
    SELECT @UserJID = JID FROM TB_User WITH (NOLOCK) WHERE strUserID = @Account
    IF @@ERROR <> 0 OR @UserJID = 0
    BEGIN
        SELECT -1
        RETURN
    END

    -- ÀÌ¹Ì ºí·° °É¸° À¯ÀúÀ̳Ä?
    IF( EXISTS ( SELECT UserJID FROM _BlockedUser WITH (NOLOCK) WHERE UserJID = @UserJID AND Type = @Type))
    BEGIN
        select -2
        RETURN    
    END

    
    
    INSERT _Punishment values( @UserJID, @Type, @Executor, 0, '', '', '', @Guide, @Description, @BlockStartTime, @BlockStartTime, @BlockEndTime, @BlockStartTime, 0)
    set @NewSerialNo = @@identity
    if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
    BEGIN
        SELECT -3
        RETURN        
    END

    INSERT _BlockedUser values( @UserJID, @Account, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
    if( @@rowcount = 0 or @@error <> 0 )
    BEGIN
        SELECT -4
        RETURN        
    END

    SELECT @UserJID 

Results:

[MentaL]

[SGA]

works for me thx :)

[LastThief]

good job ;)

[stewie316]

You sir, are a star!

There is also a problem with it actually reading the 100 year bans as well in _RegisterPunishment?

dbo._RegisterPunishment

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- 
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
ALTER procedure [dbo].[_RegisterPunishment]
	@UserJID 	int,
	@Type	  	tinyint,
	@Executor	varchar(128),
	@Shard		smallint,
	@CharName	varchar(16),
	@CharInfo	varchar(256),
	@PosInfo	varchar(64),
	@Guide		varchar(512),
	@Description	varchar(1024),
	@RaiseTime	datetime,
	@BlockStartTime	datetime,
	@BlockEndTime	datetime,
	@PunishTime	datetime
as
	-- 현재 유저가 Blocking중인지 검사해본다
	if( exists( select UserJID from _BlockedUser with(NOLOCK) where UserJID = @UserJID and Type = @Type))
	begin
		select -1	-- 이전 징계가 아직 풀리지 않았다
		RETURN
	end
	declare @NewSerialNo int	
	set @NewSerialNo = 0
	
	insert _Punishment values( @UserJID, @Type, @Executor, @Shard, @CharName, @CharInfo,
				@PosInfo, @Guide, @Description, @RaiseTime, @BlockStartTime, @BlockEndTime, @PunishTime, 0)
	set @NewSerialNo = @@identity
	if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
	begin
		select -2	-- insert하다가 실패 났다
		return
	end
	declare @UserID varchar(128)
	select @UserID = sr_id from sr_member1 where sr_code = @UserJID
	if( @@rowcount = 0 or @@error <> 0 or @UserID is null)
	begin
		select - 2
		return
	end
	-- 새로운 blocking을 넣어준다
	insert _BlockedUser values( @UserJID, @UserID, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
	select 0

I could be looking at the incorrect table but i imagine the table is not set correct as well here.

[Jangan]

give me the consol error and i will trace it ;)

[stewie316]

Under SMC all you get in the SR_UserPunishment is register db is failed along with update db is failed. "db error" so it doesn't block any accounts.

CONSOLE ERROR

Query Error!!! Query Result : -1, Query : EXEC _UpdatePunishment 2476,745531,1,'sro_gm2', 64, '', '', '', 'Blocked for 10 minutes due to incorrect password entered 5 times', 'Blocked for 10 minutes due to incorrect password entered 5 times', '2011-10-24 23:49

EXEC _UpdatePunishment 2476,745531,1,'sro_gm2', 64, '', '', '', test ban for me', 'test ban for me', '2011-10-24 23:49:44', '2011-10-24 23:49:37', '2111-10-24 2

Query Error!!! Query Result : -1, Query : EXEC _RegisterPunishment 745531,2,'sro_gm2',64,'','','','','','2011-10-25 21:37:07','2011-10-25 21:37:05','2111-10-25 21:37:05','2011-10-24 23:19:00'

SQL Error State:42S02, Native Error Code: D0, ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sr_member1'.

I think some requirements are missing in these two tables just as there was for _AutomatedPunishment.

Hope this helps Jangan. Let me know if you need any other errors.

[ItachiSNS]

This will fix this error:



The Way to do it:

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_RegisterAutomatedPunishment]    Script Date: 10/24/2011 05:46:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[_RegisterAutomatedPunishment]
    @Account    VARCHAR(128),
    @Type          TINYINT,
    @Executor    VARCHAR(128),
    @Guide        VARCHAR(512),
    @Description    VARCHAR(1024),
    @BlockTimeElapse    INT
--    @BlockStartTime    DATETIME,
--    @BlockEndTime    DATETIME
AS
   
    DECLARE @BlockStartTime    VARCHAR(128)
    DECLARE @BlockEndTime    VARCHAR(128)

    SET @BlockStartTime     = getdate()
    SET @BlockEndTime    = dateadd(minute, 10, getdate())
    ------------------------------------------------------------------------------------

    DECLARE @UserJID INT
    SET @UserJID = 0
    DECLARE @NewSerialNo int    
    SET @NewSerialNo = 0
    -- Á¸ÀçÇϴ ij¸¯ÅÍ À̸§ÀÎÁö? JID ¾÷¾î¿ÀÀÚ.
    SELECT @UserJID = JID FROM TB_User WITH (NOLOCK) WHERE strUserID = @Account
    IF @@ERROR <> 0 OR @UserJID = 0
    BEGIN
        SELECT -1
        RETURN
    END

    -- ÀÌ¹Ì ºí·° °É¸° À¯ÀúÀ̳Ä?
    IF( EXISTS ( SELECT UserJID FROM _BlockedUser WITH (NOLOCK) WHERE UserJID = @UserJID AND Type = @Type))
    BEGIN
        select -2
        RETURN    
    END

    
    
    INSERT _Punishment values( @UserJID, @Type, @Executor, 0, '', '', '', @Guide, @Description, @BlockStartTime, @BlockStartTime, @BlockEndTime, @BlockStartTime, 0)
    set @NewSerialNo = @@identity
    if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
    BEGIN
        SELECT -3
        RETURN        
    END

    INSERT _BlockedUser values( @UserJID, @Account, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
    if( @@rowcount = 0 or @@error <> 0 )
    BEGIN
        SELECT -4
        RETURN        
    END

    SELECT @UserJID 

Results:

Is this requiring a server restart?

[stewie316]

Is this requiring a server restart?

I've also noticed another issue that is the Auto-ban for incorrect password only works once. If the user was to ever block there account again you end up with this error.

_RegisterAutomatedPunishment Query failed!: [ret: -2, User: sro_gm2]

[Jangan]

oh right... 2 things:

1) About getting banned twice, i forgot that i have system checks that deletes the tables if
Time Now > Punishment Time. on the php site.

I will do another fix release for this and make it more upgraded :P just need to wait for some testers hehe!


@ Stewie316:
and about -1 returns, arrg... how do i get that? what do i need to do to get myself banned for -1 return?

[stewie316]

Query Error!!! Query Result : -1, Query : EXEC _RegisterPunishment 745549,1,'sro_gm2',64,'','','','','','2011-10-26 09:49:21','2011-10-26 09:49:19','2111-10-26 09:49:19','2011-10-26 06:06:08'

SQL Error State:42S02, Native Error Code: D0, ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sr_member1'.

EXEC _RegisterPunishment 745549,1,'sro_gm2',64,'','','','','','2011-10-26 09:49:21','2011-10-26 09:49:19','2111-10-26 09:49:19','2011-10-26 06:06:08'

hmm this is the error i got from doing it in SMC & Above


i get this if i try to duplicate the automated 10 minute ban for incorrect password.

_RegisterAutomatedPunishment Query failed!: [ret: -2, User: sro_gm2

It seems the -1 is coming at the same time as the 1 error. So it seems to be when registering a new ban through SMC which always fails.

Hope this helps.

[stemale]

Some 1 fixd?
_RegisterAutomatedPunishment Query failed!

and the second ban success?

[driver13]

very good

[K4bo]

thx

[nevetS]

Error still showing after put it on my database.