server.js
6.97 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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
const express = require('express')
const app = express()
const port = 3001 // react의 기본값은 3000이니까 3000이 아닌 아무 수
const cors = require("cors");
const bodyParser = require("body-parser");
const route = require('./routes/index')
const mysql = require("mysql");
let moment = require('moment');
//db연동 위해
var connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "sotnadlrkxdlgo",
database: "nodejs"
})
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(cors());
app.use('/', route);
app.get('/', (req, res) => {
res.send('코딩 중!')
})
// data가 0. 맑음
app.post("/database0", (req, res) => {
let month = moment().month() + 1;
console.log("현재 월 : ", month);
if (3 <= month && month <= 5) { // 맑고 봄
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, -rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="봄") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(-rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="봄") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('맑고 봄이네요 : ', results);
});
}
else if (6 <= month && month <= 8){
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, -rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="여름") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(-rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="여름") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('맑고 여름이네요 : ', results);
});
}
else if (9 <= month && month <= 11){
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, -rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="가을") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(-rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="가을") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('맑고 가을이네요 : ', results);
});
}
else {
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, -rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="겨울") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(-rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="겨울") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('맑고 겨울이네요 : ', results);
});
}
})
// data가 0이 아님. 비/눈
app.post("/database1", (req, res) => {
let month = moment().month() + 1;
console.log("현재 월 : ", month);
if (3 <= month && month <= 5) { // 비오고 봄
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="봄") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="봄") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('비/눈오고 봄이네요 : ', results);
});
}
else if (6 <= month && month <= 8){
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="여름") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="여름") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('비/눈오고 여름이네요 : ', results);
});
}
else if (9 <= month && month <= 11){
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="가을") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="가을") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('비/눈오고 가을이네요 : ', results);
});
}
else {
connection.query('SELECT * FROM (SELECT IFNULL(rain.menu, seasons.menu) AS menu, rain.percent + IFNULL(seasons.percent, 0) as sum_percent FROM rain LEFT JOIN (SELECT menu, percent FROM seasons WHERE season="겨울") seasons ON rain.menu = seasons.menu UNION SELECT IFNULL(rain.menu, seasons.menu) AS menu, IFNULL(rain.percent, 0)+seasons.percent FROM rain RIGHT JOIN (SELECT menu, percent FROM seasons WHERE season="겨울") seasons ON rain.menu = seasons.menu ORDER BY sum_percent DESC LIMIT 12) subtable ORDER BY rand() limit 5;', (error, results, field) => {
if (error) throw error;
res.send(results)
console.log('비/눈오고 겨울이네요 : ', results);
});
}
})
app.listen(port, () => {
console.log(`Connect at http://localhost:${port}`); // '가 아닌 좌측상단의 esc버튼 밑의 `다.
})