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

const { sequelize } = require('../database')
const moment = require('moment-immutable')
const { Op } = require('sequelize')

const list = async (req, res) => {
  const date = req.params.date
  ? moment(req.params.date)
  : moment(moment.now())

  const startDate = date.add(-365, 'days')
  const endDate = date.add(365, 'days')

  const [results, metadata] = await sequelize.query(`
    select distinct date,
    (select count(*) from labors where workdays.id = workdayId) as hasLabor,
    (select count(*) from services where workdays.id = workdayId) as hasService
    from workdays
    where laborCost > 0
    and date >= :startDate
    and date <= :endDate
  `, {
    replacements: {
      startDate: startDate.format('YYYY-MM-DD'),
      endDate: endDate.format('YYYY-MM-DD')
    }
  })

  const workdays = results.map(x => ({
    date: x.date,
    hasLabor: !!x.hasLabor,
    hasService: !!x.hasService
  }))

  res.status(200).send({
    startDate: startDate.format('YYYY-MM-DD'),
    endDate: endDate.format('YYYY-MM-DD'),
    workdays
  })
}


module.exports = {
  list
}