-
Notifications
You must be signed in to change notification settings - Fork 10
/
queries.js
50 lines (42 loc) · 1.38 KB
/
queries.js
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
var promise = require('bluebird');
var options = {
promiseLib: promise
};
var pgp = require('pg-promise')(options);
var dbUrl = process.env.DATABASE_URL || 'postgres:https://localhost:5432/nola311';
var db = pgp(dbUrl);
function findAllCalls(req, res, next) {
let pageSize = req.query.pageSize ? parseInt(req.query.pageSize, 10) : 50;
db.any('select * from nola311.calls limit $1', pageSize)
.then(function (data) {
res.status(200).json({ status: 'success', data: data });
})
.catch(function (err) {
return next(err);
});
}
function findCallsByTicketId(req, res, next) {
let ticketId = req.params.ticketId;
db.one('select * from nola311.calls where ticket_id=$1', ticketId)
.then(function (data) {
res.status(200).json({ status: 'success', data: data });
})
.catch(function (err) {
return next(err);
});
}
function findCallTypeTotals(req, res, next) {
db.any('with totals as (select count(*) as total, issue_type from nola311.calls group by issue_type order by total desc) select issue_type, total from totals')
.then(function (data) {
console.log(data);
res.status(200).json({ status: 'success', data: data });
})
.catch(function (err) {
return next(err);
});
}
module.exports = {
findAllCalls: findAllCalls,
findCallsByTicketId: findCallsByTicketId,
findCallTypeTotals: findCallTypeTotals
};