aboutsummaryrefslogtreecommitdiff
path: root/routes
diff options
context:
space:
mode:
Diffstat (limited to 'routes')
-rw-r--r--routes/index.js187
1 files changed, 96 insertions, 91 deletions
diff --git a/routes/index.js b/routes/index.js
index dcb6e23..c3cef8c 100644
--- a/routes/index.js
+++ b/routes/index.js
@@ -1,5 +1,6 @@
var collection_json = require('collection_json')
, pg = require('pg').native
+ , util = require('util')
, _ = require('underscore');
function mapRow(row) {
@@ -25,22 +26,32 @@ function mapEmployees(res, employees) {
});
}
-function pager(req) {
- var pageSize = 10;
+function pager(req, count) {
+ var pageSize = 5;
var offset = parseInt(req.query.offset) || 0;
var limit = parseInt(req.query.limit) || pageSize;
var prevOffset = offset - pageSize;
var nextOffset = offset + pageSize;
return {
+ pageSize: pageSize,
offset: offset,
limit: limit,
- prevOffset: prevOffset >= 0 ? prevOffset : 0,
- nextOffset: function(length) {
- return length > 0 ? nextOffset : undefined;
- }
+ count: count,
+ prevOffset: prevOffset >= 0 ? prevOffset : undefined,
+ nextOffset: nextOffset < count ? nextOffset : undefined
};
}
+function after(res, callback) {
+ return function(err, queryResult) {
+ if(err) {
+ res.writeHead(500, {"Content-Type" : "text/plain"});
+ return res.end("Error! " + util.inspect(err))
+ }
+ callback(queryResult)
+ }
+}
+
exports.index = function(req, res) {
if(req.accepts('html')) {
res.render('index', {
@@ -68,29 +79,28 @@ exports.index = function(req, res) {
exports.departments = function(req, res) {
pg.connect(process.env.DATABASE_URL, function(err, client) {
if(err) throw err;
- var p = pager(req);
- var departments = [];
- client.query('SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2', [ p.offset, p.limit ] ).
- on('row', function(row) {
- departments.push(row);
- }).
- on('end', function() {
- if(req.accepts('html')) {
- res.render('departments', {
- title: 'Department List',
- urlgenerator: res.urlgenerator, pager: p,
- departments: departments
- });
- }
- else {
- var c = {collection: {
- href: res.urlgenerator.departments(),
- items: mapDepartments(res, departments)
- }};
- res.contentType('application/vnd.collection+json');
- res.send(JSON.stringify(collection_json.fromObject(c)), 200);
- }
- });
+ var sql = 'SELECT n_live_tup FROM pg_stat_user_tables WHERE relname=\'departments\'';
+ var sql2 = 'SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2';
+ client.query(sql, after(res, function(rs) {
+ var p = pager(req, parseInt(rs.rows[0].n_live_tup));
+ client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
+ if(req.accepts('html')) {
+ res.render('departments', {
+ title: 'Department List',
+ urlgenerator: res.urlgenerator, pager: p,
+ departments: rs2.rows
+ });
+ }
+ else {
+ var c = {collection: {
+ href: res.urlgenerator.departments(),
+ items: mapDepartments(res, departments.rows)
+ }};
+ res.contentType('application/vnd.collection+json');
+ res.send(JSON.stringify(collection_json.fromObject(c)), 200);
+ }
+ }));
+ }));
});
};
@@ -125,67 +135,66 @@ exports.employees_in_department = function(req, res) {
pg.connect(process.env.DATABASE_URL, function(err, client) {
if(err) throw err;
var dept_no = req.params.dept_no;
- var p = pager(req);
- var employees = [];
// TODO: Add dept_name to view
// TODO: Add manager as a link
- client.query('SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date OFFSET $2 LIMIT $3', [ dept_no, p.offset, p.limit ]).
- on('row', function(row) {
- employees.push(row);
- }).
- on('end', function() {
- if(req.accepts('html')) {
- res.render('employees_in_department', {
- title: 'Department: #' + dept_no,
- urlgenerator: res.urlgenerator, pager: p,
- dept_no: dept_no,
- employees: employees
- });
- }
- else {
- var c = {collection: {
- href: res.urlgenerator.employees_in_department(dept_no),
- links: [ {
- rel: 'department',
- prompt: 'Department: #' + dept_no,
- href: res.urlgenerator.department(dept_no)
- } ],
- items: mapEmployees(res, employees)
- }};
- res.contentType('application/vnd.collection+json');
- res.send(collection_json.fromObject(c));
- }
- });
+ var where = 'dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date'
+ var sql1 = 'SELECT count(emp_no) as count FROM dept_emp WHERE ' + where;
+ var sql2 = 'SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND ' + where + ' OFFSET $2 LIMIT $3';
+ client.query(sql1, [ dept_no ], after(res, function(rs1) {
+ var p = pager(req, parseInt(rs1.rows[0].count));
+ client.query(sql2, [ dept_no, p.offset, p.limit ], after(res, function(rs2) {
+ if(req.accepts('html')) {
+ res.render('employees_in_department', {
+ title: 'Department: #' + dept_no,
+ urlgenerator: res.urlgenerator, pager: p,
+ dept_no: dept_no,
+ employees: rs2.rows
+ });
+ }
+ else {
+ var c = {collection: {
+ href: res.urlgenerator.employees_in_department(dept_no),
+ links: [ {
+ rel: 'department',
+ prompt: 'Department: #' + dept_no,
+ href: res.urlgenerator.department(dept_no)
+ } ],
+ items: mapEmployees(res, rs2.rows)
+ }};
+ res.contentType('application/vnd.collection+json');
+ res.send(collection_json.fromObject(c));
+ }
+ }));
+ }));
});
};
exports.employees = function(req, res) {
pg.connect(process.env.DATABASE_URL, function(err, client) {
if(err) throw err;
- var p = pager(req);
var emp_no = req.params.emp_no;
- var employees = [];
- client.query('SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2', [ p.offset, p.limit ]).
- on('row', function(row) {
- employees.push(row);
- }).
- on('end', function() {
- if(req.accepts('html')) {
- res.render('employees', {
- title: 'Employee List',
- urlgenerator: res.urlgenerator, pager: p,
- employees: employees
- });
- }
- else {
- var c = {collection: {
- href: res.urlgenerator.employees(),
- items: mapEmployees(res, employees)
- }};
- res.contentType('application/vnd.collection+json');
- res.send(JSON.stringify(collection_json.fromObject(c)), 200);
- }
- });
+ var sql1 = 'SELECT n_live_tup FROM pg_stat_user_tables WHERE relname=\'employees\'';
+ var sql2 = 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2';
+ client.query(sql1, after(res, function(rs1) {
+ var p = pager(req, parseInt(rs1.rows[0].n_live_tup));
+ client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
+ if(req.accepts('html')) {
+ res.render('employees', {
+ title: 'Employee List',
+ urlgenerator: res.urlgenerator, pager: p,
+ employees: rs2.rows
+ });
+ }
+ else {
+ var c = {collection: {
+ href: res.urlgenerator.employees(),
+ items: mapEmployees(res, rs2.rows)
+ }};
+ res.contentType('application/vnd.collection+json');
+ res.send(JSON.stringify(collection_json.fromObject(c)), 200);
+ }
+ }));
+ }));
});
};
@@ -193,27 +202,23 @@ exports.employee = function(req, res) {
pg.connect(process.env.DATABASE_URL, function(err, client) {
if(err) throw err;
var emp_no = req.params.emp_no;
- var employee;
- client.query('SELECT * FROM employees WHERE employees.emp_no=$1', [ emp_no ]).
- on('row', function(row) {
- employee = row;
- }).
- on('end', function() {
+ var sql = 'SELECT * FROM employees WHERE employees.emp_no=$1';
+ client.query(sql, [ emp_no ], after(res, function(rs) {
if(req.accepts('html')) {
res.render('employee', {
title: 'Employee: #' + emp_no,
urlgenerator: res.urlgenerator,
- employee: employee
+ employee: rs.rows[0]
});
}
else {
var c = {collection: {
href: res.urlgenerator.employee(emp_no),
- items: [ mapRow(employee) ],
+ items: [ mapRow(rs.rows[0]) ],
}};
res.contentType('application/vnd.collection+json');
res.send(JSON.stringify(collection_json.fromObject(c)), 200);
}
- });
+ }))
});
-};
+}
generated by cgit v1.2.3 (git 2.46.0) at 2024-12-22 21:33:57 +0000