server.js
7.54 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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
const express = require('express');
const app = express();
const PORT = process.env.PORT || 4000;
const db = require('./config/db');
app.get('/api/host', (req, res) => {
res.send({ host : 'Rekrow' });
})
app.get('/api/home_most_epic', (req, res) => {
db.query("SELECT * FROM (SELECT ROW_NUMBER() over(order by temp.value desc)NUM, PICTURE,NAME, VALUE FROM (SELECT itemId, count(itemId) AS VALUE, date(droped_at) FROM item_drop_exception WHERE date(droped_at) = DATE(NOW()) GROUP BY itemid) temp join item_exception ie ON ie.itemId = temp.itemid) t WHERE t.NUM <=5", (err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
app.get('/api/home_most_channel', (req, res) => {
db.query("SELECT * from(SELECT * , ROW_NUMBER() over(order by temp.value desc)NUM from(select channel_name, channel_number, count(itemId) AS VALUE FROM item_drop_exception WHERE date(droped_at) = DATE(NOW()) GROUP BY CHANNEL_Name, Channel_NUMBER) temp) t WHERE num<=5",
(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
app.get('/api/most_channel', (req, res) => {
db.query("SELECT channel_name, channel_number, count(itemId) AS VALUE, ROW_NUMBER() OVER (ORDER BY value desc) as NUM FROM item_drop_exception WHERE date(droped_at) = DATE(NOW()) GROUP BY CHANNEL_Name, Channel_NUMBER order BY VALUE desc",
(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
app.get('/api/week_most_channel', (req, res) => {
db.query("SELECT *, ROW_NUMBER() OVER (ORDER BY T.value desc) as NUM FROM(SELECT channel_name, channel_number,COUNT(id) AS VALUE FROM item_drop_exception WHERE droped_at BETWEEN DATE_ADD(NOW(),INTERVAL - 1 week) AND NOW() GROUP BY CHANNEL_NAME, CHANNEL_NUMBER) T",
(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
app.get('/api/most_epic', (req, res) => {
db.query("SELECT NAME, PICTURE,VALUE, ROW_NUMBER() OVER (ORDER BY value desc) as NUM FROM (SELECT itemId, count(itemId) AS VALUE, date(droped_at) FROM item_drop_exception WHERE date(droped_at) = DATE(NOW()) GROUP BY itemid) temp join item_exception ie ON ie.itemId = temp.itemid",
(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
app.get('/api/week_most_epic', (req, res) => {
db.query("SELECT NAME, PICTURE,VALUE, ROW_NUMBER() OVER (ORDER BY T.value desc) as NUM FROM(SELECT itemId, COUNT(itemId) AS VALUE FROM item_drop_exception WHERE droped_at BETWEEN DATE_ADD(NOW(),INTERVAL - 1 week) AND NOW() GROUP BY itemId) T JOIN item_exception i ON i.itemId = T.itemId",
(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
//search data
app.get('/api/search_data/:searchTag', (req, res) => {
const searchTag = req.params.searchTag;
//console.log(searchTag);
res.json({searchTag});
})
app.get('/api/getImage/:searchName', (req, res) => {
const searchName =req.params.searchName;
let id;
let valueSql = `SELECT itemId as keyValue, name, picture FROM item_exception WHERE NAME LIKE '${searchName}'`;
db.query(valueSql,searchName,(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
})
app.get('/api/search_epic1/:searchName', (req, res) => {
const searchName =req.params.searchName;
let id;
let valueSql = `SELECT itemId as keyValue FROM item_exception WHERE NAME LIKE '${searchName}'`;
db.query(valueSql,searchName,(err, data) => {
if(!err) {
id = data[0].keyValue;
let resultSql =`SELECT T.channel_name, T.channel_number, T.VALUE, T.num from(SELECT itemId, channel_name, channel_number, COUNT(*) AS VALUE, ROW_NUMBER() over(order by value DESC) NUM FROM item_drop_exception WHERE itemId = (SELECT itemId FROM item_exception WHERE itemid = '${id}') AND date(droped_at) = DATE(NOW()) GROUP BY channel_name, channel_number) T WHERE T.NUM<=5 ORDER BY T.NUM`
//console.log("id: "+ id);
db.query( resultSql,id,(err, data) => {
if(!err) {
//console.log("data : " + data)
res.send(data);
} else {
//console.log(err);
res.send(err);
}
})
} else {
//console.log(err);
res.send(err);
}
})
})
//원그래프 용 쿼리
app.get('/api/search_epic2/:searchName', (req, res) => {
const searchName =req.params.searchName;
let id;
let valueSql = `SELECT itemId as keyValue FROM item_exception WHERE NAME LIKE '${searchName}'`;
db.query(valueSql,searchName,(err, data) => {
if(!err) {
id=data[0].keyValue;
console.log("dataLog2: "+ JSON.stringify(data))
var resultSql =`SELECT T.channel_name, T.channel_number, T.VALUE, T.num from(SELECT itemId, channel_name, channel_number, COUNT(*) AS VALUE, ROW_NUMBER() over(order by value DESC) NUM FROM item_drop_exception WHERE itemId = (SELECT itemId FROM item_exception WHERE itemid = '${id}') AND date(droped_at) = DATE(NOW()) GROUP BY channel_name, channel_number) T WHERE T.NUM<=7 ORDER BY T.NUM`
db.query( resultSql,id,(err, data) => {
if(!err) {
res.send(data);
} else {
console.log(err);
res.send(err);
}
})
} else {
//console.log(err);
res.send(err);
}
})
})
app.get('/api/search_epic3/:searchName', (req, res) => {
const searchName =req.params.searchName;
var id;
var i =0;
var list1 = [];
var valueSql = `SELECT itemId as keyValue FROM item_exception WHERE NAME LIKE '${searchName}'`;
db.query(valueSql,searchName,(err, data) => {
if(!err) {
id=data[0].keyValue;
//반복문으로 진행?
while(i<7)
{
//console.log("iterator: "+i)
var resultSql1 =`SELECT T.channel_name, T.channel_number, T.VALUE, T.num from(SELECT itemId, channel_name, channel_number, COUNT(*) AS VALUE, ROW_NUMBER() over(order by value DESC) NUM FROM item_drop_exception WHERE itemId = (SELECT itemId FROM item_exception WHERE itemid = '${id}') AND date(droped_at) = date(date_add(now(),interval -${i} DAY)) GROUP BY channel_name, channel_number) T WHERE T.NUM<=3 ORDER BY T.num`
db.query( resultSql1,id,(err, result) => {
if(!err) {
list1.push(result);
console.log(i+"번째 리스트 push, 내용: "+JSON.stringify(result));
if(list1.length==7)
{
res.send(list1);
}
}
else {
// console.log(err);
res.send(err);
}
})
i++;
}
} else {
console.log(err);
res.send(err);
}
})
})
app.listen(PORT, () => {
console.log(`Server On : http://localhost:${PORT}/`);
})