ازاي ادي Ban IP

[Howdare]

??

[Hero]

Ban account
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason = 'Ban Reason Here'
SET @Begin = '2012-05-01 00:00:00.000' -- Time Begin --
SET @End = '2040-01-01 00:00:00.000' -- Time End --
---------------------------------
---------------------------

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
-----------------------------------------------------------------
ban IP By CharName
How to Ban IP By CharName :D


Credits to @Caipi

My Query to Ban Player


PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason = 'Ban Reason Here'
SET @Begin = '2012-05-01 00:00:00.000' -- Time Begin --
SET @End = '2040-01-01 00:00:00.000' -- Time End --
---------------------------------
---------------------------

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
-----------------------------------------------------------------
--By Leandro ( nukertube ) --
Querry to Unban Player

PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
--------------------------------
USE [SRO_VT_ACCOUNT]
--------------------------------
DELETE FROM [dbo].[_BlockedUser]
WHERE UserJID = @AccJID
DELETE FROM [dbo].[_Punishment]
WHERE UserJID = @AccJID

--By Leandro ( nukertube ) --

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
-----------------------------------------------------------------
(Querry to ban and Unban by me! +1 Like :D)

Credits to @Caipi ->

query to create table to store the ip of the players

PHP Code:
USE Log_DB

CREATE TABLE _IPLogs (

[No.] int IDENTITY(1,1) PRIMARY KEY,
[CharID] int,
[Charname] varchar(max),
[IP] varchar(max),
[Date] datetime
);
stored product to record the ip of the players at the table _IPlogs

PHP Code:
USE [Log_DB]
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

END

Okay, now the ip will be archived and it will be possible to display the name of the char
EDITED
--> Only if the _IPLogs table doesn't contain already IP's of the Char you want to ban


PHP Code:
USE [Log_DB]

Declare @Charname varchar(max);

SET @Charname = 'CharNameHere'

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
Now the table where the IP will be blocked

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

Now the stored product to block all accounts that connect the blocked ip

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
-----------------------------------------------------------------


Last stored product


PHP Code:
USE [Log_DB]
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




After Run all tables and stored products, to ban an ip you just run the query to find the ip by the name of the character, get the ip, and paste the table _bannedips (in log_DB)


Plz THX for me and Caipi[/FONT][/COLOR][/SIZE]

[Hero]

Ban account
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason = 'Ban Reason Here'
SET @Begin = '2012-05-01 00:00:00.000' -- Time Begin --
SET @End = '2040-01-01 00:00:00.000' -- Time End --
---------------------------------
---------------------------

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
-----------------------------------------------------------------
ban IP By CharName
How to Ban IP By CharName :D


Credits to @Caipi

My Query to Ban Player


PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason = 'Ban Reason Here'
SET @Begin = '2012-05-01 00:00:00.000' -- Time Begin --
SET @End = '2040-01-01 00:00:00.000' -- Time End --
---------------------------------
---------------------------

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
-----------------------------------------------------------------
--By Leandro ( nukertube ) --
Querry to Unban Player

PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
--------------------------------
USE [SRO_VT_ACCOUNT]
--------------------------------
DELETE FROM [dbo].[_BlockedUser]
WHERE UserJID = @AccJID
DELETE FROM [dbo].[_Punishment]
WHERE UserJID = @AccJID

--By Leandro ( nukertube ) --

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
-----------------------------------------------------------------
(Querry to ban and Unban by me! +1 Like :D)

Credits to @Caipi ->

query to create table to store the ip of the players

PHP Code:
USE Log_DB

CREATE TABLE _IPLogs (

[No.] int IDENTITY(1,1) PRIMARY KEY,
[CharID] int,
[Charname] varchar(max),
[IP] varchar(max),
[Date] datetime
);
stored product to record the ip of the players at the table _IPlogs

PHP Code:
USE [Log_DB]
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

END

Okay, now the ip will be archived and it will be possible to display the name of the char
EDITED
--> Only if the _IPLogs table doesn't contain already IP's of the Char you want to ban


PHP Code:
USE [Log_DB]

Declare @Charname varchar(max);

SET @Charname = 'CharNameHere'

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
Now the table where the IP will be blocked

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

Now the stored product to block all accounts that connect the blocked ip

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
-----------------------------------------------------------------


Last stored product


PHP Code:
USE [Log_DB]
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




After Run all tables and stored products, to ban an ip you just run the query to find the ip by the name of the character, get the ip, and paste the table _bannedips (in log_DB)


Plz THX for me and Caipi[/FONT][/COLOR][/SIZE]

[moatazsayed]

Ban account
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason = 'Ban Reason Here'
SET @Begin = '2012-05-01 00:00:00.000' -- Time Begin --
SET @End = '2040-01-01 00:00:00.000' -- Time End --
---------------------------------
---------------------------

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
-----------------------------------------------------------------
ban IP By CharName
How to Ban IP By CharName :D


Credits to @Caipi

My Query to Ban Player


PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason = 'Ban Reason Here'
SET @Begin = '2012-05-01 00:00:00.000' -- Time Begin --
SET @End = '2040-01-01 00:00:00.000' -- Time End --
---------------------------------
---------------------------

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
-----------------------------------------------------------------
--By Leandro ( nukertube ) --
Querry to Unban Player

PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]
GO
--------------------------------
DECLARE @ChrID INT;
DECLARE @CharName VARCHAR(64);
DECLARE @AccJID INT;
---------------------------------
SET @CharName = 'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
--------------------------------
USE [SRO_VT_ACCOUNT]
--------------------------------
DELETE FROM [dbo].[_BlockedUser]
WHERE UserJID = @AccJID
DELETE FROM [dbo].[_Punishment]
WHERE UserJID = @AccJID

--By Leandro ( nukertube ) --

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
-----------------------------------------------------------------
(Querry to ban and Unban by me! +1 Like :D)

Credits to @Caipi ->

query to create table to store the ip of the players

PHP Code:
USE Log_DB

CREATE TABLE _IPLogs (

[No.] int IDENTITY(1,1) PRIMARY KEY,
[CharID] int,
[Charname] varchar(max),
[IP] varchar(max),
[Date] datetime
);
stored product to record the ip of the players at the table _IPlogs

PHP Code:
USE [Log_DB]
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

END

Okay, now the ip will be archived and it will be possible to display the name of the char
EDITED
--> Only if the _IPLogs table doesn't contain already IP's of the Char you want to ban


PHP Code:
USE [Log_DB]

Declare @Charname varchar(max);

SET @Charname = 'CharNameHere'

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
Now the table where the IP will be blocked

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

Now the stored product to block all accounts that connect the blocked ip

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
-----------------------------------------------------------------


Last stored product


PHP Code:
USE [Log_DB]
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




After Run all tables and stored products, to ban an ip you just run the query to find the ip by the name of the character, get the ip, and paste the table _bannedips (in log_DB)


Plz THX for me and Caipi[/FONT][/COLOR][/SIZE]

طب ازاي بقي اعرف اني واخد بان اي بي ولا لاء

[محمد منتصر]

##