How do I get ranking information from DB?

[King_Dud]

So I'm a web developer, I have no experience at all with this game and some dude hired me to create a complete React site for his game's private server. This guy has absolutely no idea how anything about the game's server works and his developers aren't available

I'm done with most of the site, including the forum. I'm left with the leaderboards but I can't figure out how the game's ranking system works.

So my questions are

1. How do I calculate unique ranks? Does every character have different points for each unique monster and then I have to add all of those to sort by rank?
2. And where are PvP ranks stored? or how is that calculated? Is it calculated from DailyPK, TotalPK and PKPenaltyPoint in SRO_VT_SHARD._Char table?
3. How are player ranks calculated? From Item strength? Level? total experience points?
4. And how do u calculate Guild rank points? With level? GatheredSP? Booty? Gold?
5. How are Wanted levels calculated? Or where are they stored? Couldn't find them..

Thanks for your time

[#HB]

1- About unique rank: There's already gameservers who logs the unique kills at the account database, so ask your dude about the gameserver he's using, if Evangelion then you'll find a table called "Evangelion_uniques", else if srZor the table name is "srZor_uniques". Nah, there's no specific points for each unique but you can define that yourself. You can do something like this to get the top record by unique kills: .

2- About pvp rank: Well, actually you can get the amount of pvp kills like what I've done above but with shardlog's _LogEventChar table like: .
But I actually prefer making a new table and altering _AddLogChar procedure to insert into that new table, which will be smoother and easier to control.

3- About player rank: PPL at past used to compare level and exp offset, but newer they made a system called item points. This might be quite deep for you, since you got no experience at sro's database. First, there's 3 rarity types at SRO. Arranged by strength/power (Normal A, Normal B, Normal C, Star (Rare A), Moon (Rare B), Sun (Rare C)), you're gonna make a specific amount of points for each rarity type of them. And calculate the amount of points checking each item for each character. Like this: .

(Note: The query of player rank is kinda dump behaving, it was an old code of me. @ kinda not working as it should tho. You can make much better one, I am just giving an example.)

Note: You might find the code stupid and complex. If you use inner join or table as, it would be much quicker and smoother. And as I said before, I am just giving an example. ATN it was an old code.

I will respond to your other questions later.

[King_Dud]

I got the basic idea about point 3 now, I might be able to google around for some examples. That was very helpful, thanks.
I'll be waiting for the other answers.

[#HB]

4- About guild rank: It's the most simple one actually. We aren't going to use item points system here, so we're gonna do it with GuildLevel/GuildSP/GuildMembersCount. You can just work with this one: .

5- Wanted? Do you mean job wanted system?

[Crayon*]

Unique ranking => It is easier to explain in code for me.

UPDATE UR SET points+=UI.Points,
	UR.Kills += 1
FROM SRO_VT_SHARD.._UniqueRanking UR
JOIN SRO_VT_SHARD.._UniqueInfo UI ON UR.CodeName128=UI.CodeName128
WHERE UR.CharID=@CharID AND UR.CodeName128=@MonsterCodeName

PvP Kills => EventID = 20 in _AddLogChar means character death. You basically create a table with columns. CharID, PvP Points, Job Points, and also I prefer to add Item points to this table (will explain how to calculate that below) and in AddLogChar you basically += 1 the points for Killer CharID. How to get KillerID?

SELECT  [MENTION=3271931]killername[/MENTION] = (SELECT SUBSTRING  [MENTION=316630]DEsc[/MENTION], CHARINDEX('('  [MENTION=316630]DEsc[/MENTION])+1, CHARINDEX(')'  [MENTION=316630]DEsc[/MENTION]) - CHARINDEX('('  [MENTION=316630]DEsc[/MENTION])-1))
SELECT  [MENTION=2490155]killerid[/MENTION]   = (SELECT CharID FROM REDIC_SHARD.._Char WHERE CharName16 =  [MENTION=3271931]killername[/MENTION])

To check for job or free mode you can do

SELECT  [MENTION=2703943]killtyp[/MENTION]e = 
CASE
	WHEN     [MENTION=316630]DEsc[/MENTION] LIKE '%My: Trader%') 
		OR   [MENTION=316630]DEsc[/MENTION] LIKE '%My: Robber%')
		OR   [MENTION=316630]DEsc[/MENTION] LIKE '%My: Hunter%')
		THEN 'Job'
	WHEN   [MENTION=316630]DEsc[/MENTION] LIKE '%Murderer%')
		THEN 'Murder'
	ELSE 'Free'
END

The best way to do the player ranking. calculate the optlevel + item point + reqlevel1.

Guild points => SUM of item points of chars in a specific guild.

If you mean wanted system ingame you basically insert CharID & Points in a table then you += 1 that and depends on the points you assign a skill id for it using a case statement and maybe reward them. Also, make sure you set the points to 0 and delete the skill for that char if he is dead.

Btw, MENTION == @ which declares a variable in SQL. I hope I made myself clear, and if you are missing something do not ever hesitate to drop a DM on my Discord

P.S: #HB I think you should stop writing queries

[B1Q]

Originally Posted by Crayon*

P.S: #HB I think you should stop writing queries

I think you should stop writing queries aswell (not that you wrote these)
OP said #HB helped him get the basic idea i believe he's not looking for a "COPY PASTE" solution

[Crayon*]

Originally Posted by B1Q

I think you should stop writing queries aswell (not that you wrote these)
OP said #HB helped him get the basic idea i believe he's not looking for a "COPY PASTE" solution

Did I even provide complete code for anything? If you copy-paste what I wrote it wouldn't make sense LOL. I was just explaining the idea in code, and did you even read his queries?

[B1Q]

Originally Posted by Crayon*

Did I even provide complete code for anything? If you copy-paste what I wrote it wouldn't make sense LOL. I was just explaining the idea in code, and did you even read his queries?

your queries are equally as bad.
good luck

[King_Dud]

Originally Posted by #HB

5- Wanted? Do you mean job wanted system?

Thanks again. And honestly, I don't know what "wanted rank" is. But I was given as an example to what ranks to include.

@ Thanks a lot.

@ Yeah, copy paste ready queries aren't necessary but using them in explanation doesn't hurt even if it's awfully written..

Thanks a lot. I managed to complete it, I'll mark this as solved.