special-dispatch-app/lib/integration/xlsx-reports.js

const _ = require('lodash')
const { init, Retailer, Service, Workday } = require('../database')
const { title } = require('change-case')
const terminals = require('./terminals')

const XLSX = require('xlsx')

const $import = async (locKey, filename) => {
  await init()
  const locs = await terminals()
  const terminal = locs[locKey]
  const book = XLSX.readFile(filename)
  const sheets = []
  for (let sheetName of book.SheetNames) {
    let sheet = book.Sheets[sheetName]
    const data = XLSX.utils.sheet_to_json(sheet, {
      header: 1,
      blankrows: false
    })
    sheets.push(data)    
  }
  const workdays = []
  for (let sheet of sheets) {
    if (!sheet[0] || !sheet[0][0]) continue
    const date = sheet[0][0]
    const regularHours = +sheet[5][1]
    const overtimeHours = +sheet[5][2]
    let foundRetailer = 0
    const services = sheet
      .filter(row => (row[0] && foundRetailer) || (row[0] === 'Retailer' && foundRetailer++))
      .map(([retailer, totalDel, nonDlv, early, late, onTime, pctOnTime, delivered, scanned, notScanned, pctScanned]) => ({
        date,
        retailer,
        delivered: +delivered,
        scanned: +scanned
      }))
      //.filter(row => row.delivered || row.scanned)
      workdays.push({
        date,
        services,
        regularHours,
        overtimeHours
      })
  }

  const retailerKeys = _.chain(workdays)
    .map(x => x.services)
    .flatten()
    .map(x => x.retailer)
    .uniq()
    .value()

  for (let key of retailerKeys) {
    console.log(key)
    await Retailer.upsert({
      key,
      name: title(key)
    })
  }

  const retailerIds = _.chain(await Retailer.findAll())
    .map(({key, id}) => [key, id])
    .fromPairs()
    .value()

  console.log(retailerIds)
  
  for (let workday of workdays) {
    await Workday.upsert({
      terminalId: terminal.id,
      date: workday.date,
      regularHours: workday.regularHours,
      overtimeHours: workday.overtimeHours
    })

    const wd = await Workday.find({where: {
      terminalId: terminal.id,
      date: workday.date
    }})

    for (let service of workday.services) {
      if (service.delivered || service.scanned) {
        console.log(service)
        await Service.upsert({
          workdayId: wd.id,
          retailerId: retailerIds[service.retailer],
          date: workday.date,
          delivered: service.delivered,
          scanned: service.scanned
        })
      }
    }
  }

}



module.exports = {
  import: $import
}