Added basic admin interface

author: Marvin Borner 2020-10-10 17:05:27 +0200
committer: Marvin Borner 2020-10-10 17:05:27 +0200
commit: 72f5731adeebf8d76c5c2dcc266f600ba57812d8 (patch)
tree: 4d774eb3d0464411a1bb09472c597298aa8965e8
parent: 167600b52eb03801bb7051a09dcb0e4f159cfb2a (diff)
8 files changed, 158 insertions, 13 deletions
diff --git a/admin/index.js b/admin/index.js
new file mode 100644
index 0000000..4cea14d
--- /dev/null
+++ b/admin/index.js
@@ -0,0 +1,53 @@
+const express = require("express");
+const db = require("../db");
+const app = express.Router();
+const { checkUser, checkAdmin } = require("../auth");
+
+app.use("/", checkAdmin, express.static(__dirname + "/public"));
+
+// For debugging ig
+app.get("/api/all", checkAdmin, async (req, res) => {
+    const all = [];
+
+    const types = await db.query("SELECT * FROM types ORDER BY id");
+    const clazz = await db.query("SELECT * FROM class ORDER BY id");
+    const users = await db.query("SELECT * FROM users ORDER BY id");
+    const quotes = await db.query("SELECT * FROM quotes ORDER BY id");
+    const ranking_questions = await db.query("SELECT * FROM ranking_questions ORDER BY id");
+    const ranking_answers = await db.query("SELECT * FROM ranking_answers ORDER BY id");
+    const mottos = await db.query("SELECT * FROM mottos ORDER BY id");
+    const motto_votes = await db.query("SELECT * FROM motto_votes ORDER BY id");
+
+    all.push(
+        { quotes },
+        { clazz },
+        { users },
+        { quotes },
+        { ranking_questions },
+        { ranking_answers },
+        { mottos },
+        { motto_votes },
+    );
+    res.json(all);
+});
+
+app.get("/api/questions", checkAdmin, async (req, res) => {
+    const questions = await db.query("SELECT q.id, question, t.name type FROM ranking_questions q INNER JOIN types t on type_id = t.id ORDER BY q.id");
+    res.json(questions);
+});
+
+app.get("/api/answers", checkAdmin, async (req, res) => {
+    const answers = await db.query(
+	    "SELECT question_id, name, middlename, surname, count(*) count FROM ranking_questions q INNER JOIN ranking_answers a ON q.id = a.question_id INNER JOIN users u ON answer_id = u.id GROUP BY question_id, answer_id ORDER BY count DESC"
+    );
+    res.json(answers);
+});
+
+app.get("/api/votes", checkAdmin, async (req, res) => {
+    const votes = await db.query(
+        "SELECT m.id, m.name, m.description, SUM(votes) votes FROM motto_votes mv RIGHT JOIN mottos m on mv.motto_id = m.id GROUP BY m.id, m.name, m.description ORDER BY SUM(votes) DESC",
+    );
+    res.json(votes);
+});
+
+module.exports = app;
diff --git a/admin/public/index.html b/admin/public/index.html
new file mode 100644
index 0000000..cf5d286
--- /dev/null
+++ b/admin/public/index.html
@@ -0,0 +1,32 @@
+<!DOCTYPE html>
+<html>
+    <head>
+        <meta charset="UTF-8" />
+        <meta name="viewport" content="width=device-width, initial-scale=1" />
+        <link
+            rel="stylesheet"
+            href="https://unpkg.com/purecss@2.0.3/build/pure-min.css"
+            integrity="sha384-cg6SkqEOCV1NbJoCu11+bm0NvBRc8IYLRGXkmNrqUBfTjmMYwNKPWBTIKyw9mHNJ"
+            crossorigin="anonymous"
+        />
+        <link rel="stylesheet" href="style.css" type="text/css" media="all" />
+
+        <title>Home</title>
+    </head>
+    <body>
+        <div class="pure-menu pure-menu-horizontal">
+            <a href="/" class="pure-menu-item pure-menu-link">Home</a>
+            <a href="/auth/api/logout" class="pure-menu-item pure-menu-link">Logout</a>
+        </div>
+
+	<!-- TODO: Class-based stats (easily solveable in frontend) -->
+        <div class="card">
+	    Welche/r Schüler/in...
+	    <ul id="pupil"></ul>
+	    Welche/r Lehrer/in...
+	    <ul id="teacher"></ul>
+	</div>
+
+	<script src="script.js"></script>
+    </body>
+</html>
diff --git a/admin/public/script.js b/admin/public/script.js
new file mode 100644
index 0000000..ff6fa2b
--- /dev/null
+++ b/admin/public/script.js
@@ -0,0 +1,22 @@
+fetch("api/questions").then(questions => questions.json()).then(questions => {
+	fetch("api/answers").then(answers => answers.json()).then(answers => {
+		questions.forEach(question => question.answers = []);
+		answers.forEach(answer => questions[answer.question_id - 1].answers.push(answer));
+		render(questions);
+	});
+});
+
+function render(questions)
+{
+	console.log(questions);
+	const teacher = document.querySelector("ul#teacher");
+	const pupil = document.querySelector("ul#pupil");
+	questions.forEach(question => {
+		const list = question.type === "teacher" ? teacher : pupil;
+		let answers = "";
+		question.answers.forEach(answer => {
+			answers += `<li>${answer.name} ${answer.middlename ? answer.middlename + " " : ""}${answer.surname}: ${answer.count}</li>`
+		});
+		list.insertAdjacentHTML("beforeend", `<li>${question.question}<br><ol>${answers}</ol></li>`);
+	});
+}
diff --git a/admin/public/style.css b/admin/public/style.css
new file mode 100644
index 0000000..77853bf
--- /dev/null
+++ b/admin/public/style.css
@@ -0,0 +1,33 @@
+html,
+body {
+    padding: 0;
+    margin: 0;
+    height: 100%;
+    width: 100%;
+    background-color: #eec0c6;
+    background-image: linear-gradient(315deg, #eec0c6 0%, #7ee8fa 74%);
+}
+
+.card {
+    position: absolute;
+    max-height: 80%;
+    overflow: auto;
+    width: 30%;
+    left: 50%;
+    top: 50%;
+    -webkit-transform: translate(-50%, -50%);
+    transform: translate(-50%, -50%);
+    padding: 20px;
+    border-radius: 10px;
+    background: white;
+}
+
+div {
+    background: white;
+}
+
+@media only screen and (max-width: 600px) {
+    .card {
+        width: calc(100% - 50px);
+    }
+}
diff --git a/app.js b/app.js
index 5ddfdb7..e07aaaa 100644
--- a/app.js
+++ b/app.js
@@ -2,10 +2,11 @@ require("dotenv").config();
 const express = require("express");
 const session = require("express-session");
 
-const { auth, checkUser } = require("./auth");
+const { auth, checkUser, checkAdmin } = require("./auth");
 const mottovote = require("./mottovote");
 const quotes = require("./quotes");
 const poll = require("./poll");
+const admin = require("./admin");
 
 const app = express();
 
@@ -30,6 +31,7 @@ app.use("/", express.static(__dirname + "/overview/public"));
 app.use("/mottovote", checkUser, mottovote);
 app.use("/quotes", checkUser, quotes);
 app.use("/poll", checkUser, poll);
+app.use("/admin", checkAdmin, admin);
 app.use("/auth", auth);
 
 app.listen(process.env.PORT || 5005, () => console.log("Server started on http://localhost:5005"));
diff --git a/auth/index.js b/auth/index.js
index 1ea6290..40062cc 100644
--- a/auth/index.js
+++ b/auth/index.js
@@ -4,14 +4,24 @@ const db = require("../db");
 
 const app = express.Router();
 
-// TODO: Change passwords
-// TODO: Login (+ Frontend, cookie, etc)
-
 function checkUser(req, res, next) {
     if (req.session.loggedIn) next();
     else res.redirect("/auth");
 }
 
+function checkAdmin(req, res, next) {
+    if (!req.session.loggedIn) res.redirect("/auth");
+
+    try {
+        db.query("SELECT is_admin FROM users WHERE id = ?", [req.session.uid]).then((ret) => {
+            if (ret[0].is_admin == 1) next();
+            else res.redirect("/");
+        });
+    } catch (e) {
+        res.redirect("/");
+    }
+}
+
 app.use(
     "/",
     (req, res, next) => {
@@ -81,4 +91,4 @@ app.get("/api/list", checkUser, async (req, res) => {
 
 app.get("/api/status", (req, res) => res.json({ loggedIn: req.session.loggedIn }));
 
-module.exports = { auth: app, checkUser };
+module.exports = { auth: app, checkUser, checkAdmin };
diff --git a/mottovote/index.js b/mottovote/index.js
index d0af6e3..2df985d 100644
--- a/mottovote/index.js
+++ b/mottovote/index.js
@@ -34,12 +34,4 @@ app.put("/api/vote", checkUser, async (req, res) => {
     }
 });
 
-// Vote result - admin
-app.get("/api/get", checkUser, async (req, res) => {
-    const votes = await db.query(
-        "SELECT m.id, m.name, m.description, SUM(votes) votes FROM motto_votes mv RIGHT JOIN mottos m on mv.motto_id = m.id GROUP BY m.id, m.name, m.description ORDER BY SUM(votes) DESC",
-    );
-    res.json(votes);
-});
-
 module.exports = app;
diff --git a/tables.sql b/tables.sql
index e0bc160..9f139f2 100644
--- a/tables.sql
+++ b/tables.sql
@@ -27,6 +27,7 @@ CREATE TABLE IF NOT EXISTS users(
     password VARCHAR(255) NOT NULL,
     class_id INTEGER NOT NULL,
     type_id INTEGER NOT NULL,
+    is_admin BOOLEAN DEFAULT FALSE,
 
     UNIQUE KEY uk_name (name, middlename, surname),
     CONSTRAINT `fk_class_user` FOREIGN KEY (class_id) REFERENCES class (id),
author	Marvin Borner	2020-10-10 17:05:27 +0200
committer	Marvin Borner	2020-10-10 17:05:27 +0200
commit	72f5731adeebf8d76c5c2dcc266f600ba57812d8 (patch)
tree	4d774eb3d0464411a1bb09472c597298aa8965e8
parent	167600b52eb03801bb7051a09dcb0e4f159cfb2a (diff)