"Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWriter!

[Witchy Moo]

Hey there,

I took this idea from PX2000 (thank you), which was originally posted here and here.

I just "simplifies" it for alternative, no need SR_GameServer modifications, no need separate tables between Job kill and Free PVP kill and key constraints, and everything based on SQL query only.

NEW Updates:
I just read this thread about PlusNotice (NoticeWriter). It's actually interesting. And since the author released the source I recon we can modify it ourselves, hence I made some updates to this "Kill Fetch" SP to so we can make it work with our own modified auto notice system.

Updates are:

• Added 'isSent' and 'strDesc' columns in _LogEventPVP table (those two can be used with the PlusNotice program, but you have to modify the program first (don't ask me how, I'm not a .NET programmer), cause the original executable will look for "PlusNotice" table and "Sent"/"Message" columns)
• I suggest you drop and re-create the table (table code updated below)
• SP modified below, replace it with new one if you already applied this.

NEW updates (December 02, 2013):

• Modified version of NoticeWriter included (see attachment below)
• The SP will now write the Job Nickname instead of real character nickname (updated in the _AddLogChar SP)
• Add notice flag to set notice as "SENT" if it's not send more than 5 minutes. (to avoid notice flood)

Here we go:

Additional table:
First, create table in [SRO_VT_LOG] database to record each kills (which you can use for your website, or whatever you want), I call this table: _LogEventPVP

Spoiler:

USE [SRO_VT_LOG]
GO
DROP TABLE _LogEventPVP
GO
CREATE TABLE _LogEventPVP (
    isSent INT NOT NULL DEFAULT 0,
    CharID INT NOT NULL DEFAULT 0, 
    KilledCharID INT NOT NULL DEFAULT 0, 
    JobType INT NOT NULL DEFAULT 0, 
    EventTime DATETIME NULL, 
    EventLocation VARCHAR(128) NULL,
    strDesc VARCHAR(128) NULL
)
GO

Modification to existing _AddLogChar SP in [SRO_VT_LOG]:

Spoiler:

IF (@EventID = 20) -- PVP
BEGIN
    IF  @desc LIKE '%Trader, Neutral, no freebattle team%'    -- Trader
        OR @desc LIKE '%Hunter, Neutral, no freebattle team%'    -- Hunter
        OR @desc LIKE '%Robber, Neutral, no freebattle team%'    -- Thief
        OR @desc like '%no job, Neutral, %no job, Neutral%'    -- Free PVP
    )
    BEGIN
        -- Get killer name
        DECLARE @killername VARCHAR(512) = @desc
        DECLARE @killeriD INT = 0
        SELECT @killername = REPLACE @killername, LEFT @killername, CHARINDEX('(', @killername)), '')
        SELECT @killername = REPLACE @killername, RIGHT @killername, CHARINDEX(')', REVERSE @killername))), '')
        SELECT @killeriD = CharID FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharName16 = @killername
        -- Get job type
        DECLARE @jobString VARCHAR(10) = LTRIM(RTRIM(SUBSTRING @desc, 5, 7)))
        DECLARE @jobType INT = CASE
            WHEN @jobString LIKE 'Trader' THEN 1
            WHEN @jobString LIKE 'Robber' THEN 2
            WHEN @jobString LIKE 'Hunter' THEN 3
            ELSE 0 END
        -- Delete original log
        DELETE FROM _LogEventChar WHERE CharID = @CharID AND EventID = 20
            AND (strDesc LIKE '%Trader, Neutral, no freebattle team%'
            OR strDesc LIKE '%Hunter, Neutral, no freebattle team%'
            OR strDesc LIKE '%Robber, Neutral, no freebattle team%'
            OR @desc like '%no job, Neutral, %no job, Neutral%')
        -- Get additional info for notice message
        DECLARE @CharName VARCHAR(64) = (SELECT CharName16 FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharID = @CharID)
        DECLARE @jobDesc VARCHAR(32) = CASE WHEN @jobType BETWEEN 1 AND 3 THEN 'Job Conflict' ELSE 'Free PVP' END
        DECLARE @strDesc VARCHAR(512)
        IF  @jobString LIKE 'Trader' OR @jobString LIKE 'Robber' OR @jobString LIKE 'Hunter')
        BEGIN
            -- If it's a Job Kill, then write character nicknames
            DECLARE @killerNickName VARCHAR(64) = (SELECT NickName16 FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharID = @killeriD)
            DECLARE @CharnickName VARCHAR(64) = (SELECT NickName16 FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharID = @CharID)
            SET @strDesc = '[' + @killerNickName + '] has just killed [' + @CharnickName + '] in [' + @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
        END
        ELSE BEGIN
            -- If it's normal PVP Kill, write real character names
            SET @strDesc = '[' + @killername + '] has just killed [' + @CharName + '] in [' + @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
        END
        -- Update the log
        INSERT INTO _LogEventPVP VALUES (0, @killeriD, @CharID, @jobType, GETDATE(), @strPos, @strDesc)
        -- Flag notice if it's not sent more than 5 minutes
        UPDATE _LogEventPVP SET isSent = 1 WHERE CharID = @CharID AND EventTime < DATEADD(MINUTE, -5, GETDATE())
    END
END

Result of _LogEventPVP table after each kills: (strDesc is something that we can parse in-game using PlusNotice thingy)

Spoiler:

Sample query to get kill count when doing job:

Spoiler:

SELECT COUNT(*) FROM [SRO_VT_LOG].[dbo].[_LogEventPVP] WHERE CharID = <yourCharID> AND JobType > 0

That's all ;)

NOTES:

• You need to make sure that your "_AddLogChar" procedure is working. So If you use Evangelion SR_GameServer, you should set "disableLog = 0" in misc.ini and restart the gameserver.
• CharID = who kills
• KilledCharID = who got killed
• isSent = 0 or 1, to make it work with modified PlusNotice program
• strDesc = notice message, for PlusNotice also. (see screenshot)
• JobType (for your websites): 1 = Trader, 2 = Thief, 3 = Hunter, 0 = No job (Free PVP)
• My DB is vSRO 1.88 with SQL 2008 R2, didn't try in any other DB

---

NEW Updates: (December 02, 2013)

Modified version of Notice Writer:
NoticeWriter.zip

This is modified version Notice Writer v2 from this this thread. I modified it to work with this Kill Fetching system, but it will NOT work with your old PlusNotice system (if you already have it), because the table name and columns are different.

Video:

Spoiler:

---

Good luck and have fun ;)

[MentaL]

[denise456]

Good Job

[Crue]

good job , keep going ;)

[Witchy Moo]

First post updated ;)

[P0kemonMast]

Interesting , i'll test this and edit my post once i do ^>^

[Crue]

so you can make reward for each kill ?

[xaviar]

very good now not need any change in gameserver hex true

[Blacksheep25]

Awesome, another release from witchymoo :)

Will give it a try soon!

[Witchy Moo]

Interesting , i'll test this and edit my post once i do ^>^

awesome ;)

so you can make reward for each kill ?

Hm, excellent idea. Anyway, you can make anything out of it, just add with anything you want to it ;) for example... you can add query to count the kill and give the player something when reach certain kills, something like this:

-- Count kills (JOB KILLS)
DECLARE @KillCount INT = (SELECT COUNT(*) FROM [SRO_VT_LOG].[dbo].[_LogEventPVP] WHERE CharID = @CharID AND JobType > 0)
-- Set reward
DECLARE @JobPVPReward VARCHAR(128) = CASE
    WHEN @KillCount = 100 THEN 'ITEM_CODE_SOMETHING_100_KILLS'
    WHEN @KillCount = 500 THEN 'ITEM_CODE_SOMETHING_500_KILLS'
    WHEN @KillCount = 1000 THEN 'ITEM_CODE_SOMETHING_1000_KILLS'
END
-- Check if the reward code exist and deliver it
IF (EXISTS (SELECT CodeName128 FROM [SRO_VT_SHARD].[dbo].[_RefObjCommon] WHERE CodeName128 = @JobPVPReward))
BEGIN
    EXEC [SRO_VT_SHARD].[dbo].[_ADD_ITEM_EXTERN] @KillerName, @JobPVPReward, 1, 0
END

Customize that to match your needs, it's a rough code and just example anyway.

very good now not need any change in gameserver hex true

Indeed, hacking the HEX would be the last thing I wanna do (Since i'm not a programmer :P)

Awesome, another release from witchymoo :)

Will give it a try soon!

Have fun ;), and since you got BR files perhaps you can feed me some info about it, I made those in vSRO188 with SQL2K8R2

[karemsame]

what about Global :) can you do this saved in Table

[Witchy Moo]

what about Global :) can you do this saved in Table

I don't know, haven't tried that, I think you have to modify your _AddLogItem SP and put condition for @Operation related to global, as far as I checked, the @Operation for global chat = 16, CMIIW

[longway]

Witchymoo i get error in this line

-- Count kills (JOB KILLS)
DECLARE @KillCount INT = (SELECT COUNT(*) FROM [SRO_VT_LOG].[dbo].[_LogEventPVP] WHERE CharID = @CharID AND JobType > 0)
-- Set reward
DECLARE @JobPVPReward VARCHAR(128) = CASE
WHEN @KillCount = 100 THEN 'ITEM_CODE_SOMETHING_100_KILLS'
WHEN @KillCount = 500 THEN 'ITEM_CODE_SOMETHING_500_KILLS'
WHEN @KillCount = 1000 THEN 'ITEM_CODE_SOMETHING_1000_KILLS'
END
-- Check if the reward code exist and deliver it
IF (EXISTS (SELECT CodeName128 FROM [SRO_VT_SHARD].[dbo].[_RefObjCommon] WHERE CodeName128 = @JobPVPReward))
BEGIN
EXEC [SRO_VT_SHARD].[dbo].[_ADD_ITEM_EXTERN] @KillerName, @JobPVPReward, 1, 0
END

[Witchy Moo]

You will always get error if you only copy-paste the whole thing without understanding how it works or how you wanna make it work, read my 2 notes up there:

1. "for example:"
2. "Customize that to match your needs, it's a rough code and just example anyway."

So, do understand how it works and how you wanna make it work.

cheers ;)

[longway]

You will always get error if you only copy-paste the whole thing without understanding how it works or how you wanna make it work, read my 2 notes up there:

1. "for example:"
2. "Customize that to match your needs, it's a rough code and just example anyway."

So, do understand how it works and how you wanna make it work.

cheers ;)

1/ i don't get your System,s Copy-past
2/ my error in (@KillCount INT = (SELECT COUNT(*) ) its Give Me Wrong idk why
3/ Cheers :)

[loveme]

done and dont notice kil char , PlusNotice program open?