How to Ban Player By CharName (100% Work)

PHP Code:

---

USE SRO_VT_SHARDLOG

Declare @Charname varchar(max);

SET @Charname = 'RuN0rDiE'

SELECT Data2 INTO #temp_table FROM _LogEventChar 
WHERE CharID = (SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharName16 like @Charname) AND (EventID = 4 OR EventID = 6)
GROUP BY Data2
ORDER BY Data2 asc

Declare @Counter int = (SELECT MIN(Data2) FROM #temp_table), @IP varchar(max);

CREATE TABLE #temp_table_result (
[IP] varchar(max)
);

WHILE (@Counter <= (SELECT MAX(Data2) FROM #temp_table))
    BEGIN
        exec @IP = SRO_VT_ACCOUNT.dbo.split_ip @Counter
        INSERT INTO #temp_table_result SELECT @IP
        SET @Counter = (SELECT MIN(Data2) FROM #temp_table WHERE Data2 > @Counter)
    END

SELECT * FROM #temp_table_result

DROP TABLE #temp_table
DROP TABLE #temp_table_result 


---

--> will output all the used IP's of that Character (Only if it wasn't logged into the new _IPLogs table so far)


_BannedIPs table:

PHP Code:

---

CREATE TABLE _BannedIPs (
[No.] int PRIMARY KEY IDENTITY (1,1),
[IP] varchar(max) NOT NULL
); 


---

Your Query as a SP:

PHP Code:

---

USE [SRO_VT_ACCOUNT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_BannPlayerSP]         

@CharName varchar(max),
@Reason varchar(max)

as
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @AccJID INT;           
---------------------------------

SET @ChrID=(SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM SRO_VT_SHARD.dbo._User WHERE CharID=@ChrID)

----------------------

DECLARE 
        @ID VARCHAR(64) = (Select STRuserID FROM TB_User Where JID = @AccJID),
        @Begin DATETIME = GETDATE()-1,
        @End DATETIME = GETDATE()+3650


INSERT [dbo].[_Punishment]
(
[UserJID],
[Type],
[Executor],
[Shard],
[CharName],
[CharInfo],
[PosInfo] ,
[Guide],
[Description],
[RaiseTime],
[BlockStartTime],
[BlockEndTime],
[PunishTime],
[Status]
)
VALUES 
(
@AccJID,
1,
1,
0,
@CharName,
1,
1,
@Reason,
@Reason,
@Begin,
@Begin,
@End,
@End,
0);
----------------------------------------------------------
DECLARE @ReasonID INT
SET @ReasonID =
(Select SerialNo FROM _Punishment Where UserJID = @AccJID)
----------------------------------------------------------
INSERT [dbo].[_BlockedUser]
(
[UserJID],
[UserID],
[Type],
[SerialNo],
[TimeBegin],
[TimeEnd])
VALUES
(
@AccJID,
@ID,
1,
@ReasonID,
@Begin,
@End);

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
----------------------------------------------------------------- 


---

The _AddLogChar procedure which you need for the ban stuff:

PHP Code:

---

USE [SRO_VT_SHARDLOG] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 


ALTER   procedure [dbo].[_AddLogChar]  
@CharID        int, 
@EventID        tinyint, 
@Data1        int, 
@Data2        int, 
@strPos        varchar(64), 
@Desc        varchar(128) 
as 

    IF(@EventID = 4 OR @EventID = 6) 
    BEGIN 

    declare @len_pos     int 
    declare @len_desc    int 
    set @len_pos = len(@strPos) 
    set @len_desc = len(@Desc) 
    if (@len_pos > 0 and @len_desc > 0) 
    begin     
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)     
    end 
    else if (@len_pos > 0 and @len_desc = 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos) 
    end 
    else if (@len_pos = 0 and @len_desc > 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc) 
    end 
    else 
    begin 
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2) 
    end 
     
    --For the new IPLog table 
    Declare @DynIP varchar(max); 
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2 
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE()) 
    -- END
    -- Banned IP stuff
    IF exists (SELECT IP FROM _BannedIPs WHERE IP like @DynIP)
    BEGIN
    Declare @Charname varchar(max) = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
    
    exec SRO_VT_ACCOUNT.dbo._BannPlayerSP @Charname,'usage of a banned IP'
    -- END banned IP stuff
    END

    END 


---

Keep in mind to insert the IP you want to be banned in the new Table _BannedIPs located in the SHARDLOG DB

Tanks For You

Work 100% ban IP !!!!

Thanxs to '@Caipi'

Please Tutorial For All

What kind of a tutorial do you mean? ^^ I explained already everything above in my posts

@onthread
there is already a procedure to do so
@Caipi
you're awesome

Ya'll acting like SQL wud be a challenge to script ^^ thanks, my pleasure

Caipi, you have Querry to Disconect Player??

Caipi

Msg 1046, Level 15, State 1, Procedure _AddLogChar, Line 39
Subqueries are not allowed in this context. Only scalar expressions are allowed.


help pls ?

nukertube is my account here

i need this ; (

Quote:

---

Originally Posted by xxNukertube

nukertube is my account here

i need this ; (

---

You Don't Know why u got Banned

i Know ;D

but i'm Sorry

i Need Unban My Old Account ;P