Schedules_time.js
2.86 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
const express = require("express");
const { pool } = require("../libs/MySQL");
const schedules_timeRouter = express.Router();
async function route() {
// (userID, date) => schedules
schedules_timeRouter.get("/", async (req, res) => {
// console.log("/db/schedules_time");
const connection = await pool.getConnection(async (con) => con);
try {
const queryString = `
SELECT sc.label, sc.type, sc.description, sc.url, sc.detail, sbj.name, us.nickname,
us.color, sc.uid, sc.startTime, sc.endTime, "schedules_time" \`table\`
FROM schedules_time sc
INNER JOIN \`user-subject\` us
ON sc.userID = us.userID
AND sc.subjectID = us.subjectID
AND us.status = 1
INNER JOIN subjects sbj
ON sc.subjectID = sbj.ID
WHERE sc.date = "${req.query.date}"
AND sc.userID = ${req.query.userID}
AND sc.status = 1
ORDER BY sc.endTime`;
const [results] = await connection.query(queryString);
res.send(results);
} catch (e) {
console.log(e);
res.end();
} finally {
connection.release();
}
});
// (userID, label, subjectID, type, description, url, date, startTime, endTime)
schedules_timeRouter.post("/", async (req, res) => {
console.log("post /db/schedules_time");
const connection = await pool.getConnection(async (con) => con);
try {
await connection.beginTransaction();
const startTime = req.body.startTime ? `"${req.body.startTime}"` : "null";
const endTime = req.body.endTime ? `"${req.body.endTime}"` : "null";
let queryString = `
INSERT INTO \`schedules_time\`
(userID, label, subjectID, type, description, url, date, status, startTime, endTime)
VALUES (${req.body.userID}, "${req.body.label}", ${req.body.subjectID}, "${req.body.type}", "${req.body.description}",
"${req.body.url}", "${req.body.date}", 1, ${startTime}, ${endTime});`;
await connection.query(queryString);
queryString = `
UPDATE schedules_time
SET uid = -ID
WHERE ID = LAST_INSERT_ID()`;
await connection.query(queryString);
queryString = `SELECT sc.label, sc.type, sc.description, sc.url, sc.detail, sbj.name, us.nickname,
us.color, sc.uid, sc.startTime, sc.endTime, "schedules_time" \`table\`
FROM schedules_time sc
INNER JOIN \`user-subject\` us
ON sc.userID = us.userID
AND sc.subjectID = us.subjectID
AND us.status = 1
INNER JOIN subjects sbj
ON sc.subjectID = sbj.ID
WHERE sc.ID = LAST_INSERT_ID()
ORDER BY sc.endTime`;
[results] = await connection.query(queryString);
await connection.commit();
res.send(results[0]);
} catch (e) {
console.log(e);
await connection.rollback();
res.end();
} finally {
connection.release();
}
});
}
route();
module.exports = schedules_timeRouter;