diff options
| -rw-r--r-- | server.js | 29 | ||||
| -rw-r--r-- | tools/sql/schema.txt | 56 | ||||
| -rw-r--r-- | views/stats.pug | 46 | ||||
| -rw-r--r-- | views/user_stats.pug | 38 |
4 files changed, 127 insertions, 42 deletions
@@ -210,6 +210,15 @@ const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id const SQL_COUNT_INBOX = SQL("SELECT COUNT(*) FROM messages WHERE to_id=? AND read=0 AND deleted_from_inbox=0").pluck(); +const SQL_USER_STATS = SQL(` + SELECT title_name, scenario, SUM(role=result) AS won, count(*) AS total + FROM players + NATURAL JOIN games + NATURAL JOIN titles + WHERE user_id=? AND status=2 AND game_id IN (SELECT game_id FROM opposed_games) + GROUP BY title_name, scenario + `); + function is_blacklisted(mail) { if (SQL_BLACKLIST_MAIL.get(mail) === 1) return true; @@ -512,6 +521,17 @@ app.post('/change_about', must_be_logged_in, function (req, res) { return res.redirect('/profile'); }); +app.get('/user/:who_name/stats', function (req, res) { + LOG(req, "GET /user/" + req.params.who_name + "/stats"); + let who = SQL_SELECT_USER_BY_NAME.get(req.params.who_name); + if (who) { + let stats = SQL_USER_STATS.all(who.user_id); + res.render('user_stats.pug', { user: req.user, who: who, stats: stats }); + } else { + return res.status(404).send("Invalid user name."); + } +}); + app.get('/user/:who_name', function (req, res) { LOG(req, "GET /user/" + req.params.who_name); let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name); @@ -1594,7 +1614,7 @@ function on_chat(socket, message) { try { let chat = SQL_INSERT_GAME_CHAT.get(socket.game_id, socket.user_id, message); chat[2] = socket.user_name; - SLOG(socket, "CHAT", JSON.stringify(chat)); + SLOG(socket, "CHAT"); for (let other of clients[socket.game_id]) if (other.role !== "Observer") other.emit('chat', chat); @@ -1748,16 +1768,13 @@ io.on('connection', (socket) => { * HIDDEN EXTRAS */ -const QUERY_STATS = db.prepare("SELECT * FROM game_stat_view"); +const SQL_GAME_STATS = db.prepare("SELECT * FROM game_stat_view"); app.get('/stats', function (req, res) { LOG(req, "GET /stats"); - let stats = QUERY_STATS.all(); + let stats = SQL_GAME_STATS.all(); res.render('stats.pug', { user: req.user, stats: stats, - title_role_map: ROLES, - title_name_map: TITLES, - title_rule_map: RULES, }); }); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index a7cb6bb..13062ee 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -121,8 +121,8 @@ DROP VIEW IF EXISTS thread_view; CREATE VIEW thread_view AS SELECT * , author.name AS author_name - , ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies - , ( SELECT MAX(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime + , ( SELECT count(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies + , ( SELECT max(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime FROM threads JOIN users AS author ON threads.author_id = author.user_id ; @@ -227,13 +227,26 @@ CREATE VIEW game_full_view AS NATURAL JOIN users WHERE players.game_id=game_view.game_id ) AS player_names, - ( SELECT COUNT(DISTINCT user_id) = 1 + ( SELECT count(DISTINCT user_id) = 1 FROM players WHERE players.game_id=game_view.game_id ) AS is_solo FROM game_view ; +DROP VIEW IF EXISTS opposed_games; +CREATE VIEW opposed_games AS + SELECT * + FROM GAMES + WHERE + STATUS > 0 AND + ( + SELECT count(DISTINCT user_id) > 1 + FROM players + where players.game_id=games.game_id + ) + ; + DROP VIEW IF EXISTS your_turn_reminder; CREATE VIEW your_turn_reminder AS SELECT @@ -287,6 +300,7 @@ BEGIN AND new.role <> 'Random 4' AND new.role <> 'Random 5' AND new.role <> 'Random 6' + AND new.role <> 'Random 7' ; END; @@ -301,11 +315,39 @@ BEGIN DELETE FROM players WHERE game_id = old.game_id; END; +-- Game Stats + +DROP VIEW IF EXISTS role_index_view; +CREATE VIEW role_index_view(t,i,r) AS + SELECT title_id, row_number() OVER ( PARTITION BY title_id ), role + FROM roles; + DROP VIEW IF EXISTS game_stat_view; CREATE VIEW game_stat_view AS - SELECT title_id, scenario, result, count(*) AS count - FROM game_full_view + SELECT title_name, NULL AS scenario + , NULL as total + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=1) AS r1 + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=2) AS r2 + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=3) AS r3 + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=4) AS r4 + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=5) AS r5 + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=6) AS r6 + , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=7) AS r7 + FROM titles + WHERE hidden=0 + UNION + SELECT title_name, scenario + , count(*) as total + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=1) AS r1 + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=2) AS r2 + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=3) AS r3 + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=4) AS r4 + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=5) AS r5 + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=6) AS r6 + , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=7) AS r7 + FROM games NATURAL JOIN titles - WHERE hidden=0 AND is_solo=0 AND status=2 - GROUP BY title_name, scenario, result + WHERE hidden=0 AND status=2 AND game_id IN (SELECT game_id FROM opposed_games) + GROUP BY title_id, scenario + ORDER BY title_name, total DESC NULLS FIRST ; diff --git a/views/stats.pug b/views/stats.pug index 7634048..b6c8673 100644 --- a/views/stats.pug +++ b/views/stats.pug @@ -6,40 +6,28 @@ html title Game Statistics style. table { table-layout: fixed; min-width: auto; } + td:not(:nth-child(1)) { text-align: right; } td:nth-child(1) { width: 240px; } td { width: 100px; } - tr.blank { height: 2rem; border: none; } + tr+tr.blank { height: 2rem; border: none; } body include header article h1 Game Statistics table - - - function total(t,s) { - return stats - .filter(entry => entry.title_id === t && entry.scenario === s) - .reduce((acc, entry) => acc + entry.count, 0); - } - function result(t,s,r) { - let info = stats.find(entry => entry.title_id === t && entry.scenario === s && entry.result === r); - return info ? info.count : 0; - } - each title_name, title_id in title_name_map - unless title_name_map[title_id].hidden - - let scenarios = title_rule_map[title_id].scenarios; - - let roles = title_role_map[title_id].concat(["Draw"]); - tr - th= title_name_map[title_id].title_name - each role in roles - th= role - each scenario in scenarios - - let t = total(title_id, scenario); - tr - td #{scenario} (#{t}) - each role in roles - if t > 0 - -let r = result(title_id, scenario, role); - td= Math.round(r * 100 / t) + "%" - else - td - + - function p(t,r) { return r > 0 ? Math.round(r*100/t) + "%" : "" } + - function drawn(x) { return x.total-(x.r1+x.r2+x.r3+x.r4+x.r5+x.r6+x.r7) } + each row in stats + unless row.scenario tr.blank + tr + th= row.title_name + th= row.r1 + th= row.r2 + th Draw + else + tr + td #{row.scenario} (#{row.total}) + td= p(row.total, row.r1) + td= p(row.total, row.r2) + td= p(row.total, drawn(row)) diff --git a/views/user_stats.pug b/views/user_stats.pug new file mode 100644 index 0000000..70211ef --- /dev/null +++ b/views/user_stats.pug @@ -0,0 +1,38 @@ +//- vim:ts=4:sw=4: +- let all_won = 0 +- let all_total = 0 +doctype html +html + head + include head + title Stats for #{who.name} + style td:nth-child(3),td:nth-child(4){text-align:right} + body + include header + article + h1 Stats for #{who.name} + + table(style="min-width:auto") + tr + th Title + th Scenario + th Won + th Total + each row in stats + tr + - all_won += row.won + - all_total += row.total + td= row.title_name + td= row.scenario + td= row.won + td= row.total + tr + th + th + th + th + tr + td + td + td= Math.round(all_won * 100 / all_total) + "%" + td= all_total |