special-dispatch-app/lib/controllers/statistics.js

const { Terminal } = require('../database')
const sequelize = require('../database/sequelize')

const get = async (req, res) => {
  const terminalIds = (await Terminal.findAll())
    .filter(loc => req.claims.TERMINAL_ALL_ACCESS || req.claims[`TERMINAL_${loc.key}_ACCESS`])
    .map(loc => loc.id)

  const [results, metadata] = await sequelize.query(`
    SELECT
      loc.key,
      wd.date,
      wd.laborCost,
      SUM(svc.delivered) as delivered,
      SUM(svc.scanned) as scanned,
      SUM(svc.delivered) / (wd.regularHours + (wd.overtimeHours * 1.5)) as efficiency
    FROM workdays wd
    JOIN terminals loc on wd.terminalId = loc.id
    LEFT JOIN services svc on svc.workdayId = wd.id
    WHERE loc.id IN(:terminalIds)
    GROUP BY loc.key, wd.date
  `, {
    replacements: { terminalIds }
  })
  res.status(200).send(results)

}

module.exports = {
  get
}