From 27fdccc050bbfe09beca29d8d50ca25e18c5102a Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Thu, 12 Jul 2012 22:01:54 +0200
Subject: o Moving most of the SQL code to a DAO.

---
 lib/dao.js | 127 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 127 insertions(+)
 create mode 100644 lib/dao.js

(limited to 'lib')

diff --git a/lib/dao.js b/lib/dao.js
new file mode 100644
index 0000000..5ebb38e
--- /dev/null
+++ b/lib/dao.js
@@ -0,0 +1,127 @@
+var _ = require('underscore');
+
+function EmployeeDao(con) {
+  this.con = con;
+  this.getDepartments = getDepartments;
+  this.getEmployeesInDepartment = getEmployeesInDepartment;
+  this.getEmployeesByName = getEmployeesByName;
+  this.getEmployee = getEmployee;
+  this.deleteEmployee = deleteEmployee;
+}
+
+function getDepartments(pager, cb) {
+  var con = this.con;
+  var departments_count = {
+    text: 'SELECT COUNT(dept_no) AS count FROM departments',
+    values: []
+  } 
+
+  var departments = {
+    text: 'SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2',
+    values: [ pager.offset, pager.limit ]
+  } 
+
+  con.query(departments_count, function(err, rs1) {
+    if(err) return cb(err);
+    con.query(departments, function(err, rs2) {
+      if(err) return cb(err);
+      cb(undefined, rs2.rows, rs1.rows[0].count);
+    });
+  });
+}
+
+function getEmployeesInDepartment(dept_no, pager, cb) {
+  var con = this.con;
+  var where = 'dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date'
+  var employees_in_department_count = {
+    text: 'SELECT COUNT(emp_no) AS count FROM dept_emp WHERE ' + where,
+    values: [ dept_no ]
+  };
+
+  var employees_in_department = {
+    text: 'SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND ' + where + ' OFFSET $2 LIMIT $3',
+    values: [ dept_no, pager.offset, pager.limit ]
+  };
+
+  con.query(employees_in_department_count, function(err, rs1) {
+    if(err) return cb(err);
+    con.query(employees_in_department, function(err, rs2) {
+      if(err) return cb(err);
+      cb(undefined, rs2.rows, rs1.rows[0].count);
+    });
+  });
+}
+
+function getEmployeesByName(query, pager, cb) {
+  var con = this.con;
+  var where = 'WHERE UPPER(first_name) LIKE UPPER($1) OR UPPER(last_name) LIKE UPPER($1)';
+  var employees_by_name_count = {
+    text: 'SELECT count(emp_no) AS count FROM employees ' + where,
+    values: []
+  };
+  var employees_by_name = {
+    text: 'SELECT * FROM employees ' + where + ' ORDER BY emp_no OFFSET $2 LIMIT $3',
+    values: []
+  };
+
+  var employee_count = {
+    text: 'SELECT COUNT(emp_no) AS count FROM employees',
+    values: []
+  };
+  var employees = {
+    text: 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2',
+    values: []
+  };
+
+  var q1, q2;
+  if(_.isString(query)) {
+    if(query.charAt(0) == '*') {
+      query = '%' + query.slice(1);
+    }
+    if(query.charAt(query.length - 1) == '*') {
+      query = query.slice(0, -1) + '%';
+    }
+    employees_by_name_count.values.push(query);
+    employees_by_name.values.push(query);
+    q1 = employees_by_name_count;
+    q2 = employees_by_name;
+  } else {
+    q1 = employee_count;
+    q2 = employees;
+  }
+  con.query(q1, function(err, rs1) {
+    if(err) return cb(err);
+    q2.values.push(pager.offset);
+    q2.values.push(pager.limit);
+    con.query(q2, function(err, rs2) {
+      if(err) return cb(err);
+      cb(undefined, rs2.rows, rs1.rows[0].count);
+    });
+  });
+}
+
+function getEmployee(emp_no, cb) {
+  var employee_by_emp_no = {
+    text: 'SELECT * FROM employees WHERE employees.emp_no=$1',
+    values: []
+  }
+  employee_by_emp_no.values.push(emp_no);
+  this.con.query(employee_by_emp_no, function(err, rs) {
+    if(err) return cb(err);
+    cb(undefined, rs.rows[0]);
+  });
+}
+
+function deleteEmployee(emp_no, cb) {
+  var delete_employee_by_emp_no = {
+    text: 'DELETE FROM employees WHERE emp_no=$1',
+    values: []
+  }
+  delete_employee_by_emp_no.values.push(emp_no);
+  this.con.query(delete_employee_by_emp_no, function(err, rs) {
+    if(err) return cb(err);
+    cb(undefined, rs.rowCount);
+  });
+}
+
+module.exports = EmployeeDao;
-- 
cgit v1.2.3