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

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

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 date = req.params.date
  ? moment(req.params.date)
  : moment((await sequelize.query('select max(date) as date from workdays where laborCost > 0'))[0].date || moment.now())

  const [metricsOverTime, metadata] = await sequelize.query(`
    SELECT
      loc.key,
      wd.date,
      wd.laborCost,
      SUM(distinct svc.inbound) as inbound,
      cast(SUM(distinct wd.laborCost) as double) / SUM(distinct svc.inbound) as costPerCarton
    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)
    AND wd.date <= :date
    AND wd.date >= :startDate
    GROUP BY loc.key, wd.date
  `, {
    replacements: {
      terminalIds,
      date: date.format('YYYY-MM-DD'),
      startDate: date.add(-30, 'days').format('YYYY-MM-DD')
    }
  })

  const metricsOverTimeSql = (serviceColumn) => `
    select
      terminals.key,
      workdays.date,
      sum(distinct labors.laborCost) as laborCost,
      sum(distinct services.${serviceColumn}) as ${serviceColumn},
      cast(sum(distinct labors.laborCost) as double) / sum(distinct services.${serviceColumn}) as costPer

    -- 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

    where terminals.id in(:terminalIds)
    and serviceCategories.key = :serviceCategory
    and workdays.date <= :date
    and workdays.date >= :startDate
    group by terminals.key, workdays.date
  `

  const metricsSql = (serviceColumn) => `
    select
        terminals.key as terminal,
        serviceCategories.key as serviceCategory,
        serviceCategories.displayOrder,
        laborCategories.key as laborCategory,
        sum(distinct labors.laborCost) as laborCost,
        sum(distinct services.${serviceColumn}) as ${serviceColumn},
        cast(sum(distinct labors.laborCost) as double) / sum(distinct services.${serviceColumn}) as costPer

    -- 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

    where terminals.id in(:terminalIds)
    and workdays.date = :date
    and serviceCategories.key = :serviceCategory
    group by terminals.key, serviceCategories.key, laborCategories.key
  `

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

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

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

  const metricsPerTerminalAndLaborCategorySql = (serviceColumn) => `
    select
      terminal,
      serviceCategory,
      laborCategory,
      cast(sum(distinct laborCost) as double) / sum(distinct ${serviceColumn}) as costPer
    from (${metricsSql(serviceColumn)})
    group by terminal, laborCategory
  `

  const lastMetricsSql = (serviceColumn) => `
      select
        workdays.date,
        terminals.key as terminal,
        sum(distinct labors.laborCost) as laborCost,
        sum(distinct services.${serviceColumn}) as ${serviceColumn},
        cast(sum(distinct labors.laborCost) as double) / sum(distinct services.${serviceColumn}) as costPer

      -- 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

      where workdays.date = :date
      and serviceCategories.key = :serviceCategory
      and terminals.id in(:terminalIds)

  `


  const serviceCategories = (await ServiceCategory.findAll())
  //const opts = {replacements: {serviceCategory: sc.key, terminalIds}}
  const ret = {
    date,
    serviceCategories: await Promise.all(
      (await ServiceCategory.findAll()).map(async sc => {
        const opts = {
          replacements: {
            terminalIds,
            serviceCategory: sc.key,
            date: date.format('YYYY-MM-DD'),
            startDate: date.add(-30, 'days').format('YYYY-MM-DD')
          }
        }
        const [mpsc] = await sequelize.query(metricsPerServiceCategorySql(sc.serviceColumn), opts)
        const [mplc] = await sequelize.query(metricsPerLaborCategorySql(sc.serviceColumn), opts)
        const [mptsc] = await sequelize.query(metricsPerTerminalAndServiceCategorySql(sc.serviceColumn), opts)
        const [mptlc] = await sequelize.query(metricsPerTerminalAndLaborCategorySql(sc.serviceColumn), opts)
        const [lastMetrics] = await sequelize.query(lastMetricsSql(sc.serviceColumn), opts)
        const [metricsOverTime] = await sequelize.query(metricsOverTimeSql(sc.serviceColumn), opts)
        return {
          ...mpsc[0],
          key: sc.key,
          serviceColumn: sc.serviceColumn,
          displayOrder: sc.displayOrder,
          laborCategories: mplc,
          terminals: mptsc.map(t => ({
            ...t,
            key: t.terminal,
            laborCategories: mptlc.filter(l => l.terminal === t.terminal)
          })),
          lastMetrics: lastMetrics[0],
          metricsOverTime
        }
      })
    ),
    metricsOverTime
  }

  return res.status(200).send(ret)

  
  const queries = await Promise.all(serviceCategories.map(async sc => ({
    metricsPerServiceCategory: await sanitize(req, (await sequelize.query(metricsPerServiceCategorySql(sc.serviceColumn), {replacements: {serviceCategory: sc.key, terminalIds}}))[0]),
    metricsPerLaborCategory: await sanitize(req, (await sequelize.query(metricsPerLaborCategorySql(sc.serviceColumn), {replacements: {serviceCategory: sc.key, terminalIds}}))[0]),
    metricsPerTerminalAndServiceCategory: await sanitize(req, (await sequelize.query(metricsPerTerminalAndServiceCategorySql(sc.serviceColumn), {replacements: {serviceCategory: sc.key, terminalIds}}))[0]),
    metricsPerTerminalAndLaborCategory: await sanitize(req, (await sequelize.query(metricsPerTerminalAndLaborCategorySql(sc.serviceColumn), {replacements: {serviceCategory: sc.key, terminalIds}}))[0]),
    lastMetrics: await sanitize(req, (await sequelize.query(lastMetricsSql(sc.serviceColumn), {replacements: {serviceCategory: sc.key, terminalIds}}))[0])

  })))

  const results = {
    serviceCategories: serviceCategories.map((sc, i) => Object.assign(queries[i].metricsPerServiceCategory[0], {
      laborCategories: queries[i].metricsPerLaborCategory.filter(mplc => mplc.serviceCategory === sc.key),
      terminals: queries[i].metricsPerTerminalAndServiceCategory.filter(mpt => mpt.serviceCategory === sc.key).map(mpt => Object.assign(mpt, {
        laborCategories: queries[i].metricsPerTerminalAndLaborCategory.filter(mptlc => mptlc.serviceCategory === sc.key && mptlc.terminal === mpt.terminal)
      })),
      lastMetrics: queries[i].lastMetrics,
      metricsOverTime: queries[i].metricsOverTime
    })),
    metricsOverTime
  }
  res.status(200).send(results)
}

module.exports = {
  get
}