From e65239e53bf7f9ce53ec0157fe41fad94beea682 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Tue, 18 May 2021 23:51:32 +0200
Subject: server: Add a /stats page to show game result statistics.

---
 server.js | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

(limited to 'server.js')

diff --git a/server.js b/server.js
index edf3ef9..719b288 100644
--- a/server.js
+++ b/server.js
@@ -311,6 +311,20 @@ app.get('/users', function (req, res) {
 	res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows });
 });
 
+const QUERY_STATS = db.prepare(`
+	SELECT title_name, scenario, result, count(*) AS count
+	FROM games
+	JOIN titles ON games.title_id=titles.title_id
+	WHERE status=2 AND private=0
+	GROUP BY title_name, scenario, result
+	`);
+
+app.get('/stats', function (req, res) {
+	LOG(req, "GET /stats");
+	let stats = QUERY_STATS.all();
+	res.render('stats.ejs', { user: req.user, message: req.flash('message'), stats: stats });
+});
+
 app.get('/change_password', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /change_password");
 	res.render('change_password.ejs', { user: req.user, message: req.flash('message') });
-- 
cgit v1.2.3