272 lines
12 KiB
SQL
272 lines
12 KiB
SQL
-- D deathmatch
|
|
-- M maze
|
|
-- MT best maze time
|
|
-- P paper chase (mine town revival weeks) 2012
|
|
-- A online age
|
|
-- T ATK
|
|
-- F DEF
|
|
-- W wealth (money)
|
|
-- X XP
|
|
-- B Best (xp and online age)
|
|
-- @ Achievement score
|
|
-- R Role play score (xp, online age and achievement)
|
|
-- O
|
|
-- 0
|
|
-- 3,4,5,6,7,8,9 Paper Chase 2013 and newer
|
|
-- S Sokoban score
|
|
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
SET sql_notes = 0;
|
|
|
|
UPDATE achievement SET reached =
|
|
(SELECT count(*) as cnt
|
|
FROM reached_achievement, character_stats
|
|
WHERE reached_achievement.achievement_id = achievement.id
|
|
AND reached_achievement.charname = character_stats.name
|
|
AND character_stats.admin<=600
|
|
AND achievement.category != 'SPECIAL'
|
|
AND achievement.active=1
|
|
GROUP BY achievement_id);
|
|
UPDATE achievement SET reached = 0 WHERE reached IS NULL;
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='D' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='M' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='MT' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points ASC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='P' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT name, 'A', @rownum:=@rownum+1 as rankX, age/60, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND age >= 5 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY age DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT name, 'T', @rownum:=@rownum+1 as rankX, atk*(1+0.03*level) As points, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND level >= 2 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT name, 'F', @rownum:=@rownum+1 as rankX, def*(1+0.03*level) As points, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND level >= 2 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT name, 'W', @rownum:=@rownum+1 as rankX, money, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND money >= 100 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY money DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT name, 'X', @rownum:=@rownum+1 as rankX, xp, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND xp >= 100 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY xp DESC, karma DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT name, 'B', @rownum:=@rownum+1 as rankX, xp/(age+1) As points, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND xp >= 100 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT scoretable.charname, '@', @rownum:=@rownum+1 as rankX, scoretable.points, CURRENT_DATE()
|
|
FROM (
|
|
SELECT charname, round(1000000*sum(1/reached)) As points, count(*) As nmb, xp
|
|
FROM achievement
|
|
JOIN reached_achievement ra ON (ra.achievement_id=achievement.id)
|
|
JOIN character_stats ON (name = charname)
|
|
WHERE admin<=600 AND level >= 2 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
AND achievement.reached > 0
|
|
GROUP BY charname, xp
|
|
ORDER BY points DESC, nmb DESC, xp DESC) As scoretable,
|
|
(SELECT @rownum:=0) r WHERE points > 500;
|
|
|
|
|
|
|
|
INSERT INTO halloffame_archive_recent (charname, fametype, halloffame_archive_recent.rank, points, day)
|
|
SELECT scoretable.name, 'R', @rownum:=@rownum+1 as rankX, scoretable.points, CURRENT_DATE()
|
|
FROM (
|
|
SELECT name, round(ln(xp*(ifnull(sum(1/reached)*1000,0)+0.0001)/(age+1))*1000) As points, sum(1/reached)
|
|
FROM achievement
|
|
JOIN reached_achievement ra ON (ra.achievement_id=achievement.id)
|
|
RIGHT JOIN character_stats ON name = charname
|
|
WHERE admin<=600 AND level >= 2 AND character_stats.lastseen>date_sub(CURRENT_TIMESTAMP, interval 1 month)
|
|
AND achievement.reached > 0
|
|
GROUP BY name
|
|
ORDER BY points desc
|
|
) As scoretable,
|
|
(SELECT @rownum:=0) r WHERE points > 0;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TRUNCATE TABLE halloffame_archive_alltimes;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='D' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='M' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='MT' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0
|
|
ORDER BY points ASC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT charname, fametype, @rownum:=@rownum+1 as rankX, points, CURRENT_DATE()
|
|
FROM halloffame, character_stats, (SELECT @rownum:=0) r
|
|
WHERE fametype='P' AND halloffame.charname = character_stats.name AND admin<=600 AND points > 0
|
|
ORDER BY points;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT name, 'A', @rownum:=@rownum+1 as rankX, age/60, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND age >= 5
|
|
ORDER BY age DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT name, 'T', @rownum:=@rownum+1 as rankX, atk*(1+0.03*level) As points, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND level >= 2
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT name, 'F', @rownum:=@rownum+1 as rankX, def*(1+0.03*level) As points, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND level >= 2
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT name, 'W', @rownum:=@rownum+1 as rankX, money, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND money >= 100
|
|
ORDER BY money DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT name, 'X', @rownum:=@rownum+1 as rankX, xp, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND xp >= 100
|
|
ORDER BY xp DESC, karma DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT name, 'B', @rownum:=@rownum+1 as rankX, xp/(age+1) As points, CURRENT_DATE()
|
|
FROM character_stats, (SELECT @rownum:=0) r
|
|
WHERE admin<=600 AND xp >= 100
|
|
ORDER BY points DESC;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT scoretable.charname, '@', @rownum:=@rownum+1 as rankX, scoretable.points, CURRENT_DATE()
|
|
FROM (
|
|
SELECT charname, round(1000000*sum(1/reached)) As points, count(*) As nmb, xp
|
|
FROM achievement
|
|
JOIN reached_achievement ra ON (ra.achievement_id=achievement.id)
|
|
JOIN character_stats ON (name = charname)
|
|
WHERE admin<=600 AND level >= 2
|
|
AND achievement.reached > 0
|
|
GROUP BY charname, xp
|
|
ORDER BY points DESC, nmb DESC, xp DESC) As scoretable,
|
|
(SELECT @rownum:=0) r WHERE points > 500;
|
|
|
|
|
|
INSERT INTO halloffame_archive_alltimes (charname, fametype, halloffame_archive_alltimes.rank, points, day)
|
|
SELECT scoretable.name, 'R', @rownum:=@rownum+1 as rankX, scoretable.points, CURRENT_DATE()
|
|
FROM (
|
|
SELECT name, round(ln(xp*(ifnull(sum(1/reached)*1000,0)+0.0001)/(age+1))*1000) As points, sum(1/reached)
|
|
FROM achievement
|
|
JOIN reached_achievement ra ON (ra.achievement_id=achievement.id)
|
|
RIGHT JOIN character_stats ON name = charname
|
|
WHERE admin<=600 AND level >= 2
|
|
AND achievement.reached > 0
|
|
GROUP BY name
|
|
ORDER BY points desc
|
|
) As scoretable,
|
|
(SELECT @rownum:=0) r WHERE points > 0;
|
|
|
|
|
|
|
|
-- statistics
|
|
|
|
INSERT INTO statistics_archive (name, val, day)
|
|
SELECT 'accounts_30', count(DISTINCT player_id), CURRENT_DATE()
|
|
FROM loginEvent
|
|
WHERE timedate>date_sub(CURRENT_TIMESTAMP, interval 30 day) AND result=1;
|
|
|
|
INSERT INTO statistics_archive (name, val, day)
|
|
SELECT 'accounts_7', count(DISTINCT player_id), CURRENT_DATE()
|
|
FROM loginEvent
|
|
WHERE timedate>date_sub(CURRENT_TIMESTAMP, interval 7 day) AND result=1;
|
|
|
|
INSERT INTO statistics_archive (name, val, day)
|
|
SELECT 'accounts_1', count(DISTINCT player_id), CURRENT_DATE()
|
|
FROM loginEvent
|
|
WHERE timedate>date_sub(CURRENT_TIMESTAMP, interval 1 day) AND result=1;
|
|
|
|
CREATE TABLE IF NOT EXISTS cid(charname varchar(32), timedate timestamp);
|
|
|
|
INSERT INTO statistics_archive (name, val, day)
|
|
SELECT 'characters_30', count(DISTINCT charname), CURRENT_DATE()
|
|
FROM cid
|
|
WHERE timedate>date_sub(CURRENT_TIMESTAMP, interval 30 day);
|
|
|
|
INSERT INTO statistics_archive (name, val, day)
|
|
SELECT 'characters_7', count(DISTINCT charname), CURRENT_DATE()
|
|
FROM cid
|
|
WHERE timedate>date_sub(CURRENT_TIMESTAMP, interval 7 day);
|
|
|
|
INSERT INTO statistics_archive (name, val, day)
|
|
SELECT 'characters_1', count(DISTINCT charname), CURRENT_DATE()
|
|
FROM cid
|
|
WHERE timedate>date_sub(CURRENT_TIMESTAMP, interval 1 day);
|
|
|