aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--app.js30
-rw-r--r--routes/index.js60
-rw-r--r--views/departments.jade2
-rw-r--r--views/employees.jade16
-rw-r--r--views/employees_in_department.jade3
-rw-r--r--views/lib/pager.jade12
6 files changed, 97 insertions, 26 deletions
diff --git a/app.js b/app.js
index 6f3f4cf..dcbfb95 100644
--- a/app.js
+++ b/app.js
@@ -1,6 +1,7 @@
var express = require('express')
, routes = require('./routes')
- , http = require('http');
+ , http = require('http')
+ , url = require('url');
var app = express();
@@ -28,18 +29,33 @@ function urlgenerator(req, res, next) {
return 'http://' + host + '/';
},
- departments: function(offset) {
- return 'http://' + host + '/department/' + (offset ? '?offset=' + offset : '');
+ departments: function(query) {
+ return url.format({
+ protocol: 'http',
+ host: host,
+ pathname: '/department/',
+ query: query
+ });
},
department: function(dept_no) {
return 'http://' + host + '/department/' + dept_no;
},
- employees_in_department: function(dept_no, offset) {
- return 'http://' + host + '/department/' + dept_no + '/employees' + (offset ? '?offset=' + offset : '');
+ employees_in_department: function(dept_no, query) {
+ return url.format({
+ protocol: 'http',
+ host: host,
+ pathname: '/department/' + dept_no + '/employees',
+ query: query
+ });
},
- employees: function(offset) {
- return 'http://' + host + '/employee/' + (offset ? '?offset=' + offset : '');
+ employees: function(query) {
+ return url.format({
+ protocol: 'http',
+ host: host,
+ pathname: '/employee/',
+ query: query
+ });
},
employee: function(emp_no) {
return 'http://' + host + '/employee/' + emp_no;
diff --git a/routes/index.js b/routes/index.js
index c3cef8c..969c0b8 100644
--- a/routes/index.js
+++ b/routes/index.js
@@ -87,7 +87,7 @@ exports.departments = function(req, res) {
if(req.accepts('html')) {
res.render('departments', {
title: 'Department List',
- urlgenerator: res.urlgenerator, pager: p,
+ urlgenerator: res.urlgenerator, pager: p, query: req.query,
departments: rs2.rows
});
}
@@ -146,7 +146,7 @@ exports.employees_in_department = function(req, res) {
if(req.accepts('html')) {
res.render('employees_in_department', {
title: 'Department: #' + dept_no,
- urlgenerator: res.urlgenerator, pager: p,
+ urlgenerator: res.urlgenerator, pager: p, query: req.query,
dept_no: dept_no,
employees: rs2.rows
});
@@ -173,22 +173,60 @@ exports.employees = function(req, res) {
pg.connect(process.env.DATABASE_URL, function(err, client) {
if(err) throw err;
var emp_no = req.params.emp_no;
- 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) {
+ var sql1, sql2;
+ var sql1Params = [];
+ var sql2Params = [];
+ var qname = req.query.name;
+ if(_.isString(qname)) {
+ if(qname.charAt(0) == '*') {
+ qname = '%' + qname.slice(1);
+ }
+ if(qname.charAt(qname.length - 1) == '*') {
+ qname = qname.slice(0, -1) + '%';
+ }
+ var where = 'WHERE UPPER(first_name) LIKE UPPER($1) OR UPPER(last_name) LIKE UPPER($1)';
+ sql1 = 'SELECT count(emp_no) AS count FROM employees ' + where;
+ sql1Params.push(qname);
+ sql2 = 'SELECT * FROM employees ' + where + ' OFFSET $2 LIMIT $3';
+ sql2Params.push(qname);
+ }
+ else {
+ sql1 = 'SELECT n_live_tup AS count FROM pg_stat_user_tables WHERE relname=\'employees\'';
+ sql2 = 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2';
+ }
+ client.query(sql1, sql1Params, after(res, function(rs1) {
+ var p = pager(req, parseInt(rs1.rows[0].count));
+ sql2Params.push(p.offset);
+ sql2Params.push(p.limit);
+ client.query(sql2, sql2Params, after(res, function(rs2) {
if(req.accepts('html')) {
res.render('employees', {
title: 'Employee List',
- urlgenerator: res.urlgenerator, pager: p,
- employees: rs2.rows
+ urlgenerator: res.urlgenerator, pager: p, query: req.query,
+ employees: rs2.rows,
+ query: req.query
});
}
else {
+ var links = [];
+ if(_.isNumber(p.prevOffset)) {
+ links.push({
+ href: res.urlgenerator.employees(_.extend({}, req.query, {offset: p.prevOffset})),
+ rel: 'prev',
+ prompt: 'Previous page'
+ });
+ }
+ if(_.isNumber(p.nextOffset)) {
+ links.push({
+ href: res.urlgenerator.employees(_.extend({}, req.query, {offset: p.nextOffset})),
+ rel: 'next',
+ prompt: 'Next page'
+ });
+ }
var c = {collection: {
- href: res.urlgenerator.employees(),
- items: mapEmployees(res, rs2.rows)
+ href: res.urlgenerator.employees(req.query),
+ items: mapEmployees(res, rs2.rows),
+ links: links
}};
res.contentType('application/vnd.collection+json');
res.send(JSON.stringify(collection_json.fromObject(c)), 200);
diff --git a/views/departments.jade b/views/departments.jade
index 14d4a31..6974763 100644
--- a/views/departments.jade
+++ b/views/departments.jade
@@ -12,4 +12,4 @@ block content
tr
td: a(href=urlgenerator.department(department.dept_no)) #{department.dept_name}
- mixin pager(pager, urlgenerator.departments)
+ mixin pager(pager, urlgenerator.departments, query)
diff --git a/views/employees.jade b/views/employees.jade
index cea80ed..7a332ac 100644
--- a/views/employees.jade
+++ b/views/employees.jade
@@ -12,4 +12,18 @@ block content
tr
td: a(href=urlgenerator.employee(employee.emp_no)) #{employee.first_name} #{employee.last_name}
- mixin pager(pager, urlgenerator.employees)
+ mixin pager(pager, urlgenerator.employees, query)
+
+ h3 Queries
+
+ h4 Employee Search
+
+ form(action=urlgenerator.employees())
+ table
+ tr
+ th Name
+ td: input(name='name')
+ td Searches both first and last name
+ tr
+ th
+ td: input(type='submit', value='Search')
diff --git a/views/employees_in_department.jade b/views/employees_in_department.jade
index 8313b2c..277c750 100644
--- a/views/employees_in_department.jade
+++ b/views/employees_in_department.jade
@@ -18,4 +18,5 @@ block content
tr
td: a(href=urlgenerator.employee(employee.emp_no)) #{employee.first_name} #{employee.last_name}
- mixin pager(pager, function(offset) { return urlgenerator.employees_in_department(dept_no, offset) })
+ - var f = function(offset) { return urlgenerator.employees_in_department(dept_no, offset) }
+ mixin pager(pager, f, query)
diff --git a/views/lib/pager.jade b/views/lib/pager.jade
index 1a47e12..3a9d451 100644
--- a/views/lib/pager.jade
+++ b/views/lib/pager.jade
@@ -1,9 +1,10 @@
-mixin pager(pager, f)
+mixin pager(pager, f, query)
p
if typeof pager.prevOffset == 'number'
- a(href=f(pager.prevOffset)) << Previous
+ - query.offset=pager.prevOffset
+ a(href=f(query)) << Previous
else
- | Previous
+ | << Previous
|
| #{pager.offset / pager.pageSize + 1} /
@@ -11,7 +12,8 @@ mixin pager(pager, f)
|
if typeof pager.nextOffset == 'number'
- a(href=f(pager.nextOffset)) Next >>
+ - query.offset=pager.nextOffset
+ a(href=f(query)) Next >>
else
- | Next
+ | Next >>