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

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

const get = async (req, res) => {
  const locationIds = (await Location.findAll())
    .filter(loc => req.claims.LOCATION_ALL_ACCESS || req.claims[`LOCATION_${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 locations loc on wd.locationId = loc.id
    LEFT JOIN services svc on svc.workdayId = wd.id
    WHERE loc.id IN(:locationIds)
    GROUP BY loc.key, wd.date
  `, {
    replacements: { locationIds }
  })
  res.status(200).send(results)

}

module.exports = {
  get
}