"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 needSR_GameServer modifications, no needseparate 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:
Code:
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:
Code:
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)
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
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.
Interesting , i'll test this and edit my post once i do ^>^
awesome ;)
Quote:
Originally Posted by Crue
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:
Code:
-- 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.
Quote:
Originally Posted by xaviar
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)
Quote:
Originally Posted by blacksheep25
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
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
-- 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
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:
"for example:"
"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.
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:
"for example:"
"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 :)
done and dont notice kil char , PlusNotice program open?
"unmodified" PlusNoticewill NOT work with this. You have to modify the PlusNotice program to match my table: "_LogEventPVP" and columns: ""isSent" and "strDesc", the original PlusNotice reads table "PlusNotice" and columns: "Sent", "Message" (read my first post CAREFULLY).
If you cannot modify the PlusNotice program you can use this kill record for other purposes, like website statistics maybe? or whatever.
Quote:
Originally Posted by MeGZONE
This query is for Free PvP? on what Job?
read my notes on first page, CAREFULLY.
Quote:
Originally Posted by blacksheep25
I don't think this uses PlusNotice, but it could be used with it with modifications
+1, Yep, needs modification to PlusNotice, as I already wrote in the first page.
Quote:
Originally Posted by NourAyman
For Auto Notice -
+ Brain.exe
+1, Yep, in simple words, (which I repeated several times already, needs to modify the PlusNotice to work with this SP), otherwise it will only write to database (which can be used also for another purposes, say... kill statistics on the website?) ;)
Finally made it ;), thanks to PlusNotice "source code release" I was able to make it work with this Kill Fetching system (check out the video), I'll improve the job query so it will log the "Job Alias" name and not real character name.
