diff options
author | Trygve Laugstøl <trygvis@inamo.no> | 2012-07-12 22:01:54 +0200 |
---|---|---|
committer | Trygve Laugstøl <trygvis@inamo.no> | 2012-07-12 22:01:54 +0200 |
commit | 27fdccc050bbfe09beca29d8d50ca25e18c5102a (patch) | |
tree | dc4948e3e4edb40d725a2a3595c6f9d01a917a5c /lib | |
parent | ffa799fa0fa762f74ac04b46b670ab981c715fbf (diff) | |
download | example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.tar.gz example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.tar.bz2 example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.tar.xz example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.zip |
o Moving most of the SQL code to a DAO.
Diffstat (limited to 'lib')
-rw-r--r-- | lib/dao.js | 127 |
1 files changed, 127 insertions, 0 deletions
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; |