Merge branch 'master' into profile

author: Lars Krönner 2020-10-10 18:39:46 +0200
committer: GitHub 2020-10-10 18:39:46 +0200
commit: 1f45ede8253421439e07790375b72a31ceef33ed (patch)
tree: a9285cef4e2dc3451ed609be2a34bbf21580c35e
parent: 930ecde7e84e723061cba4780459887c329e50a3 (diff)
parent: 16ebbb932c0b780c11d3e574bc24a515eb095f5f (diff)
21 files changed, 342 insertions, 28 deletions
diff --git a/README.md b/README.md
index 1592e41..1ab28fe 100644
--- a/README.md
+++ b/README.md
@@ -1,3 +1,12 @@
 # Abizeugs
 
-These are some tools for our Abitur (2021). They are very poorly written and should never be used by anyone except me.
+These are some tools for our Abitur (2021). They are very poorly written and should never be used by anyone except us (RBS TG13).
+
+## Installation
+* Install `nodejs`, `npm`, `redis` and `mariadb`. As the installation instructions vary depending on the platform, please look it up on your own
+* Start `redis` and `mariadb`
+* Create a `mariadb` database
+* Copy `.env.example` to `.env` and edit the file accordingly
+* Edit `mottos.txt`, `poll.txt` and `name.csv` (not published due to data protection)
+* Run `npm i`
+* Start the server with `node app`
diff --git a/admin/index.js b/admin/index.js
new file mode 100644
index 0000000..407bbcf
--- /dev/null
+++ b/admin/index.js
@@ -0,0 +1,55 @@
+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..815b6aa
--- /dev/null
+++ b/admin/public/index.html
@@ -0,0 +1,29 @@
+<!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>Admin</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>
+        <div class="card">
+            <h2>Admin</h2>
+            <ul>
+                <li><a href="ranking.html">Ranking</a></li>
+                <li><a href="votes.html">Votes</a></li>
+            </ul>
+        </div>
+    </body>
+</html>
diff --git a/admin/public/ranking.html b/admin/public/ranking.html
new file mode 100644
index 0000000..328f09a
--- /dev/null
+++ b/admin/public/ranking.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="ranking.js"></script>
+    </body>
+</html>
diff --git a/admin/public/ranking.js b/admin/public/ranking.js
new file mode 100644
index 0000000..4281e23
--- /dev/null
+++ b/admin/public/ranking.js
@@ -0,0 +1,26 @@
+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) {
+    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..4e3cffc
--- /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: 40%;
+    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: 800px) {
+    .card {
+        width: calc(100% - 50px);
+    }
+}
diff --git a/admin/public/votes.html b/admin/public/votes.html
new file mode 100644
index 0000000..b700ed9
--- /dev/null
+++ b/admin/public/votes.html
@@ -0,0 +1,30 @@
+<!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>Votes</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>
+        <div class="card">
+            <button class="pure-button" id="switch">Switch</button>
+            <br />
+            <canvas id="votes" width="400" height="400"></canvas>
+        </div>
+
+        <script src="https://cdn.jsdelivr.net/npm/chart.js@2.9.3/dist/Chart.bundle.min.js"></script>
+        <script src="votes.js"></script>
+    </body>
+</html>
diff --git a/admin/public/votes.js b/admin/public/votes.js
new file mode 100644
index 0000000..62ff374
--- /dev/null
+++ b/admin/public/votes.js
@@ -0,0 +1,51 @@
+let date;
+let chart;
+
+fetch("api/votes")
+    .then((response) => response.json())
+    .then((response) => {
+        data = response;
+        render("bar");
+    });
+
+function render(type) {
+    const ctx = document.getElementById("votes").getContext("2d");
+    chart = new Chart(ctx, {
+        type,
+        data: {
+            labels: data.map((v) => v.name),
+            datasets: [
+                {
+                    label: "# of Votes",
+                    data: data.map((v) => v.votes || 0),
+                    backgroundColor: () => "#" + (Math.random().toString(16) + "0000000").slice(2, 8),
+                    borderWidth: 1,
+                },
+            ],
+        },
+        options: {
+            legend: {
+                display: false,
+            },
+            scales: {
+                yAxes: [
+                    {
+                        ticks: {
+                            beginAtZero: true,
+                            precision: 0,
+                        },
+                    },
+                ],
+            },
+        },
+    });
+}
+
+let index = 0;
+const types = ["pie", "doughnut", "polarArea", "radar", "line", "bar"];
+document.getElementById("switch").addEventListener("click", () => {
+    chart.destroy();
+    render(types[index]);
+    if (index + 1 < types.length) index++;
+    else index = 0;
+});
diff --git a/app.js b/app.js
index c4e3039..ffa9ba9 100644
--- a/app.js
+++ b/app.js
@@ -2,11 +2,12 @@ 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 profile = require("./profile");
+const admin = require("./admin");
 
 const app = express();
 
@@ -32,6 +33,7 @@ app.use("/mottovote", checkUser, mottovote);
 app.use("/quotes", checkUser, quotes);
 app.use("/poll", checkUser, poll);
 app.use("/profile", checkUser, profile);
+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..e40ea43 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) => {
@@ -79,6 +89,14 @@ app.get("/api/list", checkUser, async (req, res) => {
     res.json(users);
 });
 
-app.get("/api/status", (req, res) => res.json({ loggedIn: req.session.loggedIn }));
+app.get("/api/status", (req, res) => {
+	if (req.session.loggedIn) {
+        db.query("SELECT is_admin FROM users WHERE id = ?", [req.session.uid]).then((ret) => {
+	    res.json({ loggedIn: req.session.loggedIn, admin: ret[0].is_admin ? true : false });
+        });
+	} else {
+	res.json({ loggedIn: false, admin: false });
+	}
+});
 
-module.exports = { auth: app, checkUser };
+module.exports = { auth: app, checkUser, checkAdmin };
diff --git a/auth/public/index.html b/auth/public/index.html
index b56db07..8273238 100644
--- a/auth/public/index.html
+++ b/auth/public/index.html
@@ -16,7 +16,6 @@
     <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>
 
         <form class="pure-form pure-form-stacked" action="api/login" method="post">
diff --git a/auth/public/style.css b/auth/public/style.css
index 4bbdc55..413ace1 100644
--- a/auth/public/style.css
+++ b/auth/public/style.css
@@ -14,7 +14,7 @@ div {
 
 form {
     position: absolute;
-    width: 30%;
+    width: 40%;
     left: 50%;
     top: 50%;
     -webkit-transform: translate(-50%, -50%);
@@ -29,7 +29,7 @@ button {
     width: 100%;
 }
 
-@media only screen and (max-width: 600px) {
+@media only screen and (max-width: 700px) {
     form {
         width: calc(100% - 50px);
     }
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/mottovote/public/style.css b/mottovote/public/style.css
index 1982e0a..90bf0f6 100644
--- a/mottovote/public/style.css
+++ b/mottovote/public/style.css
@@ -52,7 +52,7 @@ select {
     width: 100%;
 }
 
-@media only screen and (max-width: 600px) {
+@media only screen and (max-width: 700px) {
     main {
         width: calc(100% - 50px);
     }
diff --git a/overview/public/index.html b/overview/public/index.html
index c97b83f..f9bc2d8 100644
--- a/overview/public/index.html
+++ b/overview/public/index.html
@@ -15,8 +15,9 @@
     </head>
     <body>
         <div class="pure-menu pure-menu-horizontal">
-            <a href="/auth/change.html" class="pure-menu-item pure-menu-link">Passwort ändern</a>
-            <a href="/auth/api/logout" class="pure-menu-item pure-menu-link">Logout</a>
+            <a href="" class="pure-menu-item pure-menu-link"></a>
+            <a href="" class="pure-menu-item pure-menu-link"></a>
+            <a href="" class="pure-menu-item pure-menu-link"></a>
         </div>
         <div class="card">
             <h2>Hallo, liebe RBS-Schüler*innen!</h2>
@@ -45,5 +46,7 @@
                 <li><a href="https://github.com/marvinborner/Abizeugs/">Öffentlicher Source-Code</a></li>
             </ul>
         </div>
+
+	<script src="script.js"></script>
     </body>
 </html>
diff --git a/overview/public/script.js b/overview/public/script.js
new file mode 100644
index 0000000..be058b7
--- /dev/null
+++ b/overview/public/script.js
@@ -0,0 +1,22 @@
+fetch("/auth/api/status").then(response => response.json()).then(response => {
+	console.log(response);
+	const first = document.querySelectorAll("a")[0];
+	const second = document.querySelectorAll("a")[1];
+	const third = document.querySelectorAll("a")[2];
+
+	if (!response.admin)
+		third.style.display = "none";
+
+	if (response.loggedIn) {
+		first.href = "/auth/change.html";
+		first.innerText = "Passwort ändern";
+		second.href = "/auth/api/logout";
+		second.innerText = "Logout";
+		if (response.admin) {
+		third.href = "/admin";
+		third.innerText = "Administration";
+		}
+	} else {
+		document.querySelectorAll("div.pure-menu")[0].style.display = "none";
+	}
+});
diff --git a/overview/public/style.css b/overview/public/style.css
index 77853bf..16cd26f 100644
--- a/overview/public/style.css
+++ b/overview/public/style.css
@@ -12,7 +12,7 @@ body {
     position: absolute;
     max-height: 80%;
     overflow: auto;
-    width: 30%;
+    width: 40%;
     left: 50%;
     top: 50%;
     -webkit-transform: translate(-50%, -50%);
@@ -26,7 +26,7 @@ div {
     background: white;
 }
 
-@media only screen and (max-width: 600px) {
+@media only screen and (max-width: 700px) {
     .card {
         width: calc(100% - 50px);
     }
diff --git a/poll/public/script.js b/poll/public/script.js
index 8c56894..09f8c93 100644
--- a/poll/public/script.js
+++ b/poll/public/script.js
@@ -29,7 +29,19 @@ fetch("/auth/api/list" + (type == "teacher" ? "?class=teacher" : ""))
     .then((response) => appendOption(response));
 
 fetch("/poll/api/get?type=" + type)
-    .then((response) => response.json())
+    .then(async (response) => {
+        let json;
+        try {
+            return await response.json();
+        } catch (e) {
+            document.querySelector("p").innerText = "";
+            question_label.innerText = "Du hast bereits alle Fragen beantwortet.";
+            document.querySelectorAll("label")[1].innerText = "Danke!";
+            document.querySelector("select").style.display = "none";
+            document.querySelector("button").style.display = "none";
+            throw "Oh nein, alle beantwortet!"; // :^)
+        }
+    })
     .then((response) => {
         question_label.innerText = response["question"];
         question_input.setAttribute("value", response["id"]);
diff --git a/poll/public/style.css b/poll/public/style.css
index 9861f9d..80f9294 100644
--- a/poll/public/style.css
+++ b/poll/public/style.css
@@ -14,7 +14,7 @@ div {
 
 main {
     position: absolute;
-    width: 30%;
+    width: 40%;
     left: 50%;
     top: 50%;
     -webkit-transform: translate(-50%, -50%);
@@ -30,7 +30,7 @@ select {
     width: 100%;
 }
 
-@media only screen and (max-width: 600px) {
+@media only screen and (max-width: 700px) {
     main {
         width: calc(100% - 50px);
     }
diff --git a/quotes/public/style.css b/quotes/public/style.css
index a4e85ea..ae0642b 100644
--- a/quotes/public/style.css
+++ b/quotes/public/style.css
@@ -16,7 +16,7 @@ main {
     position: absolute;
     max-height: 80%;
     overflow-y: auto;
-    width: 30%;
+    width: 40%;
     left: 50%;
     top: 50%;
     -webkit-transform: translate(-50%, -50%);
@@ -54,7 +54,7 @@ select {
     width: 100%;
 }
 
-@media only screen and (max-width: 600px) {
+@media only screen and (max-width: 700px) {
     main {
         width: calc(100% - 50px);
     }
diff --git a/tables.sql b/tables.sql
index 4255c93..94594f7 100644
--- a/tables.sql
+++ b/tables.sql
@@ -31,6 +31,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	Lars Krönner	2020-10-10 18:39:46 +0200
committer	GitHub	2020-10-10 18:39:46 +0200
commit	1f45ede8253421439e07790375b72a31ceef33ed (patch)
tree	a9285cef4e2dc3451ed609be2a34bbf21580c35e
parent	930ecde7e84e723061cba4780459887c329e50a3 (diff)
parent	16ebbb932c0b780c11d3e574bc24a515eb095f5f (diff)