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

const _ = require('lodash')
const { Terminal } = require('../database')
const sequelize = require('../database/sequelize')
const { sanitize } = require('@alancnet/material-framework/lib/util')

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 [metricsOverTime, metadata] = await sequelize.query(`
    SELECT
      loc.key,
      wd.date,
      wd.laborCost,
      SUM(distinct svc.cartons) as cartons,
      cast(SUM(distinct wd.laborCost) as double) / SUM(distinct svc.cartons) 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 }
  })

  const metricsSql = `
    select
        terminals.key as terminal,
        serviceCategories.key as serviceCategory,
        serviceCategories.displayOrder,
        laborCategories.key as laborCategory,
        sum(distinct labors.laborCost) as laborCost,
        sum(distinct services.cartons) as cartons,
        cast(sum(distinct labors.laborCost) as double) / sum(distinct services.cartons) as costPerCarton

    -- Start with Terminals
    from terminals

    -- Link to labor cost
    join workdays on workdays.terminalId = terminals.id
    join services on services.workdayId = workdays.id
    join labors on labors.workdayId = workdays.id

    -- Link to LaborCategory
    join staffMembers on labors.staffMemberId = staffMembers.id
    join laborCategories on staffMembers.laborCategoryId = laborCategories.id

    -- Link to ServiceCategory
    join laborServiceCategories on laborServiceCategories.laborCategoryId = laborCategories.id
    join serviceCategories on laborServiceCategories.serviceCategoryId = serviceCategories.id
    group by terminals.key, serviceCategories.key, laborCategories.key
  `

  const metricsPerServiceCategorySql = `
    select
      serviceCategory,
      displayOrder,
      cast(sum(distinct laborCost) as double) / sum(distinct cartons) as costPerCarton
    from (${metricsSql})
    group by serviceCategory
    order by displayOrder
  `

  const metricsPerLaborCategorySql = `
    select
      serviceCategory,
      laborCategory,
      cast(sum(distinct laborCost) as double) / sum(distinct cartons) as costPerCarton
    from (${metricsSql})
    group by laborCategory
  `

  const metricsPerTerminalAndServiceCategorySql = `
    select
      terminal,
      serviceCategory,
      cast(sum(distinct laborCost) as double) / sum(distinct cartons) as costPerCarton
    from (${metricsSql})
    group by terminal, serviceCategory
  `

  const metricsPerTerminalAndLaborCategorySql = `
    select
      terminal,
      serviceCategory,
      laborCategory,
      cast(sum(distinct laborCost) as double) / sum(distinct cartons) as costPerCarton
    from (${metricsSql})
    group by terminal, laborCategory
  `
  const metricsPerServiceCategory = await sanitize(req, (await sequelize.query(metricsPerServiceCategorySql))[0])
  const metricsPerLaborCategory = await sanitize(req, (await sequelize.query(metricsPerLaborCategorySql))[0])
  const metricsPerTerminalAndServiceCategory = await sanitize(req, (await sequelize.query(metricsPerTerminalAndServiceCategorySql))[0])
  const metricsPerTerminalAndLaborCategory = await sanitize(req, (await sequelize.query(metricsPerTerminalAndLaborCategorySql))[0])

  const results = {
    serviceCategories: metricsPerServiceCategory.map(mpsc => Object.assign(mpsc, {
      laborCategories: metricsPerLaborCategory.filter(mplc => mplc.serviceCategory === mpsc.serviceCategory),
      terminals: metricsPerTerminalAndServiceCategory.filter(mpt => mpt.serviceCategory === mpsc.serviceCategory).map(mpt => Object.assign(mpt, {
        laborCategories: metricsPerTerminalAndLaborCategory.filter(mptlc => mptlc.serviceCategory === mpsc.serviceCategory && mptlc.terminal === mpt.terminal)
      }))
    })),
    metricsOverTime
  }
  res.status(200).send(results)

}

module.exports = {
  get
}