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

[Witchy Moo]

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

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  [USER=275999]des[/USER]c LIKE '%Trader, Neutral, no freebattle team%'    -- Trader
        OR [USER=275999]des[/USER]c LIKE '%Hunter, Neutral, no freebattle team%'    -- Hunter
        OR [USER=275999]des[/USER]c LIKE '%Robber, Neutral, no freebattle team%'    -- Thief
        OR [USER=275999]des[/USER]c like '%no job, Neutral, %no job, Neutral%'    -- Free PVP
    )
    BEGIN
        -- Get killer name
        DECLARE [USER=434304]killername[/USER] VARCHAR(512) = [USER=275999]des[/USER]c
        DECLARE [USER=803511]killeri[/USER]D INT = 0
        SELECT [USER=434304]killername[/USER] = REPLACE [USER=434304]killername[/USER], LEFT [USER=434304]killername[/USER], CHARINDEX('(', [USER=434304]killername[/USER])), '')
        SELECT [USER=434304]killername[/USER] = REPLACE [USER=434304]killername[/USER], RIGHT [USER=434304]killername[/USER], CHARINDEX(')', REVERSE [USER=434304]killername[/USER]))), '')
        SELECT [USER=803511]killeri[/USER]D = CharID FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharName16 = [USER=434304]killername[/USER]
        -- Get job type
        DECLARE [USER=768752]job[/USER]String VARCHAR(10) = LTRIM(RTRIM(SUBSTRING [USER=275999]des[/USER]c, 5, 7)))
        DECLARE [USER=768752]job[/USER]Type INT = CASE
            WHEN [USER=768752]job[/USER]String LIKE 'Trader' THEN 1
            WHEN [USER=768752]job[/USER]String LIKE 'Robber' THEN 2
            WHEN [USER=768752]job[/USER]String LIKE 'Hunter' THEN 3
            ELSE 0 END
        -- Delete original log
        DELETE FROM _LogEventChar WHERE CharID = [USER=551894]Char[/USER]ID 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 [USER=275999]des[/USER]c like '%no job, Neutral, %no job, Neutral%')
        -- Get additional info for notice message
        DECLARE [USER=551894]Char[/USER]Name VARCHAR(64) = (SELECT CharName16 FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharID = [USER=551894]Char[/USER]ID)
        DECLARE [USER=768752]job[/USER]Desc VARCHAR(32) = CASE WHEN [USER=768752]job[/USER]Type BETWEEN 1 AND 3 THEN 'Job Conflict' ELSE 'Free PVP' END
        DECLARE @strDesc VARCHAR(512)
        IF  [USER=768752]job[/USER]String LIKE 'Trader' OR [USER=768752]job[/USER]String LIKE 'Robber' OR [USER=768752]job[/USER]String LIKE 'Hunter')
        BEGIN
            -- If it's a Job Kill, then write character nicknames
            DECLARE [USER=770918]killer[/USER]NickName VARCHAR(64) = (SELECT NickName16 FROM [SRO_VT_SHARD].[dbo].[[COLOR=#ff0000]_Char[/COLOR]] WHERE CharID = [USER=803511]killeri[/USER]D)
            DECLARE [USER=1333392005]Charnic[/USER]kName VARCHAR(64) = (SELECT NickName16 FROM [SRO_VT_SHARD].[dbo].[[COLOR=#ff0000]_Char[/COLOR]] WHERE CharID = [USER=551894]Char[/USER]ID)
            SET @strDesc = '[' + [USER=770918]killer[/USER]NickName + '] has just killed [' + [USER=1333392005]Charnic[/USER]kName + '] in [' + [USER=768752]job[/USER]Desc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
        END
        ELSE BEGIN
            -- If it's normal PVP Kill, write real character names
            SET @strDesc = '[' + [USER=434304]killername[/USER] + '] has just killed [' + [USER=551894]Char[/USER]Name + '] in [' + [USER=768752]job[/USER]Desc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
        END
        -- Update the log
        INSERT INTO _LogEventPVP VALUES (0, [USER=803511]killeri[/USER]D, [USER=551894]Char[/USER]ID, [USER=768752]job[/USER]Type, GETDATE(), @strPos, @strDesc)
        -- Flag notice if it's not sent more than 5 minutes
        UPDATE _LogEventPVP SET isSent = 1 WHERE CharID = [USER=551894]Char[/USER]ID 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:
View attachment 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

 

[denise456]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

Good Job

 

[Crue]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

good job , keep going

 

[Witchy Moo]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

First post updated

 

[P0kemonMast]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

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

 

[Crue]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

so you can make reward for each kill ?

 

[xaviar]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

very good now not need any change in gameserver hex true

 

[Blacksheep25]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

Awesome, another release from witchymoo

Will give it a try soon!

 

[Witchy Moo]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

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 )

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]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

what about Global can you do this saved in Table

 

[Witchy Moo]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

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]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

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]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

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]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

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]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

done and dont notice kil char , PlusNotice program open?

 

[MeGZONE]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

This query is for Free PvP? on what Job?

 

[Blacksheep25]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

done and dont notice kil char , PlusNotice program open?

I don't think this uses PlusNotice, but it could be used with it with modifications

 

[gotsha]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

I Don't UnderStand This When Character Kill Ckaracter At PVP He Writ Notic Or WHAT

 

[NourAyman]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

For Auto Notice -

Source:

You must be registered to see links

+ Brain.exe

 

[longway]

Re: "Simplified" [Job Kill] and [Free PVP Kill] Fetching (vSRO 1.88)

NourAyman
can you give me the PW of rare ?