<repo_name>Mark-Mulligan/ReactEmployeeManagementBackend<file_sep>/db/schema.sql
Drop Database IF exists company_db;
CREATE DATABASE company_db;

Use company_db;

CREATE TABLE departments (
    id INT NOT NULL AUTO_INCREMENT PRIMARY Key,
    name VARCHAR(30),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

CREATE TABLE roles (
  id INT NOT NULL AUTO_INCREMENT PRIMARY Key,
  title VARCHAR(30),
  salary DECIMAL,
  department_id INT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
);

CREATE TABLE employees (
	id INT NOT NULL AUTO_INCREMENT PRIMARY Key,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    role_id INT,
    manager_id INT,
    date_hired DATE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);<file_sep>/db/seeds.sql
Use company_db;

INSERT INTO departments (name) VALUES
('Management'),
('Sales'),
('Finance'),
('R&D'),
('Human Resources');

INSERT INTO roles (title, salary, department_id) VALUES
('CEO', 500000, 1),
('Regional Manager', 150000, 1),
('Lead Salesperson', 90000, 2),
('Salesperson', 70000, 2),
('Lead Accountant', 100000, 3),
('Accountant', 75000, 3),
('Analyst', 85000, 4),
('HR Rep', 50000, 5);

INSERT INTO employees (first_name, last_name, role_id, manager_id, date_hired) VALUES
('David', 'Wallace', 1,  null, '2010-01-01'),
('Michael', 'Scott', 2, 1, '2010-01-01'),
('Dwight', 'Schrute', 3, 2, '2010-03-20'),
('Jim', 'Halpert', 4, 3, '2010-05-10'),
('Stanley', 'Hudson', 4, 3, '2011-02-12'),
('Creed', 'Bratton', 7, 2, '2011-04-15'),
('Andy', 'Bernard', 3, 3, '2012-08-12'),
('Oscar', 'Martinez', 5, 2, '2013-10-09'),
('Angela', 'Martin', 6, 8, '2014-11-02'),
('Kevin', 'Malone', 6, 8, '2014-12-01'),
('Ryan', 'Howard', 7, 2, '2015-06-23'),
('Gabe', 'Lewis', 8, 2, '2015-09-07');<file_sep>/models/employee.js
const orm = require("../config/orm");

const employee = {
  getTableData: (orderby, cb, errCb) => {
    orm.getEmployeeTableData(
      orderby,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getBarChartData: (cb, errCb) => {
    orm.getEmployeeBarChartData(
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getOne: (id, cb, errCb) => {
    orm.getSingleEmployee(
      id,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getManagersForForm: (cb, errCb) => {
    orm.simpleSelect(
      `id, CONCAT(first_name, ' ', last_name) as manager`,
      "employees",
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getManagersForEdit: (userWhereValue, cb, errCb) => {
    orm.simpleSelectWithWhere(
      `id, CONCAT(first_name, ' ', last_name) as manager`,
      "employees",
      "id",
      "!=",
      userWhereValue,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  create: (userValuesArr, cb, errCb) => {
    orm.simpleInsert(
      "employees",
      ["first_name", "last_name", "role_id", "manager_id"],
      userValuesArr,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  update: (userValuesArr, employeeId, cb, errCb) => {
    orm.simpleUpdate(
      "employees",
      ["first_name", "last_name", "role_id", "manager_id"],
      userValuesArr,
      employeeId,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  deleteOne: (userWhereValue, cb, errCb) => {
    orm.simpleDelete(
      "employees",
      "id",
      "=",
      userWhereValue,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
};

module.exports = employee;
<file_sep>/server.js
const express = require("express");
const cors = require('cors')
const PORT = process.env.PORT || 3001;
const app = express();

app.use(cors());
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// Import routes and give the server access to them.
const employeeRoutes = require('./controllers/employeeController');
const roleRoutes = require('./controllers/roleController');
const departmentRoutes = require('./controllers/departmentController');

app.use(employeeRoutes);
app.use(roleRoutes);
app.use(departmentRoutes);

app.listen(PORT, function() {
  console.log("App now listening at localhost:" + PORT);
});<file_sep>/models/role.js
const orm = require("../config/orm");

const role = {
  getTableData: (cb, errCb) => {
    orm.getRoleTableData(
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getOne: (roleId, cb, errCb) => {
    orm.getSingleRole(
      roleId,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  create: (userValuesArr, cb, errCb) => {
    orm.simpleInsert(
      "roles",
      ["title", "salary", "department_id"],
      userValuesArr,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  update: (userValuesArr, roleId, cb, errCb) => {
    orm.simpleUpdate(
      "roles",
      ["title", "salary", "department_id"],
      userValuesArr,
      roleId,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  delete: (userWhereValue, cb, errCb) => {
    orm.simpleDelete(
      "roles",
      "id",
      "=",
      userWhereValue,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getBarChartData: (cb, errCb) => {
    orm.getRoleBarChartData(
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
};

module.exports = role;
<file_sep>/util/util.js
const util = {
  createPlaceholders: (arr) => {
    let placeholders = '';
    
    for (let i = 0; i < arr.length; i++) {
        if (i === 0 && arr.length === 1) {
            placeholders += '?'
        } else if (i === 0) {
            placeholders += '?,'
        } else if (i === arr.length - 1) {
            placeholders += ' ?'
        } else {
            placeholders += ' ?,'
        }
    }
    return placeholders;
  }
}

module.exports = util;<file_sep>/db/queries.sql
SELECT *
FROM employees
    INNER JOIN roles ON roles.id = employees.role_id
    INNER JOIN departments ON departments.id  = roles.department_id; 

SELECT *
FROM employees
    INNER JOIN roles ON roles.id = employees.role_id
    Where department_id = 2;

SELECT departments.*, count(roles.department_id) as roles        
from departments
left join roles
on (roles.department_id = departments.id)
group by
    departments.id;

/* Gets number of employees in department */
SELECT departments.*, count(roles.department_id) as roles        
from departments
left join roles
on (roles.department_id = departments.id)
left join employees
on (employees.role_id = roles.id)
group by
    departments.id;

/* Returns department id, name, num employees, and utilization */
SELECT departments.id, departments.name, 
count(roles.department_id) as employees, 
count(distinct roles.id) as roles, 
SUM(roles.salary) as departmentUtilization       
from departments
join roles
on (roles.department_id = departments.id)
join employees
on (employees.role_id = roles.id)
group by
    departments.id;

/* query for individual department */
SELECT departments.id, departments.name, 
roles.title as roles, roles.salary as salary,
CONCAT(employees.first_name, ' ', employees.last_name) as employees    
from departments
join roles
on (roles.department_id = departments.id)
join employees
on (employees.role_id = roles.id)
Where departments.id = 1;

/* Get Additional data for Role Table */
SELECT roles.id, roles.title, roles.salary, 
departments.name, count(employees.id) as employees, sum(roles.salary) as roleUtilization
from roles left join employees on (roles.id = employees.role_id)
left join departments on (roles.department_id = departments.id)
group by roles.id;

/* Select invdividual role */
SELECT roles.id, roles.title, roles.salary, 
departments.name, count(employees.id) as employees, sum(roles.salary) as roleUtilization
from roles left join employees on (roles.id = employees.role_id)
left join departments on (roles.department_id = departments.id)
group by roles.id having roles.id = 3;<file_sep>/controllers/roleController.js
const express = require("express");
//const { getTableData: getRoles } = require("../models/department");
const router = express.Router();
const role = require("../models/role");

router.get("/roles", (req, res) => {
  role.getTableData(
    (data) => res.json(data),
    (err) => {
      res.status(500);
      res.json(err);
    }
  );
});

router.post("/roles", (req, res) => {
  const { title, salary, departmentId } = req.body;

  role.create([title, salary, departmentId], (data) => res.json(data),
  (err) => {
    res.status(500);
    res.json(err);
  });
});

router.get("/role/:id", (req, res) => {
  const roleId = req.params.id;
  role.getOne(
    roleId,
    (data) => res.json(data),
    (err) => {
      res.status(500);
      res.json(err);
    }
  );
});

router.put("/role/:id", (req, res) => {
  const roleId = req.params.id;
  const { title, salary, departmentId } = req.body;
  role.update(
    [title, salary, departmentId],
    roleId,
    (data) => res.json(data),
    (err) => {
      res.status(500);
      res.json(err);
    }
  );
});

router.delete("/role/:id", (req, res) => {
  const roleId = req.params.id;
  role.delete(
    roleId,
    (data) => res.json(data),
    (err) => {
      res.status(500);
      res.json(err);
    }
  );
});

router.get("/api/roles/chartdata", (req, res) => {
  role.getBarChartData(
    (data) => res.json(data),
    (err) => {
      res.status(500);
      res.json(err);
    }
  );
});

module.exports = router;
<file_sep>/models/department.js
const orm = require("../config/orm");

const department = {
  getTableData: (cb, errCb) => {
    orm.getDepartmentTableData(
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getOne: (departmentId, cb, errCb) => {
    orm.getSingleDepartment(
      departmentId,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getNamesAndIds: (cb, errCb) => {
    orm.simpleSelect(
      `name, id`,
      "departments",
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  getRolesInDepartment: (departmentId, cb, errCb) => {
    orm.simpleSelectWithWhere(
      "title, id",
      "roles",
      "department_id",
      "=",
      departmentId,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  create: (valuesArr, cb, errCb) => {
    orm.simpleInsert(
      "departments",
      ["name"],
      valuesArr,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  update: (userValuesArr, departmentId, cb, errCb) => {
    orm.simpleUpdate(
      "departments",
      ["name"],
      userValuesArr,
      departmentId,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
  delete: (userWhereValue, cb, errCb) => {
    orm.simpleDelete(
      "departments",
      "id",
      "=",
      userWhereValue,
      (response) => cb(response),
      (err) => errCb(err)
    );
  },
};

module.exports = department;
