From e52bcb760f36b68495692ac5c5a5b68e8dafc33b Mon Sep 17 00:00:00 2001
From: Marvin Borner
Date: Fri, 2 Oct 2020 21:12:23 +0200
Subject: Added polls

---
 .env.example               |  1 +
 app.js                     |  4 +++-
 db.js                      | 10 ++++++++++
 overview/public/index.html |  1 +
 poll.txt                   | 34 ++++++++++++++++++++++++++++++++++
 poll/index.js              | 34 ++++++++++++++++++++++++++++++++++
 poll/public/index.html     | 35 +++++++++++++++++++++++++++++++++++
 poll/public/script.js      | 40 ++++++++++++++++++++++++++++++++++++++++
 poll/public/style.css      | 35 +++++++++++++++++++++++++++++++++++
 quotes/index.js            |  2 +-
 tables.sql                 | 31 +++++++++++++++++++++++++++----
 11 files changed, 221 insertions(+), 6 deletions(-)
 create mode 100644 poll.txt
 create mode 100644 poll/index.js
 create mode 100644 poll/public/index.html
 create mode 100644 poll/public/script.js
 create mode 100644 poll/public/style.css

diff --git a/.env.example b/.env.example
index dd6613d..ef55330 100644
--- a/.env.example
+++ b/.env.example
@@ -2,3 +2,4 @@ DBHost=
 DBName=
 DBUser=
 DBPassword=
+sessionSecret=
diff --git a/app.js b/app.js
index adda55c..23e2715 100644
--- a/app.js
+++ b/app.js
@@ -5,6 +5,7 @@ const session = require("express-session");
 const { auth, checkUser } = require("./auth");
 const motto = require("./motto");
 const quotes = require("./quotes");
+const poll = require("./poll");
 
 const app = express();
 
@@ -15,7 +16,7 @@ const redisClient = redis.createClient();
 app.use(
     session({
         store: new RedisStore({ client: redisClient }),
-        secret: "keyboard cat",
+        secret: process.env.sessionSecret,
         resave: false,
         saveUninitialized: true,
         cookie: { secure: false },
@@ -28,6 +29,7 @@ app.use(express.json());
 app.use("/", express.static(__dirname + "/overview/public"));
 app.use("/motto", checkUser, motto);
 app.use("/quotes", checkUser, quotes);
+app.use("/poll", checkUser, poll);
 app.use("/auth", auth);
 
 app.listen(5005, () => console.log("Server started on http://localhost:5005"));
diff --git a/db.js b/db.js
index da082dc..58673bc 100644
--- a/db.js
+++ b/db.js
@@ -40,6 +40,16 @@ class DB {
                 "INSERT INTO class (name) VALUES ('TGM13.1'), ('TGM13.2'), ('TGTM13.1'), ('TGI13.1'), ('TGI13.2')"
             );
 
+            fs.readFile(__dirname + "/poll.txt", "utf8", (err, data) => {
+                if (err) throw err;
+
+                const questions = data.split("\n");
+                questions.forEach((question) => {
+                    if (question.length > 0)
+                        this.query("INSERT INTO ranking_questions (question, type_id) VALUE (?,?)", [question, 2]);
+                });
+            });
+
             const classes = data.split("--");
             const userPasswords = {};
             console.log("Generating users");
diff --git a/overview/public/index.html b/overview/public/index.html
index 82d5fac..bc44530 100644
--- a/overview/public/index.html
+++ b/overview/public/index.html
@@ -27,6 +27,7 @@
             <ul>
                 <li><a href="/motto">Motto Wähler</a></li>
                 <li><a href="/quotes">Zitate</a></li>
+                <li><a href="/poll">Schüler-Ranking</a></li>
             </ul>
             <p>
                 Falls ihr uns bei der Entwicklung helfen wollt, Fehler gefunden habt oder Fragen an uns habt, könnt ihr
diff --git a/poll.txt b/poll.txt
new file mode 100644
index 0000000..19583ae
--- /dev/null
+++ b/poll.txt
@@ -0,0 +1,34 @@
+...wohnt bei 30 noch bei Mama?
+...schläft im Unterricht immer?
+...landet am ehesten im Knast?
+...ist der größte Pumper?
+...hat die schrägste Lache?
+...ist der Lehrerliebling?
+...stellt die blödesten Fragen?
+...korrigiert die meisten Lehrerfehler?
+...ist am meisten in sein Handy verliebt?
+...später Millionär*in?
+...hat den besten Style?
+...hat die schönsten Heftaufschriebe?
+...findet die besten Ausreden?
+...macht die besten Witze?
+...wird als erstes heiraten?
+...hat die größte Sauklaue?
+...öffnet zuerst eine eigene Firma?
+...ist nie krank?
+...hat immer gute Laune?
+...ist die größte Labertasche?
+...ist der Albtraum aller Lehrer?
+...ist ein tiefes, stilles Wasser?
+...heult trotz 14 Punkten?
+...isst durchgehend?
+...ist der größte Schleimer?
+...kommt immer zu spät?
+...ist der größte Schnorrer?
+...ist "dauerdicht"?
+...ist die schönste/hübscheste Person?
+...ist der größte Streber?
+...ist durchgehend krank?
+...schwänzt am meisten?
+...macht nie Hausaufgaben?
+...feiert am meisten?
diff --git a/poll/index.js b/poll/index.js
new file mode 100644
index 0000000..ab9ee6f
--- /dev/null
+++ b/poll/index.js
@@ -0,0 +1,34 @@
+const express = require("express");
+const db = require("../db");
+const app = express.Router();
+const { checkUser } = require("../auth");
+
+app.use("/", checkUser, express.static(__dirname + "/public"));
+
+app.post("/api/answer", checkUser, async (req, res) => {
+    if (!req.body.answer || !req.body.question) return res.send("error");
+    try {
+        await db.query("INSERT INTO ranking_answers (question_id, user_id, answer_id) VALUE (?,?,?)", [
+            parseInt(req.body.question),
+            req.session.uid,
+            parseInt(req.body.answer),
+        ]);
+        res.redirect("/poll");
+    } catch (e) {
+        console.error(e);
+        res.json("error");
+    }
+});
+
+app.get("/api/get", checkUser, async (req, res) => {
+    // TODO: Add teacher questions
+    const question = (
+        await db.query(
+            "SELECT q.id, q.question, t.name FROM ranking_questions AS q INNER JOIN types AS t ON type_id = t.id WHERE q.id NOT IN (SELECT question_id FROM ranking_answers WHERE user_id = ?) AND t.name = 'pupil' LIMIT 1",
+            [req.session.uid]
+        )
+    )[0];
+    res.json(question);
+});
+
+module.exports = app;
diff --git a/poll/public/index.html b/poll/public/index.html
new file mode 100644
index 0000000..8530b76
--- /dev/null
+++ b/poll/public/index.html
@@ -0,0 +1,35 @@
+<!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>Schüler-Ranking</title>
+    </head>
+    <body>
+        <div>
+            <form class="pure-form pure-form-stacked" action="api/answer" method="post">
+                <fieldset>
+                    <!-- TODO: Add progress -->
+                    <legend>Schüler-Ranking</legend>
+                    <p>Welche/r Schüler/in...</p>
+                    <label id="question_label" for="question"></label>
+                    <input name="question" id="question" hidden></input>
+                    <br/>
+                    <label for="answer">Antwort</label>
+                    <select name="answer" id="answer" required></select>
+                    <button type="submit" class="pure-button pure-button-primary">Antworten</button>
+                </fieldset>
+            </form>
+        </div>
+
+        <script src="script.js" charset="utf-8"></script>
+    </body>
+</html>
diff --git a/poll/public/script.js b/poll/public/script.js
new file mode 100644
index 0000000..a42777f
--- /dev/null
+++ b/poll/public/script.js
@@ -0,0 +1,40 @@
+const dropdown = document.getElementById("answer");
+const question_input = document.getElementById("question");
+const question_label = document.getElementById("question_label");
+
+dropdown.insertAdjacentHTML("beforeend", '<option selected="true" disabled>Schüler/in auswählen...</option>');
+
+function appendOption(response) {
+    response.forEach((elem) => {
+        dropdown.insertAdjacentHTML(
+            "beforeend",
+            `<option value="${elem["id"]}">${elem["name"]} ${elem["middlename"] ? elem["middlename"] : " "}${
+                elem["surname"]
+            }</option>`
+        );
+    });
+}
+
+function appendQuote(response) {
+    response.forEach((elem) => {
+        document
+            .getElementById(elem["class"])
+            .insertAdjacentHTML(
+                "beforeend",
+                `<li>${elem["name"]} ${elem["middlename"] ? elem["middlename"] : " "}${elem["surname"]}: ${
+                    elem["quote"]
+                }</li>`
+            );
+    });
+}
+
+fetch("/auth/api/list")
+    .then((response) => response.json())
+    .then((response) => appendOption(response));
+
+fetch("/poll/api/get")
+    .then((response) => response.json())
+    .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
new file mode 100644
index 0000000..2c9c695
--- /dev/null
+++ b/poll/public/style.css
@@ -0,0 +1,35 @@
+html,
+body {
+    padding: 0;
+    margin: 0;
+    height: 100%;
+    width: 100%;
+    background-color: #eec0c6;
+    background-image: linear-gradient(315deg, #eec0c6 0%, #7ee8fa 74%);
+}
+
+div {
+    position: absolute;
+    max-height: 80%;
+    overflow-y: scroll;
+    width: 30%;
+    left: 50%;
+    top: 50%;
+    -webkit-transform: translate(-50%, -50%);
+    transform: translate(-50%, -50%);
+    padding: 20px;
+    border-radius: 10px;
+    background: white;
+}
+
+input,
+button,
+select {
+    width: 100%;
+}
+
+@media only screen and (max-width: 600px) {
+    div {
+        width: calc(100% - 50px);
+    }
+}
diff --git a/quotes/index.js b/quotes/index.js
index ad9fa97..c0014c6 100644
--- a/quotes/index.js
+++ b/quotes/index.js
@@ -9,7 +9,7 @@ app.post("/api/add", checkUser, async (req, res) => {
     if (!req.body.author || !req.body.quote) return res.send("error");
     try {
         await db.query("INSERT INTO quotes (user_id, author_id, quote) VALUE (?,?,?)", [
-            req.session.uid, // TODO: Add actual user identification
+            req.session.uid,
             parseInt(req.body.author),
             req.body.quote,
         ]);
diff --git a/tables.sql b/tables.sql
index 516db23..6161b03 100644
--- a/tables.sql
+++ b/tables.sql
@@ -9,10 +9,12 @@ CREATE TABLE IF NOT EXISTS theme(
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 -- TODO: Remove dropping
--- DROP TABLE IF EXISTS quotes;
--- DROP TABLE IF EXISTS users;
--- DROP TABLE IF EXISTS types;
--- DROP TABLE IF EXISTS class;
+DROP TABLE IF EXISTS quotes;
+DROP TABLE IF EXISTS ranking_questions;
+DROP TABLE IF EXISTS ranking_answers;
+DROP TABLE IF EXISTS users;
+DROP TABLE IF EXISTS types;
+DROP TABLE IF EXISTS class;
 
 CREATE TABLE IF NOT EXISTS types(
     id INTEGER PRIMARY KEY AUTO_INCREMENT,
@@ -49,3 +51,24 @@ CREATE TABLE IF NOT EXISTS quotes(
     CONSTRAINT `fk_user_quote1` FOREIGN KEY (user_id) REFERENCES users (id),
     CONSTRAINT `fk_user_quote2` FOREIGN KEY (author_id) REFERENCES users (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS ranking_questions(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    question VARCHAR(255) NOT NULL,
+    type_id INTEGER NOT NULL,
+
+    UNIQUE KEY uk_question (question, type_id),
+    CONSTRAINT `fk_type_question` FOREIGN KEY (type_id) REFERENCES types (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS ranking_answers(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    question_id INTEGER NOT NULL,
+    user_id INTEGER NOT NULL, -- Submitter
+    answer_id INTEGER NOT NULL, -- Selected pupil
+
+    UNIQUE KEY uk_answer (question_id, user_id),
+    CONSTRAINT `fk_question_answer` FOREIGN KEY (question_id) REFERENCES users (id),
+    CONSTRAINT `fk_user_answer1` FOREIGN KEY (user_id) REFERENCES users (id),
+    CONSTRAINT `fk_user_answer2` FOREIGN KEY (answer_id) REFERENCES users (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 
cgit v1.2.3