o Adding support for employee name query.

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 >>