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 ++++++++++++++
 views/stats.ejs | 36 ++++++++++++++++++++++++++++++++++++
 2 files changed, 50 insertions(+)
 create mode 100644 views/stats.ejs

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') });
diff --git a/views/stats.ejs b/views/stats.ejs
new file mode 100644
index 0000000..db497e8
--- /dev/null
+++ b/views/stats.ejs
@@ -0,0 +1,36 @@
+<%- include('header', { title: "Game Statistics" }) -%>
+<style>tr.blank{height:2rem;border:none;}</style>
+<table class="wide">
+<%
+	let total = {};
+	let results = {};
+	for (let { title_name, scenario, result, count } of stats) {
+		if (total[title_name] == undefined)
+			total[title_name] = {};
+		if (total[title_name][scenario] == undefined)
+			total[title_name][scenario] = 0;
+		total[title_name][scenario] += count;
+		if (results[title_name] == undefined)
+			results[title_name] = [];
+		if (!results[title_name].includes(result))
+			results[title_name].push(result);
+	}
+	let last_title = null;
+	let last_scenario = null;
+	for (let { title_name, scenario, result, count } of stats) {
+		if (title_name != last_title) {
+			if (last_title != null) {
+				%><tr class="blank"><%
+			}
+			%><tr><th><%= title_name %><%
+			results[title_name].forEach(item => { %><th><%= item %><% });
+		}
+		if (scenario != last_scenario) {
+			%><tr><td><%= scenario %> (<%= total[title_name][scenario] %>)<%
+		}
+		%><td><%= Math.round(count * 100 / total[title_name][scenario]) %>%<%
+		last_title = title_name;
+		last_scenario = scenario;
+	}
+%>
+</table>
-- 
cgit v1.2.3