diff options
| -rw-r--r-- | schema.sql | 58 | ||||
| -rw-r--r-- | server.js | 17 | ||||
| -rw-r--r-- | views/user.pug | 27 |
3 files changed, 91 insertions, 11 deletions
@@ -119,13 +119,57 @@ create view user_login_view as drop view if exists user_profile_view; create view user_profile_view as - select - user_id, name, mail, notify, ctime, atime, about, is_banned - from - users - left join user_first_seen using(user_id) - left join user_last_seen using(user_id) - left join user_about using(user_id) + with + timeout as ( + select + user_id, + count(1) as timeout_total, + max(time) as timeout_last + from + user_timeout + group by + user_id + ), + move_time as ( + select + user_id, + avg(time) as move_time_avg + from + user_move_time + group by + user_id + ), + profile as ( + select + user_id, name, mail, notify, ctime, atime, about, is_banned, + coalesce(timeout_total, 0) as timeout_total, + coalesce(timeout_last, 0) as timeout_last, + move_time_avg + from + users + left join user_first_seen using(user_id) + left join user_last_seen using(user_id) + left join user_about using(user_id) + left join timeout using(user_id) + left join move_time using(user_id) + ) + select + profile.*, + ( + select + count(1) + from + players + join games using(game_id) + where + players.user_id = profile.user_id + and games.is_opposed + and games.status > 1 + and games.result != 'None' + and games.mtime > timeout_last + ) as games_since_timeout + from + profile ; drop view if exists user_dynamic_view; @@ -827,13 +827,22 @@ app.get("/user/:who_name", function (req, res) { let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name) if (who) { let games = QUERY_LIST_PUBLIC_GAMES_OF_USER.all({ user_id: who.user_id }) + let ratings = SQL_USER_RATINGS.all(who.user_id) annotate_games(games, 0, null) let active_pools = TM_POOL_LIST_USER_ACTIVE.all(who.user_id) let finished_pools = TM_POOL_LIST_USER_RECENT_FINISHED.all(who.user_id) let relation = 0 if (req.user) relation = SQL_SELECT_RELATION.get(req.user.user_id, who.user_id) | 0 - res.render("user.pug", { user: req.user, who, relation, games, active_pools, finished_pools }) + res.render("user.pug", { + user: req.user, + who, + relation, + games, + active_pools, + finished_pools, + ratings, + }) } else { return res.status(404).send("User not found.") } @@ -4189,12 +4198,12 @@ const SQL_USER_STATS = SQL(` `) const SQL_USER_RATINGS = SQL(` - select title_name, rating, count, date(last) as last + select title_id, title_name, rating, count, date(last) as last from ratings join titles using(title_id) where user_id = ? - and count >= 5 - order by rating desc + and count >= 3 + order by count desc `) const SQL_GAME_RATINGS = SQL(` diff --git a/views/user.pug b/views/user.pug index fc55b42..4aecb0b 100644 --- a/views/user.pug +++ b/views/user.pug @@ -42,6 +42,33 @@ html br a(href="/contacts/add-enemy/"+who.name) Add to blacklist + h3 Response time + div Average response time: #{(who.move_time_avg * 24).toFixed(2)} hours + + h3 Timeouts + div Total number of timeouts: #{who.timeout_total} + div Games completed since last timeout: #{who.games_since_timeout} + + if ratings.length > 0 + h3 Most played games + table + thead + tr + th Title + th Count + th Last played + if user && user.user_id === 1 + th Elo + tbody + each row in ratings + tr + td + a.black(href="/" + row.title_id)= row.title_name + td.r= row.count + td.r= row.last + if user && user.user_id === 1 + td.r= row.rating + +tourlist(null, active_pools, finished_pools) if open_games.length > 0 |