From 4e9154b12ea3ddd9da32d2a4d1ebc30f13678c59 Mon Sep 17 00:00:00 2001
From: LarsVomMars
Date: Wed, 7 Oct 2020 23:42:34 +0200
Subject: User profile boilerplate

---
 tables.sql | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

(limited to 'tables.sql')

diff --git a/tables.sql b/tables.sql
index e0bc160..b2592bc 100644
--- a/tables.sql
+++ b/tables.sql
@@ -4,6 +4,9 @@
 -- DROP TABLE IF EXISTS quotes;
 -- DROP TABLE IF EXISTS ranking_questions;
 -- DROP TABLE IF EXISTS ranking_answers;
+-- DROP TABLE IF EXISTS profile_comments;
+-- DROP TABLE IF EXISTS profile_answers;
+-- DROP TABLE IF EXISTS profile_questions;
 -- DROP TABLE IF EXISTS users;
 -- DROP TABLE IF EXISTS types;
 -- DROP TABLE IF EXISTS class;
@@ -84,3 +87,29 @@ CREATE TABLE IF NOT EXISTS motto_votes(
     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;
+
+CREATE TABLE IF NOT EXISTS profile_questions(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    question VARCHAR(255) NOT NULL UNIQUE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+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
+
+    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;
+
+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,
+
+    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
-- 
cgit v1.2.3


From 44dd49a479e0b1a034f87317c910cd693ee7f8f9 Mon Sep 17 00:00:00 2001
From: LarsVomMars
Date: Sat, 10 Oct 2020 18:36:57 +0200
Subject: Answer types

I think
---
 .gitignore                      |  1 +
 db.js                           | 19 +++++++-------
 package.json                    |  1 +
 profile.txt                     |  2 +-
 profile/index.js                | 55 +++++++++++++++++++++++++++++++++--------
 profile/public/script.js        | 27 +++++++++++++-------
 profile/public/style.css        |  7 +++++-
 profile/public/uploads/.gitkeep |  0
 tables.sql                      | 11 ++++++++-
 9 files changed, 91 insertions(+), 32 deletions(-)
 create mode 100644 profile/public/uploads/.gitkeep

(limited to 'tables.sql')

diff --git a/.gitignore b/.gitignore
index 56cb957..113c313 100644
--- a/.gitignore
+++ b/.gitignore
@@ -4,3 +4,4 @@ node_*
 *.env
 *.csv
 users.json
+profile/public/uploads/**/*
diff --git a/db.js b/db.js
index 31cf795..b16364a 100644
--- a/db.js
+++ b/db.js
@@ -40,6 +40,10 @@ class DB {
                 "INSERT INTO class (name) VALUES ('TGM13.1'), ('TGM13.2'), ('TGTM13.1'), ('TGI13.1'), ('TGI13.2'), ('teacher')",
             );
 
+
+            const types = ["number", "file", "date", "text", "color"];
+            await this.query("INSERT INTO profile_input_types (type) VALUES (?), (?), (?), (?), (?)", types);
+
             // User polls
             fs.readFile(__dirname + "/poll.txt", "utf8", (err, data) => {
                 if (err) throw err;
@@ -68,21 +72,16 @@ class DB {
                 });
             });
 
+            // User profile
             fs.readFile(__dirname + "/profile.txt", "utf8", (err, data) => {
                 if (err) throw err;
 
                 const questions = data.split("\n");
                 questions.forEach((question) => {
-                    if (question) this.query("INSERT INTO profile_questions (question) VALUE (?)", [question]);
-                });
-            });
-
-            fs.readFile(__dirname + "/profile.txt", "utf8", (err, data) => {
-                if (err) throw err;
-
-                const questions = data.split("\n");
-                questions.forEach((question) => {
-                    if (question) this.query("INSERT INTO profile_questions (question) VALUE (?)", [question]);
+                    if (question) {
+                        const [q, type] = question.split(" - ");
+                        this.query("INSERT INTO profile_questions (question, question_type) VALUE (?, ?)", [q, types.indexOf(type) + 1]);
+                    }
                 });
             });
 
diff --git a/package.json b/package.json
index 1775768..d1525be 100644
--- a/package.json
+++ b/package.json
@@ -10,6 +10,7 @@
     "connect-redis": "^5.0.0",
     "dotenv": "^8.2.0",
     "express": "^4.17.1",
+    "express-fileupload": "^1.2.0",
     "express-rate-limit": "^5.1.3",
     "express-session": "^1.17.1",
     "mariadb": "^2.4.2",
diff --git a/profile.txt b/profile.txt
index dd79092..58b3deb 100644
--- a/profile.txt
+++ b/profile.txt
@@ -1 +1 @@
-Alter?
\ No newline at end of file
+Was ist dein Alter? - number
\ No newline at end of file
diff --git a/profile/index.js b/profile/index.js
index f5e8373..1c5752f 100644
--- a/profile/index.js
+++ b/profile/index.js
@@ -1,10 +1,13 @@
 const express = require("express");
 const db = require("../db");
+const fileupload = require("express-fileupload");
 const app = express.Router();
 
+app.use(fileupload({}));
+
 app.use("/", express.static(__dirname + "/public/"));
 
-app.get("/user/:uid", async (req, res) => {});
+app.get("/user/:uid", async (req, res) => { });
 
 // Basic API
 app.get("/api/user", async (req, res) => {
@@ -13,7 +16,7 @@ app.get("/api/user", async (req, res) => {
 });
 
 app.get("/api/questions", async (req, res) => {
-    const questions = await db.query("SELECT id, question FROM profile_questions");
+    const questions = await db.query("SELECT q.id, q.question, t.type FROM profile_questions q INNER JOIN profile_input_types t ON t.id = q.question_type");
     const answers = await db.query("SELECT answer, question_id FROM profile_answers WHERE user_id = ?", [
         req.session.uid,
     ]);
@@ -28,11 +31,27 @@ app.get("/api/questions", async (req, res) => {
 app.post("/api/add", async (req, res) => {
     try {
         for (let qid in req.body) {
-            if (!req.body.hasOwnProperty(qid)) continue;
+            if (!req.body.hasOwnProperty(qid) || req.body[qid] === "dbg-image") continue;
+            let answer = req.body[qid].replace(/</g, "&lt;").replace(/>/g, "&gt;");
+            await db.query("INSERT INTO profile_answers (question_id, user_id, answer) VALUES (?, ?, ?)", [
+                qid,
+                req.session.uid,
+                answer
+            ]);
+        }
+        for (let fid in req.files) {
+            if (!req.files.hasOwnProperty(fid)) return;
+
+            let image, imageType, imageName;
+
+            image = req.files[fid];
+            imageType = image.name.split(".").reverse()[0];
+            imageName = `${req.session.uid}_${(new Date()).getTime()}.${imageType}`;
+            image.mv(__dirname + "/public/uploads/" + imageName);
             await db.query("INSERT INTO profile_answers (question_id, user_id, answer) VALUES (?, ?, ?)", [
                 qid,
                 req.session.uid,
-                req.body[qid].replace(/</g, "&lt;").replace(/>/g, "&gt;"),
+                imageName,
             ]);
         }
         res.send("ok");
@@ -45,13 +64,29 @@ app.post("/api/add", async (req, res) => {
 app.put("/api/update", async (req, res) => {
     try {
         for (let qid in req.body) {
-            if (!req.body.hasOwnProperty(qid)) continue;
+            if (!req.body.hasOwnProperty(qid) || req.body[qid] === "dbg-image") continue;
+            let answer = req.body[qid].replace(/</g, "&lt;").replace(/>/g, "&gt;");
             await db.query("UPDATE profile_answers SET answer = ? WHERE question_id = ? AND user_id = ?", [
-                req.body[qid].replace(/</g, "&lt;").replace(/>/g, "&gt;"),
+                answer,
                 qid,
                 req.session.uid,
             ]);
         }
+        for (let fid in req.files) {
+            if (!req.files.hasOwnProperty(fid)) return;
+
+            let image, imageType, imageName;
+
+            image = req.files[fid];
+            imageType = image.name.split(".").reverse()[0];
+            imageName = `${req.session.uid}_${(new Date()).getTime()}.${imageType}`;
+            image.mv(__dirname + "/public/uploads/" + imageName);
+            await db.query("UPDATE profile_answers SET answer = ? WHERE question_id = ? AND user_id = ?", [
+                imageName,
+                fid,
+                req.session.uid,
+            ]);
+        }
         res.send("ok");
     } catch (e) {
         console.error(e);
@@ -60,12 +95,12 @@ app.put("/api/update", async (req, res) => {
 });
 
 // Comments API
-app.get("/api/comments/:uid", async (req, res) => {});
+app.get("/api/comments/:uid", async (req, res) => { });
 
-app.post("/api/comment", async (req, res) => {});
+app.post("/api/comment", async (req, res) => { });
 
-app.put("/api/comment", async (req, res) => {});
+app.put("/api/comment", async (req, res) => { });
 
-app.delete("/api/comment", async (req, res) => {});
+app.delete("/api/comment", async (req, res) => { });
 
 module.exports = app;
diff --git a/profile/public/script.js b/profile/public/script.js
index b0434fa..a386f56 100644
--- a/profile/public/script.js
+++ b/profile/public/script.js
@@ -12,6 +12,14 @@ function appendQuestions(question) {
     const label = document.createElement("label");
     label.for = "id_" + question.id;
     label.textContent = question.question;
+    div.appendChild(label);
+
+    if (question.type === "file" && question.answer) {
+        const img = document.createElement("img");
+        img.src = "uploads/" + question.answer;
+        img.alt = "Image";
+        div.appendChild(img);
+    }
 
     const field = document.createElement("input");
     field.id = "id_" + question.id;
@@ -19,8 +27,10 @@ function appendQuestions(question) {
     if (question.answer !== undefined) init = false;
     field.value = question.answer;
     field.placeholder = question.question;
+    field.type = question.type;
+    if (question.type === "file") field.accept = "image/*";
 
-    div.append(label, field);
+    div.appendChild(field);
     fs.insertBefore(div, fs.querySelector("button"));
 }
 
@@ -30,14 +40,13 @@ form.addEventListener("submit", async (evt) => {
     const method = init ? "POST" : "PUT";
 
     const inputs = form.querySelectorAll("input");
-    const body = {};
-    for (const input of inputs) body[input.name] = input.value;
-
-    const resp = await fetch(url, {
-        headers: { "Content-Type": "application/json" },
-        method,
-        body: JSON.stringify(body),
-    });
+    const body = new FormData();
+    for (const input of inputs) {
+        if (input.type !== "file") body.append(input.name, input.value);
+        else body.append(input.name, input.files[0] ?? "dbg-image");
+    }
+
+    const resp = await fetch(url, { method, body });
     const res = await resp.text();
     if (res !== "ok") alert("AHHHH");
 });
diff --git a/profile/public/style.css b/profile/public/style.css
index 2e7b945..e674e71 100644
--- a/profile/public/style.css
+++ b/profile/public/style.css
@@ -16,7 +16,7 @@ main {
     position: absolute;
     max-height: 80%;
     overflow-y: auto;
-    width: 30%;
+    width: 50%;
     left: 50%;
     top: 50%;
     -webkit-transform: translate(-50%, -50%);
@@ -32,6 +32,11 @@ select {
     width: 100%;
 }
 
+img {
+    max-width: 80%;
+    max-height: 80%;
+}
+
 @media only screen and (max-width: 600px) {
     main {
         width: calc(100% - 50px);
diff --git a/profile/public/uploads/.gitkeep b/profile/public/uploads/.gitkeep
new file mode 100644
index 0000000..e69de29
diff --git a/tables.sql b/tables.sql
index b2592bc..4255c93 100644
--- a/tables.sql
+++ b/tables.sql
@@ -7,6 +7,7 @@
 -- 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 users;
 -- DROP TABLE IF EXISTS types;
 -- DROP TABLE IF EXISTS class;
@@ -88,9 +89,17 @@ CREATE TABLE IF NOT EXISTS motto_votes(
     CONSTRAINT `fk_voted_vote` FOREIGN KEY (motto_id) REFERENCES mottos (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+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;
+
 CREATE TABLE IF NOT EXISTS profile_questions(
     id INTEGER PRIMARY KEY AUTO_INCREMENT,
-    question VARCHAR(255) NOT NULL UNIQUE
+    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;
 
 CREATE TABLE IF NOT EXISTS profile_answers(
-- 
cgit v1.2.3

