diff options
| -rw-r--r-- | app.js | 2 | ||||
| -rwxr-xr-x | cli.js | 14 | ||||
| -rw-r--r-- | db.js | 20 | ||||
| -rw-r--r-- | drop.sql | 2 | ||||
| -rw-r--r-- | overview/public/index.html | 3 | ||||
| -rw-r--r-- | parse.py | 11 | ||||
| -rw-r--r-- | questions.txt | 2 | ||||
| -rw-r--r-- | questions/index.js | 77 | ||||
| -rw-r--r-- | questions/public/index.html | 44 | ||||
| -rw-r--r-- | questions/public/script.js | 83 | ||||
| -rw-r--r-- | questions/public/style.css | 79 | ||||
| -rw-r--r-- | tables.sql | 164 |
12 files changed, 429 insertions, 72 deletions
@@ -11,6 +11,7 @@ const quotes = require("./quotes"); const poll = require("./poll"); const profile = require("./profile"); const admin = require("./admin"); +const questions = require("./questions"); const app = express(); @@ -37,6 +38,7 @@ app.use("/mottovote", checkUser, mottovote); app.use("/quotes", checkUser, quotes); app.use("/poll", checkUser, poll); app.use("/profile", checkUser, profile); +app.use("/questions", checkUser, questions); app.use("/admin", checkAdmin, admin); // Lel app.use("/auth", auth); @@ -39,6 +39,12 @@ if ((idx = params.indexOf("-r")) > -1) { .then(() => process.exit(0)) .catch(console.error); break; + case "questions": + db.resetQuestions() + .then(() => console.info("Resetted questions!")) + .then(() => process.exit(0)) + .catch(console.error); + break; default: console.info("Nothing to do!"); process.exit(0); @@ -57,6 +63,8 @@ if ((idx = params.indexOf("-r")) > -1) { .then(() => console.info("Updating polls!")) .then(() => db.initProfiles()) .then(() => console.info("Updating profile!")) + .then(() => db.initQuestions()) + .then(() => console.info("Updating Quotes")) .then(() => process.exit(0)) .catch(console.error); @@ -79,6 +87,12 @@ if ((idx = params.indexOf("-r")) > -1) { .then(() => process.exit(0)) .catch(console.error); break; + case "questions": + db.initQuestions() + .then(() => console.info("Updating questions!")) + .then(() => process.exit(0)) + .catch(console.error); + break; default: console.info("Nothing to do!"); process.exit(0); @@ -22,7 +22,7 @@ class DB { for (const table of tables) if (table) await this.query(table); console.info("Database initialized!"); const res = await this.query("SELECT id FROM users"); - if (res.length === 0) this.initValues(); + if (res.length === 0) await this.initValues(); } async initValues() { @@ -34,6 +34,7 @@ class DB { await this.initPolls(); await this.initMottovote(); await this.initProfiles(); + await this.initQuestions(); await this.initUsers(); } @@ -177,6 +178,23 @@ class DB { console.log("Initialized users!"); } + async initQuestions() { + const data = (await fs.readFile(__dirname + "/questions.txt", "utf8")).split("\n"); + for (const q of data) { + await this.query("INSERT INTO question_questions (question) VALUE (?)", [q]) + .catch(() => console.info("Question already exists!")); + } + } + + async resetQuestions() { + const tables = await this.getTables(); + await this.query("DROP TABLE question_answers"); + await this.query("DROP TABLE question_questions"); + await this.query(tables[12]); + await this.query(tables[13]); + await this.initQuestions(); + } + async regenerateUser(uid) { const pwd = nanoid.nanoid(8); const password = await bcrypt.hash(pwd, 10); @@ -7,6 +7,8 @@ DROP TABLE IF EXISTS profile_comments; DROP TABLE IF EXISTS profile_answers; DROP TABLE IF EXISTS profile_questions; DROP TABLE IF EXISTS profile_input_types; +DROP TABLE IF EXISTS question_answers; +DROP TABLE IF EXISTS question_questions; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS types; DROP TABLE IF EXISTS class; diff --git a/overview/public/index.html b/overview/public/index.html index 218dce3..18b1890 100644 --- a/overview/public/index.html +++ b/overview/public/index.html @@ -50,6 +50,9 @@ <a href="/poll?type=teacher" class="pure-menu-link">Lehrer-Ranking</a> </li> <li class="pure-menu-item"> + <a href="/questions" class="pure-menu-link">Fragen</a> + </li> + <li class="pure-menu-item"> <a href="/images" target="_blank" class="pure-menu-link">Klassenbilder</a> </li> </ul> diff --git a/parse.py b/parse.py deleted file mode 100644 index 7181d00..0000000 --- a/parse.py +++ /dev/null @@ -1,11 +0,0 @@ -import json - -users = json.loads(open("./users.json", "r").read()) - -for cid in users: - clazz = users[cid] - s = "Nutzername,Password" - for user in clazz: - s += f"\n{user['username']},{user['pwd']}" - - open(f"./{cid}.csv", "w").write(s) diff --git a/questions.txt b/questions.txt new file mode 100644 index 0000000..fb25a9d --- /dev/null +++ b/questions.txt @@ -0,0 +1,2 @@ +Bist du blöd? +Fällst du durch?
\ No newline at end of file diff --git a/questions/index.js b/questions/index.js new file mode 100644 index 0000000..52a26e9 --- /dev/null +++ b/questions/index.js @@ -0,0 +1,77 @@ +const express = require("express"); +const db = require("../db"); +const app = express.Router(); +const { checkUser } = require("../auth"); + +app.use("/", checkUser, express.static(__dirname + "/public")); + +app.get("/api/question/:id", checkUser, async (req, res) => { + try { + const questions = await db.query( + `SELECT id, question + FROM question_questions` + ); + const id = +req.params.id; + if (id >= 0 && id < questions.length) { + const question = questions[id]; + const answers = await db.query( + `SELECT answer + FROM question_answers + WHERE question_id = ? + AND user_id = ?`, + [question.id, req.session.uid], + ); + question.answer = answers.length > 0 ? answers[0].answer : undefined; + res.json(question); // 😜 + } else { + res.json({}); + } + } catch (e) { + console.error(e); + res.json({ success: false }); + } +}); + +app.get("/api/questions", checkUser, async (req, res) => { + const fail = { success: false }; + try { + const questions = await db.query("SELECT id FROM question_questions"); + const answers = await db.query( + `SELECT question_id + FROM question_answers + WHERE user_id = ?`, + [req.session.uid], + ); + const resp = []; + let i = 0; + for (const question of questions) { + const qid = answers.findIndex((answer) => question.id === answer.question_id); + resp.push({ id: i++, answered: qid >= 0 }); + } + res.json(resp); + } catch (e) { + console.error(e); + res.json(fail); + } +}); + +app.post("/api/answer", checkUser, async (req, res) => { + return await answer(req, res, "INSERT INTO question_answers (answer, question_id, user_id) VALUE (?,?,?)"); +}); + +app.put("/api/answer", checkUser, async (req, res) => { + return await answer(req, res, "UPDATE question_answers SET answer = ? WHERE question_id = ? AND user_id = ?"); +}); + +async function answer(req, res, qu) { + const { question, answer } = req.body; + try { + await db.query(qu, [answer, question, req.session.uid]); + res.json({ success: true }); + } catch (e) { + console.error(e); + res.json({ success: false }); + } +} + +module.exports = app; diff --git a/questions/public/index.html b/questions/public/index.html new file mode 100644 index 0000000..90244f5 --- /dev/null +++ b/questions/public/index.html @@ -0,0 +1,44 @@ +<!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>Fragen</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> + + <main> + <div class="pure-form pure-form-stacked"> + <fieldset> + <legend>Fragen</legend> + <div class="bar" id="progress"></div> + <label id="question_label" for="question"></label> + <input name="question" id="question" hidden /> + <div class="answer-buttons pure-button-group" role="group"> + <button class="pure-button pure-button-primary answer-btn" data-value="1">Ja</button> + <button class="pure-button pure-button-primary answer-btn" data-value="0">Nein</button> + </div> + <br> + <div class="back-skip pure-button-group" role="group"> + <button id="prev-btn" class="pure-button">Zurück</button> + <button id="skip-btn" class="pure-button">Weiter</button> + </div> + </fieldset> + </div> + </main> + + <script src="script.js" charset="utf-8"></script> + </body> +</html> diff --git a/questions/public/script.js b/questions/public/script.js new file mode 100644 index 0000000..70a15ef --- /dev/null +++ b/questions/public/script.js @@ -0,0 +1,83 @@ +const query = new URL(window.location.href).searchParams; +const qid = +query.get("qid") || 0; +let method = "POST"; + +const question_input = document.getElementById("question"); +const question_label = document.getElementById("question_label"); +const prev = document.getElementById("prev-btn"); +const skip = document.getElementById("skip-btn"); +const progress = document.getElementById("progress"); +const buttons = document.querySelectorAll(".answer-btn"); + +skip.addEventListener("click", () => getNext(qid + 1)); +prev.addEventListener("click", () => getNext(qid - 1)); + +if (qid === 0) { + prev.style.display = "none"; + skip.style.width = "100%"; +} + +fetch(`api/question/${qid}`) + .then((response) => response.json()) + .then((response) => { + if (!response.empty()) { + question_label.innerText = response["question"]; + question_input.setAttribute("value", response["id"]); + if (response.answer !== undefined) { + method = "PUT"; + } + document.querySelector(`.answer-btn[data-value="${response.answer}"]`).style.opacity = "0.5"; + } else getNext(); // Resets + }); + +fetch(`api/questions`) + .then((response) => response.json()) + .then((response) => { + for (const elem of response) { + progress.insertAdjacentHTML( + "beforeend", + `<div data-current="${+elem.id === qid}" data-answered="${elem.answered}">${elem.id + 1}</div>`, + ); + } + }) + .then(() => { + document.querySelectorAll("div.bar div").forEach((elem) => { + elem.addEventListener("click", () => { + getNext(+elem.innerText - 1); + }); + }); + }); + +function getNext(q = 0) { + window.location.assign(`/questions/?qid=${q}`); +} + +// I did this myself lel 🤨 +Object.prototype.empty = function () { + return Object.keys(this).length === 0; +}; + +NodeList.prototype.on = function (listener, event) { + for (const node of this) { + node.addEventListener(listener, event); + } +} + +buttons.on("click", async (e) => { + const body = JSON.stringify({ + question: question_input.value, + answer: e.target.dataset.value === "1", + }); + const resp = await fetch(`api/answer`, { + method, + headers: { "Content-Type": "application/json" }, + body, + }); + const res = await resp.json(); + if (res.success) { + method = "PUT"; + getNext(qid); + // document.querySelector(`.answer-btn[data-value="${e.target.dataset.value}"]`).style.opacity = "0.5"; + // document.querySelector(`.answer-btn[data-value="${+!+e.target.dataset.value}"]`).style.opacity = "1"; // Let's not talk about it 😉 + } +});
\ No newline at end of file diff --git a/questions/public/style.css b/questions/public/style.css new file mode 100644 index 0000000..4c395e9 --- /dev/null +++ b/questions/public/style.css @@ -0,0 +1,79 @@ +html, +body { + padding: 0; + margin: 0; + height: 100%; + width: 100%; + color: #424242; + line-height: 1.6; + background-color: #eec0c6; + background-image: linear-gradient(315deg, #eec0c6 0%, #7ee8fa 74%); +} + +div { + background: white; +} + +main { + position: absolute; + width: 50%; + 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%; + color: #424242; +} + +div.bar { + display: flex; + flex-wrap: wrap; +} + +div.bar div { + width: 20px; + height: 20px; + margin: 2px; + padding: 2px; + color: white; + cursor: pointer; + border-radius: 3px; + display: flex; + justify-content: center; + align-items: center; +} + +div.bar div[data-answered="true"] { + background: green; +} + +div.bar div[data-answered="false"] { + background: red; +} + +div.bar div[data-current="true"] { + background: #0078e7; +} + +.back-skip, .answer-buttons { + display: flex; + flex-wrap: nowrap; +} + +.back-skip button { + width: 50%; +} + +@media only screen and (max-width: 700px) { + main { + width: calc(100% - 20%); + } +} @@ -1,111 +1,155 @@ -CREATE TABLE IF NOT EXISTS types( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +CREATE TABLE IF NOT EXISTS types +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS class( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +CREATE TABLE IF NOT EXISTS class +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE IF NOT EXISTS users( - id INTEGER PRIMARY KEY AUTO_INCREMENT, - username VARCHAR(255) NOT NULL UNIQUE, - name VARCHAR(255) NOT NULL, +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; + +CREATE TABLE IF NOT EXISTS users +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + username VARCHAR(255) NOT NULL UNIQUE, + name VARCHAR(255) NOT NULL, middlename VARCHAR(255) DEFAULT NULL, - surname VARCHAR(255) NOT NULL, - password VARCHAR(255) NOT NULL, - class_id INTEGER NOT NULL, - type_id INTEGER NOT NULL, - is_admin BOOLEAN DEFAULT FALSE, + surname VARCHAR(255) NOT NULL, + 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), CONSTRAINT `fk_type_user` FOREIGN KEY (type_id) REFERENCES types (id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS quotes( - id INTEGER PRIMARY KEY AUTO_INCREMENT, - user_id INTEGER NOT NULL, -- Person who heard it - author_id INTEGER NOT NULL, -- Person who said it - quote VARCHAR(255) NOT NULL, +CREATE TABLE IF NOT EXISTS quotes +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + user_id INTEGER NOT NULL, -- Person who heard it + author_id INTEGER NOT NULL, -- Person who said it + quote VARCHAR(255) NOT NULL, UNIQUE KEY uk_quote (author_id, quote), 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; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS ranking_questions( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +CREATE TABLE IF NOT EXISTS ranking_questions +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, question VARCHAR(255) NOT NULL, - type_id INTEGER 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; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS ranking_answers( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +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 + 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; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS mottos( - id INTEGER PRIMARY KEY AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, +CREATE TABLE IF NOT EXISTS mottos +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + name VARCHAR(255) NOT NULL, description VARCHAR(255) NOT NULL DEFAULT '', UNIQUE KEY main (name, description) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS motto_votes( - id INTEGER PRIMARY KEY AUTO_INCREMENT, - user_id INTEGER NOT NULL, - motto_id INTEGER NOT NULL, - votes SMALLINT UNSIGNED NOT NULL DEFAULT 0, +CREATE TABLE IF NOT EXISTS motto_votes +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + user_id INTEGER NOT NULL, + motto_id INTEGER NOT NULL, + votes SMALLINT UNSIGNED NOT NULL DEFAULT 0, UNIQUE KEY uk_vote (user_id, motto_id), CONSTRAINT `fk_voted_user` FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT `fk_voted_vote` FOREIGN KEY (motto_id) REFERENCES mottos (id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS profile_input_types( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +CREATE TABLE IF NOT EXISTS profile_input_types +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, type VARCHAR(20) NOT NULL UNIQUE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS profile_questions( - id INTEGER PRIMARY KEY AUTO_INCREMENT, - question VARCHAR(255) NOT NULL UNIQUE, - question_type INTEGER NOT NULL, +CREATE TABLE IF NOT EXISTS profile_questions +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + question VARCHAR(255) NOT NULL UNIQUE, + question_type INTEGER NOT NULL, CONSTRAINT `fk_profile_question_type` FOREIGN KEY (question_type) REFERENCES profile_input_types (id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS profile_answers( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +CREATE TABLE IF NOT EXISTS profile_answers +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, question_id INTEGER NOT NULL, - user_id INTEGER NOT NULL, - answer TEXT NULL, -- Consider VARCHAR + user_id INTEGER NOT NULL, + answer TEXT NULL, -- Consider VARCHAR UNIQUE KEY uk_answer (question_id, user_id), CONSTRAINT `fk_profile_user` FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT `fk_profile_question` FOREIGN KEY (question_id) REFERENCES profile_questions (id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; -CREATE TABLE IF NOT EXISTS profile_comments( - id INTEGER PRIMARY KEY AUTO_INCREMENT, +CREATE TABLE IF NOT EXISTS profile_comments +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, profile_id INTEGER NOT NULL, -- User's profile - user_id INTEGER NOT NULL, -- User who commented - comment TEXT NOT NULL, + user_id INTEGER NOT NULL, -- User who commented + comment TEXT NOT NULL, CONSTRAINT `fk_user_profile` FOREIGN KEY (profile_id) REFERENCES users (id), CONSTRAINT `fk_user_commenter` FOREIGN KEY (user_id) REFERENCES users (id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\ No newline at end of file +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; + +CREATE TABLE IF NOT EXISTS question_questions +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + question VARCHAR(255) NOT NULL UNIQUE +) ENGINE = InnoDB + DEFAULT CHARSET = utf8; + +CREATE TABLE IF NOT EXISTS question_answers +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + question_id INTEGER NOT NULL, + user_id INTEGER NOT NULL, + answer BOOLEAN NULL, + + UNIQUE KEY uk_answer (question_id, user_id), + CONSTRAINT `fk_question_user` FOREIGN KEY (user_id) REFERENCES users (id), + CONSTRAINT `fk_question_question` FOREIGN KEY (question_id) REFERENCES question_questions (id) +) ENGINE = InnoDB + DEFAULT CHARSET = utf8;
\ No newline at end of file |