[Release]Custom GM Commands

HoaX. · 09/01/2011, 19:52

Admin Commands:

WICHTIG!!! Alle Befehle müssen im normal Chat geschrieben werden und ohne /.
IMPORTANT!!! All commands must be inserted in normal chat and without /.

List:

addpoints [CharName] [count] - Fügt AP hinzu.
addapall [x] - Fügt x AP zu allen Accounts hinzu.
getpoints - Setzt eigene AP auf 50.000 AP (Nur für GM's)
promote [GM CharName] - Setzt das Admin Level um einen Punkt höher.
demote [GM CharName] - Setzt das Admin Level um einen Punkt niedriger.
tempban [CharName] [count] [m/h/d] - Temporärer ban in minuten/stunden/tage für einen Account. Beispiel: tempban HoaX 3 h (Für 3 Stunden ban)
ban [CharName] - Perm. Ban für einen Account.
fullban [CharName] - IP Range Ban + Ban für alle Accounts mit der selben registrierten IP. (Selbst nach IP erneuerung kann dieser Spieler nicht zum Spiel connecten.)
unban [CharName] - Unban Account.
makegsl [CharName] - Added [GSL] Tag + Erstellt einen GS-Leader der Spieler zu GS machen kann.
removegsl [CharName] - Entfernt den [GSL] Tag und entfernt Rechte zum GS machen.
makegs [CharName] - Added [GS] Tag vor dem Namen (Für Admins und GSL)
removegs [CharName] - Entfernt den [GS] Tag vor dem Namen (Für Admins und GSL)
makegm [CharName] - Added [GM] Tag vor dem Namen und gibt GM Rechte (Status 32)
removegm [CharName] - Entfernt [GM] Tag vor dem Namen und nimmt GM Rechte (Setzt Status auf 0)
makea [CharName] - Added [A] Tag vor dem Namen und gibt GM Rechte (Status 16) Danach ist der promote Befehl erforderlich um das Admin Level zu setzen.
removea [CharName] - Entfernt [A] Tag vor dem Namen und entfernt GM Rechte (Setzt Status auf 0) Danach ist kein demote Befehl erforderlich, da das Admin Level auf 0 gesetzt wird.
reschar [CharName] - Wiederbelebt einen toten Charakter und ändert die spawn Position (Auktions Haus).
delchar [CharName] - Löscht einen Charakter. Gleicher Effect wie UM-Tot.
disableacc [CharName] - Deaktiviert einen Account. Beim einloggen erscheint: "Account does not exist." (Setzt leave=1)
enableacc [CharName] - Aktiviert einen deaktivierten Account. (Setzt leave=0)

All commands above need a relogg or /kick.
Alle Befehle oberhalb benötigen einen relogg oder /kick.

Zusätzlicher Befehl: / additional commands:

report_player [CharName] [Reason] - Für normale Spieler um andere zu reporten.

Installation:

Als erstes fügt ihr in der Tabelle dbo.Chars unter PS_GameData
2 neue Spalten mit dem Namen GM und GS hinzu. Dazu verwendet ihr den Datentypen Tinyint.

Create two new colums with datatype tinyint in your PS_GameData Database in table dbo.Chars.

Command Trigger: (Hier zu muss ich sagen, dass es besser ist, den Inhalt in die Insert_Chat_Log Prozedur zu schreiben.)
Command Trigger: You can use this trigger. However it's better to insert this in your Insert_Chat_Log procedure.

Code:

USE [PS_ChatLog]
GO
/****** Object:  Trigger [dbo].[Commands]    Script Date: 09/01/2011 17:48:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Commands]
ON [PS_ChatLog].[dbo].[ChatLog]
FOR INSERT
AS


-- ********* SHAIYA ALLIANCE ********* --
-- By [Dev]HoaX (c)2011

DECLARE
@ChatData varchar(128),
@Char varchar(60),
@row int,
@ChatTime datetime, 
@UserUID int,
@UserID varchar(30),
@Admin bit,
@AdminLevel int,
@Status smallint,
@CharID int,
@CharName varchar(30),
@Family tinyint,
@GM tinyint,
@GS tinyint,
@TargetUserUID int,
@TargetUserID varchar(30),
@T_CharID int,
@T_GS tinyint,
@TargetFamily tinyint,
@TargetStatus smallint,
@TargetAdmin bit,
@TargetAdminLevel int,
@dead bit,
@PosX real,
@PosY real,
@PosZ real,
@MapID tinyint,
@user varchar(20),
@IP varchar(17),
@BlockENDDate datetime,
@bantime smallint,
@mhd char(1),
@point int

SELECT @ChatData = ChatData FROM inserted WHERE ChatType = 1


-- Report_player
IF (@ChatData like 'report_player %')
BEGIN
    SET @ChatData = substring(@ChatData,15,128)
    SET @Char = substring(@ChatData,CHARINDEX(' ', @ChatData)+1,110) 
    SET @ChatData = substring(@ChatData,1,CHARINDEX(' ', @ChatData)-1) 
    
    SET @Row=        (SELECT TOP 1 Row        FROM inserted)
    SET @ChatTime=    (SELECT TOP 1 ChatTime    FROM inserted)
    SET @MapID=        (SELECT TOP 1 MapID        FROM inserted)
    SET @UserUID=    (SELECT TOP 1 UserUID    FROM inserted)
    SET @CharID=    (SELECT TOP 1 CharID    FROM inserted)
    SET @UserID=    (SELECT UserID            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @CharName=    (SELECT CharName        FROM PS_GameData.dbo.Chars WHERE CharID = @CharID)
    SET @PosZ=        (SELECT Posz CharName    FROM PS_GameData.dbo.Chars WHERE CharID = @CharID)
    SET @PosX=        (SELECT PosX CharName    FROM PS_GameData.dbo.Chars WHERE CharID = @CharID)

    INSERT INTO PS_ChatLog.dbo.Reported_Players 
        (UserUID,UserID,CharID,CharName,Map,PosX,PosZ,[Reported Character],Time,Reason) 
    VALUES
        (@UserUID,@UserID,@CharID,@CharName,@MapID,@PosX,@PosZ,@ChatData,@ChatTime,@Char)
    DELETE FROM dbo.ChatLog WHERE Row = @Row
END



-- Adde AP zu allen Accounts...
IF (@ChatData like 'addapall %')
BEGIN
    SET @UserUID =       (SELECT TOP 1 UserUID     FROM inserted WHERE ChatData like 'addapall %')
    SET @Admin =        (SELECT TOP 1 [Admin]     FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT TOP 1 AdminLevel FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @ChatData = substring(@ChatData,10,20)
        SET @point = CONVERT(INT,@ChatData)
        UPDATE PS_UserData.dbo.Users_Master
        SET point = point+@point
    END
END


-- Promote GM - Target muss GM sein und kann ein Admin level von über 9 nicht erreichen...
IF (@ChatData like 'promote %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel=    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,9,29)

        SET @TargetUserUID =    (SELECT TOP 1 UserUID    FROM PS_GameData.dbo.Chars            WHERE Charname = @Char)
        SET @TargetAdmin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        SET @TargetAdminLevel = (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        SET @TargetStatus =        (SELECT [Status]        FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        
        -- Überprüfen ob GM Rechte vorhanden sind...
        IF ((@TargetStatus = 16) OR (@TargetStatus = 32) OR (@TargetStatus = 48))
        BEGIN
        -- Kann ein Admin level von über 9 nicht erreichen...    Nur über DB änderbar.
            IF (@TargetAdminLevel < 9)
            BEGIN
                UPDATE PS_UserData.dbo.Users_Master SET AdminLevel = (@TargetAdminLevel+1) WHERE UserUID = @TargetUserUID
            END                    
        END
    END
END



-- Demote GM - Target muss GM sein und kann ein Admin Level unter 0 nicht erreichen...
IF (@ChatData like 'demote %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,8,25)
        SET @TargetUserUID =    (SELECT TOP 1 UserUID    FROM PS_GameData.dbo.Chars            WHERE CharName = @Char)
        SET @TargetAdmin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        SET @TargetAdminLevel = (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        SET @TargetStatus =        (SELECT [Status]        FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        
        IF ((@TargetStatus = 16) OR (@TargetStatus = 32) OR (@TargetStatus = 48))
        BEGIN
            IF (@TargetAdminLevel > 0)
            BEGIN
                SET @TargetAdminLevel = (@TargetAdminLevel-1)
                UPDATE PS_UserData.dbo.Users_Master SET AdminLevel = @TargetAdminLevel WHERE UserUID = @TargetUserUID
            END                    
        END
    END
END



-- Full ban command für Admins. Ban für alle Accounts mit gleicher IP + IP Range ban.
IF (@ChatData like 'fullban %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @UserID =        (SELECT UserID            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,9,25)
        SET @TargetUserUID =    (SELECT TOP 1 UserUID    FROM PS_GameData.dbo.Chars            WHERE Charname = @Char)
        SET @TargetUserID =        (SELECT UserID            FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        SET @IP =                (SELECT UserIP            FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @TargetUserUID)
        
        UPDATE PS_UserData.dbo.Users_Master
        SET Status = -5 
        WHERE UserIP = @IP
        
        INSERT INTO [PS_UserData].[dbo].[Users_BlockIpRange]
                (IP,[Time],Reason)
        VALUES    (@IP,getdate(),'Banned by Admin Account: '+@UserID)
        
                INSERT INTO [PS_UserData].[dbo].[Users_BlockLog]
                (UserUID,[Status],BlockENDDate,GmID,InsertDate)
         VALUES    (@TargetUserUID , -9 , '' , @UserID, getdate() )
    END
END



-- unban command
IF (@ChatData like 'unban %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID     FROM inserted)
    SET @Admin =        (SELECT TOP 1 [Admin]     FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel=    (SELECT TOP 1 AdminLevel FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
            
    IF ((@Admin = 1) AND (@AdminLevel >= 9))
    BEGIN
        SET @Char = substring(@ChatData,7,25)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE CharName = @Char)
            
        DELETE FROM PS_UserData.dbo.Users_Block WHERE UserUID = @TargetUserUID 
        UPDATE PS_UserData.dbo.Users_Master
        SET status = 0
        WHERE UserUID = @TargetUserUID
    END
END



-- Make [GSL] (GS-Leader)
IF (@ChatData like 'makegsl %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)

    IF (@AdminLevel >= 9)
    BEGIN
        SET @Char = substring(@ChatData,9,25)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE CharName = @Char)
        UPDATE PS_GameData.dbo.Chars SET GS = 10                    WHERE CharName = @Char
        UPDATE PS_GameData.dbo.Chars SET CharName = '[GSL]'+@Char    WHERE CharName = @Char
    END
END



-- Remove [GSL] (GS-Leader)
IF (@ChatData like 'removegsl %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    SET @Char = substring(@ChatData,16,30)
    SET @T_GS = (SELECT TOP 1 GS FROM PS_GameData.dbo.Chars WHERE CharName = '[GSL]'+@Char)

    IF (@AdminLevel >= 10 AND @T_GS >= 3 )
    BEGIN
        -- Löschen von 'message to server' aus CharInventar und W-Lager.
        SET @T_CharID = (SELECT TOP 1 CharID    FROM PS_GameData.dbo.Chars WHERE CharName = '[GSL]'+@Char AND del = 0)
        SET @UserUID =    (SELECT TOP 1 UserUID    FROM PS_GameData.dbo.Chars WHERE CharName = '[GSL]'+@Char AND del = 0)
        DELETE FROM PS_GameData.dbo.CharItems        WHERE ItemID = 100064 AND CharID    = @T_CharID
        DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemID = 100064 AND UserUID    = @UserUID
        
        UPDATE PS_GameData.dbo.Chars SET GS = 0                WHERE CharName = '[GSL]'+@Char
        UPDATE PS_GameData.dbo.Chars SET CharName = @Char    WHERE CharName = '[GSL]'+@Char
    END
END



-- Make [GS]
IF (@ChatData like 'makegs %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @CharID =        (SELECT TOP 1 CharID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @UserUID)
    SET @Family =        (SELECT Family            FROM PS_GameData.dbo.Chars            WHERE CharID = @CharID)
    SET @GS =            (SELECT GS                FROM PS_GameData.dbo.Chars            WHERE CharID = @CharID)
            
    IF ((@Admin = 1 AND @AdminLevel >= 9) OR (@GS >= 3))
    BEGIN
        SET @Char = substring(@ChatData,8,20)
        SET @TargetFamily = (SELECT TOP 1 Family FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
        
        IF ((@Family <= 1) AND (@TargetFamily <= 1) OR (@Family >= 2) AND (@TargetFamily >= 2) OR (@AdminLevel >= 9)) 
        BEGIN
            SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
            UPDATE PS_GameData.dbo.Chars SET GS = 1                        WHERE CharName = @Char
            UPDATE PS_GameData.dbo.Chars SET CharName = '[GS]'+@Char    WHERE CharName = @Char
        END
    END
END



-- Remove [GS]
IF (@ChatData like 'removegs %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @CharID =        (SELECT TOP 1 CharID    FROM inserted)
    SET @Admin =        (SELECT [Admin]        FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel    FROM PS_UserData.dbo.Users_Master    WHERE UserUID = @UserUID)
    SET @GS =            (SELECT GS            FROM PS_GameData.dbo.Chars            WHERE CharID = @CharID)
    
    SET @Char = substring(@ChatData,14,30)
    SET @T_GS = (SELECT TOP 1 GS FROM PS_GameData.dbo.Chars WHERE CharName = '[GS]'+@Char)
    
    IF ((@GS >= 3 AND @T_GS = 1) OR (@Admin = 1 AND @AdminLevel >= 9 AND @T_GS = 1)) 
    BEGIN
        -- Löschen von 'message to server' aus CharInventar und W-Lager
        SET @T_CharID = (SELECT TOP 1 CharID    FROM PS_GameData.dbo.Chars WHERE CharName = '[GS]'+@Char AND del = 0)
        SET @UserUID =    (SELECT TOP 1 UserUID    FROM PS_GameData.dbo.Chars WHERE CharName = '[GS]'+@Char AND del = 0)
        DELETE FROM PS_GameData.dbo.CharItems        WHERE ItemID = 100064 AND CharID = @T_CharID
        DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemID = 100064 AND UserUID = @UserUID
            
        -- GS Status und Tag entfernen --    
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
        UPDATE PS_GameData.dbo.Chars SET GS = 0                WHERE CharName = '[GS]'+@Char
        UPDATE PS_GameData.dbo.Chars SET CharName = @Char    WHERE CharName = '[GS]'+@Char
    END
END



-- Make [GM]
IF (@ChatData like 'makegm %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,8,29)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars    WHERE Charname = @Char)
        UPDATE PS_GameData.dbo.Chars        SET GM = 1                            WHERE CharName = @Char
        UPDATE PS_GameData.dbo.Chars        SET CharName = '[GM]'+@Char            WHERE CharName = @Char
        UPDATE PS_UserData.dbo.Users_Master SET [Status] = 32                    WHERE UserUID = @TargetUserUID
    END
END



-- Remove [GM]
IF (@ChatData like 'removegm %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,14,34)
        UPDATE PS_GameData.dbo.Chars        SET GM = 0                WHERE CharName = '[GM]'+@Char
        UPDATE PS_GameData.dbo.Chars        SET CharName = @Char    WHERE CharName = '[GM]'+@Char
        UPDATE PS_UserData.dbo.Users_Master SET [Status] = 0            WHERE UserUID = @TargetUserUID
    END
END



-- Make [A]
IF (@ChatData like 'makea %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,7,25)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
        UPDATE PS_GameData.dbo.Chars SET GM = 1                    WHERE CharName = @Char
        UPDATE PS_GameData.dbo.Chars SET CharName = '[A]'+@Char WHERE CharName = @Char
        UPDATE PS_UserData.dbo.Users_Master SET [Status] = 16    WHERE UserUID = @TargetUserUID
    END
END



-- Remove [A]
IF (@ChatData like 'removea %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 10))
    BEGIN
        SET @Char = substring(@ChatData,13,30)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
        UPDATE PS_GameData.dbo.Chars        SET GM = 0             WHERE CharName = '[A]'+@Char
        UPDATE PS_GameData.dbo.Chars        SET CharName = @Char WHERE CharName = '[A]'+@Char
        UPDATE PS_UserData.dbo.Users_Master SET [Status] = 0     WHERE UserUID = @TargetUserUID
        UPDATE PS_UserData.dbo.Users_Master SET [Admin] = 0         WHERE UserUID = @TargetUserUID
        UPDATE PS_UserData.dbo.Users_Master SET AdminLevel = 0     WHERE UserUID = @TargetUserUID
    END
END


-- Reschar
IF (@ChatData like 'reschar %')
BEGIN
    SET @UserUID =        (SELECT UserUID        FROM inserted WHERE ChatData like 'reschar %')
    SET @Admin =        (SELECT [Admin]        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel    FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 2))
    BEGIN
        SET @Char = substring(@ChatData,9,25)
        SET @dead = (SELECT TOP 1 Del FROM PS_GameData.dbo.Chars WHERE CharName = @Char) 
        IF (@dead = 1)
        BEGIN
            UPDATE PS_GameData.dbo.Chars SET Del = 0    WHERE CharName = @Char
            UPDATE PS_GameData.dbo.Chars SET Map = 42    WHERE CharName = @Char
            UPDATE PS_GameData.dbo.Chars SET PosX = 50    WHERE CharName = @Char
            UPDATE PS_GameData.dbo.Chars SET PosY = 2    WHERE CharName = @Char            
            UPDATE PS_GameData.dbo.Chars SET Posz = 54    WHERE CharName = @Char
        END
    END
END


-- delete character
IF (@ChatData like 'delchar %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 5))
    BEGIN
        SET @Char = substring(@ChatData,9,26)
        UPDATE PS_GameData.dbo.Chars SET Del = 1 WHERE CharName = @Char
    END
END


-- disableacc (Account does not exist)
IF (@ChatData like 'disableacc %')
BEGIN 
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 9))
    BEGIN
        SET @Char = substring(@ChatData,12,30)
        SET @TargetUserUID = (SELECT UserUID FROM PS_GameData.dbo.Chars WHERE CharName = @Char)
        UPDATE PS_UserData.dbo.Users_Master
        SET leave = 1
        WHERE UserUID = @TargetUserUID
    END
END


-- enableacc
IF (@ChatData like 'enableacc %')
BEGIN
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF (@AdminLevel >= 9)
    BEGIN
        SET @char = substring(@ChatData,11,30)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE Charname = @Char)
        UPDATE [PS_UserData].[dbo].[Users_Master]
        SET leave = 0
        WHERE UserUID = @TargetUserUID
    END
END


-- Ban command für GM
IF (@ChatData like 'ban %')
BEGIN
    SET @UserUID =    (SELECT TOP 1 UserUID    FROM inserted)
    SET @Status =    (SELECT [Status]        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Status = 16) OR (@Status = 32))
    BEGIN
        SET @Char = substring(@ChatData,5,25)
        SET @TargetUserUID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE CharName = @Char)
        UPDATE PS_UserData.dbo.Users_Master
        SET status = -9
        WHERE UserUID = @TargetUserUID
            
        INSERT INTO [PS_UserData].[dbo].[Users_BlockLog]
                (UserUID,Status,BlockENDDate,GmID,InsertDate)
         VALUES    (@TargetUserUID , -9 , '' , @CharName, getdate() )
    END
END


-- tempBan command für GM
IF (@ChatData like 'tempban %')
BEGIN
    SET @Char = substring(@ChatData,9,30)
    
    SET @bantime = substring(@Char,CHARINDEX(' ', @Char)+1,1) 
    SET @mhd = substring(@ChatData,len(@ChatData),1)
    SET @Char = substring(@Char,1,CHARINDEX(' ', @Char)-1) 
    
    SET @UserUID =    (SELECT TOP 1 UserUID    FROM inserted)
    SET @Status =    (SELECT [Status]        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Status = 16) OR (@Status = 32))
    BEGIN
        SET @CharID =            (SELECT TOP 1 CharID    FROM inserted)
        SET @CharName =            (SELECT CharName        FROM PS_GameData.dbo.Chars WHERE CharID = @CharID)
        SET @TargetUserUID =    (SELECT TOP 1 UserUID    FROM PS_GameData.dbo.Chars WHERE CharName = @Char AND del = 0)

        -- Default ban by day.
        SET @BlockENDDate = DateAdd("d", @bantime, getdate())
        IF @mhd = 'm' SET @BlockENDDate = DateAdd("n", @bantime, getdate())
         IF @mhd = 'h' SET @BlockENDDate = DateAdd("hh", @bantime, getdate())
         IF @mhd = 'd' SET @BlockENDDate = DateAdd("d",    @bantime, getdate())
                        
        INSERT INTO [PS_UserData].[dbo].[Users_Block]
                (UserUID, BlockENDDate)
        VALUES    (@TargetUserUID, @BlockENDDate)
        INSERT INTO [PS_UserData].[dbo].[Users_BlockLog]
                (UserUID,[Status],BlockENDDate,GmID,InsertDate)
        VALUES    (@TargetUserUID , 5 , @BlockENDDate , @CharName, getdate() )

        UPDATE PS_UserData.dbo.Users_Master
        SET status = 5
        WHERE UserUID = @TargetUserUID
    END
END


-- Give AP to player 
IF (@ChatData like 'addpoints %')
BEGIN
    -- Parameter von command entfernen.
    SET @Char = substring(@ChatData,11,40)
    -- Alle vorderen parameter entfernen.
    SET @point = substring(@Char,CHARINDEX(' ', @Char)+1,9) 
    -- hintere parameter entfernen dass nur noch Character übrig bleibt.
    SET @Char = substring(@Char,1,CHARINDEX(' ', @Char)-1) 
    
    SET @UserUID =        (SELECT TOP 1 UserUID    FROM inserted)
    SET @Admin =        (SELECT [Admin]            FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    SET @AdminLevel =    (SELECT AdminLevel        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Admin = 1) AND (@AdminLevel >= 9))
    BEGIN
        SET @TargetUserUID = (SELECT UserUID FROM PS_GameData.dbo.Chars WHERE CharName = @Char)
        UPDATE PS_UserData.dbo.Users_Master
        SET point = point+@point
        WHERE UserUID = @TargetUserUID
    END
END



-- getpoints für GM
IF (@ChatData like 'getpoints')
BEGIN
    SET @UserUID =    (SELECT TOP 1 UserUID    FROM inserted)
    SET @Status =    (SELECT [Status]        FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
    
    IF ((@Status = 16) OR (@Status = 32))
    BEGIN
        SET @point = (SELECT point FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)
        UPDATE PS_UserData.dbo.Users_Master
        SET point = 500000
        WHERE UserUID = @UserUID
    END
END

Diesen Teil in die Prozedur dbo.usp_Try_GameLogin_Taiwan einfügen:
Füge es über der Zeile "-- BlockUser Check" ein.
Insert this part in your dbo.usp_Try_GameLogin_Taiwan:
Insert it one line above "-- BlockUser Check" in.

Code:

     -- Range Ban Script --
DECLARE @IPexist int, @o1 tinyint, @o2 tinyint, @ChkIP varchar(7)
    set @o1 = PARSENAME(@UserIP, 4)
    set @o2 = PARSENAME(@UserIP, 3)
    SET @ChkIP = CONVERT(varchar(3),@o1)+'.'+ CONVERT(varchar(3),@o2)
    SET @IPexist = (SELECT COUNT(IP) FROM PS_UserData.dbo.Users_BlockIpRange WHERE IP like @ChkIP+'%')
    IF (@IPexist <> 0) SET @Status = -5 
    -- -----------------------------------

Für die Reported Players Tabelle:
For reported_players table:

Code:

USE [PS_ChatLog]
GO

/****** Object:  Table [dbo].[Reported_Players]  by [Dev]HoaX  Script Date: 09/01/2011 02:12:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reported_Players](
    [row] [int] IDENTITY(1,1) NOT NULL,
    [UserUID] [int] NOT NULL,
    [UserID] [varchar](30) NOT NULL,
    [CharID] [int] NOT NULL,
    [CharName] [varchar](30) NOT NULL,
    [Map] [smallint] NULL,
    [PosX] [real] NULL,
    [PosZ] [real] NULL,
    [Reported Character] [varchar](30) NOT NULL,
    [Reason] [varchar](210) NOT NULL,
    [Time] [datetime] NOT NULL,
    [Readed] [bit] NULL,
    [GM_notice] [varchar](256) NULL,
 CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED 
(
    [row] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

IP Block tabele:

Code:

USE [PS_UserData]
GO

/****** Object:  Table [dbo].[Users_BlockIpRange]    Script Date: 09/01/2011 05:18:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Users_BlockIpRange](
    [row] [int] IDENTITY(1,1) NOT NULL,
    [IP] [varchar](15) NOT NULL,
    [Time] [datetime] NOT NULL,
    [Reason] [varchar](128) NULL,
 CONSTRAINT [PK_Users_BlockIP] PRIMARY KEY CLUSTERED 
(
    [row] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Any Ideas for new Commands?! Post here!

For questions about changing some commands feel free to contact me.
Für Fragen wie man einige Befehle ändert, stehe ich gerne zur verfügung.

No bugs known yet.
Es sind mir bislang keine Bugs bekannt, also wäre es hilfreich bei Fund diese zu melden.

Zum Script:
Ja zugegeben, das kann schon ein paar kleine Verbesserungen gebrauchen.
Jedoch läuft es bis jetzt ohne Probleme auf unserem Server.

To change the required Admin Level for a command you need to change this line:
IF ((@Admin = 1) AND (@AdminLevel >= 10))

Um das erforderliche Admin Level für einen bestimmten befehl zu ändern, musst du folgende Zeile ändern:
IF ((@Admin = 1) AND (@AdminLevel >= 10))

Das Skript funktioniert bei dir nicht?!
Script don't work for you?!

Quote:

1. Did you set Adminlevel to 255 on your Account in your PS_UserData.dbo.Users_Master?
2. Does your Chatlog work?
3. Check permissions for the script. Is it allowed to change Values in other Databases?
4. Can you find the command that you used in your Chatlog?

If this wasn't helpful, post your answers here.

A "Thanks" won't hurt.

Shaiya Xtreme · 09/02/2011, 17:41

Meldung 156, Ebene 15, Status 1, Prozedur Commands, Zeile 74
Falsche Syntax in der Nähe des 'IF'-Schlüsselwortes.

HoaX. · 09/02/2011, 17:57

Works now.
sorry...simply forgot the "Begin" command in a line.

mickybaker · 09/02/2011, 21:16

Translated the Commands to English hope you dont mind.

[Release] Custom GM Commands

Admin Commands:

IMPORTANT! All orders must be written in the usual chat and not /.
IMPORTANT! All commands must be inserted into normal chat and without /.

List:

. AddPoints [charnames] [count] - Adds AP added.
. addapall [x] - Adds x AP added to all accounts.
. GetPoints - AP sets its own AP to 50,000 (only for GM's)
. promote [GM charnames] - Sets the admin level to a point higher.
. demote [GM charnames] - Sets the admin level to a point lower.
. Tempbans [charnames] [count] [m / h / d] - A temporary ban in minutes / hours / days for an account. Example: Tempbans (ban for 3 hours) 3 hours hoax
. ban [charnames] - Perm. Ban for an account.
. fullban [charnames] - IP Range Ban + Ban for all registered accounts with the same IP. (Self-renewal by IP that player can not connect to the game.)
. unban [charnames] - Unban account.
. makegsl [charnames] - Added [GSL] can make day + Creates a GS Leader of the players to GS.
. removegsl [charnames] - Removes [GSL] day and do away rights to the GS.
. makegs [charnames] - Added [GS] tag before the name (for admins and GSL)
. removegs [charnames] - Removes [GS] tag before the name (for admins and GSL)
. makegm [charnames] - Added [GM] days before the GM's name and rights (status 32)
. removegm [charnames] - Removes [GM] tag at the name and rights takes GM (set status to 0)
. makea [charnames] - Added [A] day before the GM's name and rights (status 16) After the set is required to promote command at the admin level.
. removea [charnames] - Removes [A] day before the name and rights from GM (sets status to 0) then demote command is not needed as the admin level is set to 0.
. reschar [charnames] - revived a dead character and changes the spawn position (Auction House).
. delchar [charnames] - Deletes a character. Same Effect as UM-Tot.
. disableacc [charnames] - Disable an account. When you sign appears: "Account does not exist." (Sits leave = 1)
. enableacc [charnames] - Enables a disabled account. (Sits leave = 0)

Additional command: / additional commands:

. report_player [charnames] [Reason] - reporter for normal players to others.

with Big Thankx to Hoax

sondo81 · 09/02/2011, 23:51

öhm ... vlt stell ich mich ja zu doof an aber seit wann kann man ne prozedur ändern mir ....USE [PS_ChatLog]...... .... Use iss doch allgemein nur für Querys oder seh ich da was falsch?

HoaX. · 09/03/2011, 00:23

Quote:

Originally Posted by sondo81

öhm ... vlt stell ich mich ja zu doof an aber seit wann kann man ne prozedur ändern mir ....USE [PS_ChatLog]...... .... Use iss doch allgemein nur für Querys oder seh ich da was falsch?

USE [PS_ChatLog]

Bedeutet, dass die Datenbank PS_ChatLog für dieses Script verwendet werden soll.

~~kalle801~~ · 09/03/2011, 01:12

Funktioniert 100%. sehr gute Arbeit.

Wir brauchen mehr leute wie dich.

sondo81 · 09/03/2011, 02:11

ich glaub ja das das alles so toll ist jedoch hab ich nach wie vor das problem wenn ich versuche deinen code in die Insert_Chat_Log Prozedur einzufügen ich immer einen fehler bekomme....

stell mich wahrscheinlich total blöd an

alle anderen codes gehn ohne murren.

HoaX. · 09/03/2011, 03:23

Sag das doch gleich, dass du es in die Prozedur einfügen möchtest.
Dazu musst du den oberen Teil weglassen.

Also Folgendes weglassen:

Code:

USE [PS_ChatLog]
GO
/****** Object:  Trigger [dbo].[Commands]    Script Date: 09/01/2011 17:48:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Commands]
ON [PS_ChatLog].[dbo].[ChatLog]
FOR INSERT
AS

Dann würde ich dir noch empfehlen den Teil wo die Variablen deklariert werden zu den vorhandenen in der Prozedur hinzuzufügen.
(Unter "DECLARE")

Da die Variable @ChatData schon in der Prozedur deklariert wurde (@ChatData varchar(128),)
brauchst du diese nicht doppelt hinzufügen.

In dem Bereich wo die Variablen untereinander deklariert werden muss jede am ende der Zeile ein Komma haben, außer in der letzten Zeile.

Code:

@Char varchar(60),
@row int,
@ChatTime datetime, 
@UserUID int,
@UserID varchar(30),
@Admin bit,
@AdminLevel int,
@Status smallint,
@CharID int,
@CharName varchar(30),
@Family tinyint,
@GM tinyint,
@GS tinyint,
@TargetUserUID int,
@TargetUserID varchar(30),
@T_CharID int,
@T_GS tinyint,
@TargetFamily tinyint,
@TargetStatus smallint,
@TargetAdmin bit,
@TargetAdminLevel int,
@dead bit,
@PosX real,
@PosY real,
@PosZ real,
@MapID tinyint,
@user varchar(20),
@IP varchar(17),
@BlockENDDate datetime,
@bantime smallint,
@mhd char(1),
@point int

Natürlich musst du noch darauf achten, dass gleiche Variablen in der Prozedur nicht doppelt deklariert werden.

So sollte es funktionieren, wenn ich nichts vergessen habe.

sondo81 · 09/03/2011, 04:01

bin sehr erfreut das du so schnell stellung nimmst werde es auch testen ... jedoch vlt sind noch welche im irrglauben und verstehn es so wie ich (Command Trigger: (Hier zu muss ich sagen, dass es besser ist, den Inhalt in die Insert_Chat_Log Prozedur zu schreiben.) ) entweder versteh ich es falsch oder da steht ich sollte es in die Prozedur einfügen....

HoaX. · 09/03/2011, 04:18

Es ist nur so, der sql Trigger erkennt wenn der ShaiyaServer Chat-Daten in die Tabelle einfügt und verarbeitet diese anschließend.

Der ShaiyaServer verwendet dazu (zum einfügen der Chat Daten) die bestehende Prozedur Insert_Chat_Log.

Somit würde bei der Verwendung meines Scripts in der Prozedur die Daten sofort verarbeitet werden.

Ein SQL-Trigger verbraucht wiederrum Ressourcen die über den SQL-Server bemerkbar werden können.

Somit wäre die Verwendung eines Triggers unnötig.

Meine Meinung dazu ist aber, dass die Verwendung des Triggers einfacher für die Benutzer ist und die zusätzliche Ressourcen last so gering, dass es kaum einen unterschied macht.

Letztendlich kannst du wählen zwischen Trigger oder Prozedur verwendung.

~~Xx.LaMin.xX~~ · 09/03/2011, 12:12

HoaX.. what i see in your script is everybody can use it? even normal players too??

eQuiNoX. · 09/03/2011, 13:35

Respekt, war mit Sicherheit 'ne menge Arbeit.

Quote:

Originally Posted by HoaX.

/Edit
IP Range Ban Script:
Bei einem IP ban geht der erste Login, danach jedoch wird der Account erst geband. Also erst nach dem einloggen. Jemand eine Idee wie man das fixt?

Sollte gehen, indem du..

Code:

    -- Range Ban Script --
BEGIN
    DECLARE @IPexist int, @o1 tinyint, @o2 tinyint, @ChkIP varchar(7)
    set @o1 = PARSENAME(@UserIP, 4)
    set @o2 = PARSENAME(@UserIP, 3)
    SET @ChkIP = CONVERT(varchar(3),@o1)+'.'+ CONVERT(varchar(3),@o2)
    SET @IPexist = (SELECT COUNT(IP) FROM PS_UserData.dbo.Users_BlockIpRange WHERE @UserIP like @ChkIP+'%')
    
    IF (@IPexist <> 0) UPDATE PS_UserData.dbo.Users_Master    SET [Status] = -5    WHERE UserID = @UserID
END
    -- -----------------------------------

zu..

Code:

    -- Range Ban Script --
BEGIN
    DECLARE @IPexist int, @o1 tinyint, @o2 tinyint, @ChkIP varchar(7)
    set @o1 = PARSENAME(@UserIP, 4)
    set @o2 = PARSENAME(@UserIP, 3)
    SET @ChkIP = CONVERT(varchar(3),@o1)+'.'+ CONVERT(varchar(3),@o2)
    SET @IPexist = (SELECT COUNT(IP) FROM PS_UserData.dbo.Users_BlockIpRange WHERE @UserIP like @ChkIP+'%')
    
    IF (@IPexist <> 0)
	BEGIN
		UPDATE PS_UserData.dbo.Users_Master    SET [Status] = -5    WHERE UserID = @UserID
		SET @Status = -5
	END
END
    -- -----------------------------------

änderst.

Allerdings solltest du erwähnen, wo der Code in der dbo.usp_Try_GameLogin_Taiwan eingefügt werden muss, denn wenn er willkürlich eingefügt wird, könnten IP-gebannte einfach irgendeinen Benutzernamen eingeben und dieser wird dann gebannt, egal ob das Passwort richtig ist, oder nicht.

~~kalle801~~ · 09/03/2011, 14:24

ui das ist heftig.

Wäre es vielleicht auch möglich, bereits in der data.sah gespeicherte Musik mit einem Befehl abzuspielen?

HoaX. · 09/03/2011, 16:26

Quote:

HoaX.. what i see in your script is everybody can use it? even normal players too??

That's why I've added this line:
IF ((@Admin = 1) AND (@AdminLevel >= 10))

You can use it only, when you set your AdminLevel to 10+ and if Admin = True in your dbo.Users_Master

All players can use the report_player function.

Quote:

Originally Posted by eQuiNoX.

Respekt, war mit Sicherheit 'ne menge Arbeit.

Sollte gehen, indem du..zu..

Code:

    -- Range Ban Script --
BEGIN
    DECLARE @IPexist int, @o1 tinyint, @o2 tinyint, @ChkIP varchar(7)
    set @o1 = PARSENAME(@UserIP, 4)
    set @o2 = PARSENAME(@UserIP, 3)
    SET @ChkIP = CONVERT(varchar(3),@o1)+'.'+ CONVERT(varchar(3),@o2)
    SET @IPexist = (SELECT COUNT(IP) FROM PS_UserData.dbo.Users_BlockIpRange WHERE UserIP like @ChkIP+'%')
    
    IF (@IPexist <> 0)
	BEGIN
		UPDATE PS_UserData.dbo.Users_Master    SET [Status] = -5    WHERE UserID = @UserID
		SET @Status = -5
	END
END
    -- -----------------------------------

änderst.

Hatte ich auch schon ausprobiert. Funktioniert aber auch nicht.
Beim ersten versuch loggt man normal ein und erst dann ist der Account geband. Also wenn man sich danach nochmal versucht einzuloggen, dann funktioniert dies erst nicht mehr.
Man könnte vielleicht noch versuchen einen Rückgabewert zu erzeugen der bei der gleichen IP entsteht und einen DC gibt.

Quote:

ui das ist heftig.

Wäre es vielleicht auch möglich, bereits in der data.sah gespeicherte Musik mit einem Befehl abzuspielen?

Das wäre natürlich eine Super Idee für Events o.ä.

Allerdings leider nicht über SQL möglich. Man könnte aber beispielweise einen background Sound für eine bestimmte Map hinzufügen. Diese wird dann bei betreten abgespielt.