diff options
| -rw-r--r-- | app.js | 30 | ||||
| -rw-r--r-- | routes/index.js | 60 | ||||
| -rw-r--r-- | views/departments.jade | 2 | ||||
| -rw-r--r-- | views/employees.jade | 16 | ||||
| -rw-r--r-- | views/employees_in_department.jade | 3 | ||||
| -rw-r--r-- | views/lib/pager.jade | 12 |
6 files changed, 97 insertions, 26 deletions
@@ -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 >> |