From 7d5116f6f6096a9f37c4c71635dc570b7048c992 Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Mon, 2 Jul 2012 10:35:31 +0200
Subject: o Adding support for employee name query.

---
 routes/index.js | 60 ++++++++++++++++++++++++++++++++++++++++++++++-----------
 1 file changed, 49 insertions(+), 11 deletions(-)

(limited to 'routes')

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);
-- 
cgit v1.2.3