Skip to content

armgit / bento

Public Imported from GitHub
0

Bento, the office suite that fits in a file

22.5 KB
Raw Blame
// SPDX-License-Identifier: MIT
// Copyright (c) 2026 The Bento authors
// The pivot's field editor — rows, columns, values and filters, the way every
// peer lays them out (Excel's PivotTable Fields, Sheets' pivot editor, Numbers'
// Pivot Options): the source's columns in a list, four areas under it, a field
// dragged or picked into an area.
//
// A PIVOT THAT CAN ONLY BE GUESSED IS NOT A PIVOT. `defaultPivot` picks Region ×
// Owner, sum of Value, and until this file existed that guess was the whole of
// what a pivot could be: no way to group by Stage, to count rather than sum, or
// to leave out the lost deals. A summary whose question somebody else chose is
// a chart of the wrong thing.
//
// EVERY EDIT IS A SPEC, AND THE SPEC IS THE DOCUMENT. The operations at the top
// of this file are pure functions from one `PivotSpec` to the next — no DOM, so
// `scripts/test-dash-pivotfields.ts` holds them to the rules a drag gesture is
// easy to get wrong:
//
//   1. A GROUPING FIELD IS IN ONE PLACE. Region in Rows AND Columns is a
//      diagonal of one-cell groups; Region in Rows and Filters disagrees with
//      itself. Moving a field between those three areas MOVES it.
//   2. VALUES ARE NOT GROUPING. "Count of Region" beside Region in Rows is a
//      legitimate question, and so is Sum and Average of Value side by side —
//      so Values may repeat a column, but never the same column AND function
//      twice (two identical columns of numbers read as a rendering bug).
//   3. THE FILTERS AREA STORES THE JSON SPELLING. A checklist is `values`, an
//      array; filter.ts's `Set` serializes as `{}` and would reopen matching
//      nothing (model.ts `StoredFilter`).
//   4. AN EDIT NEVER MUTATES ITS INPUT. The spec it is handed is the one inside
//      `store.doc`; mutating it in place is a change the undo stack never saw.

import './pivotfields.css'
import type { Column, TableSheet } from './model.ts'
import type { PanelKit } from './cellprops.ts'
import {
  AGG_FNS, isPivotSheet, readPivotSpec,
  type AggFn, type PivotFilter, type PivotSheet, type PivotSpec, type PivotValue,
} from './pivot.ts'
import { distinctValues, matchKey, BLANK_KEY } from './filter.ts'
import { readCell } from './store.ts'
import { listLabel, TYPE_LABEL } from './format.ts'
import { t } from './i18n.ts'

// --- the operations ---------------------------------------------------------

export type Area = 'filters' | 'cols' | 'rows' | 'values'

const isMeasure = (c: Pick<Column, 'type'> | undefined): boolean =>
  !!c && (c.type === 'number' || c.type === 'money' || c.type === 'percent')

/**
 * What a field means when it lands in Values with no function chosen. A
 * measure is summed — that is the question asked of a money column nine times
 * in ten. Anything else is COUNTED, because a sum of text is not a number and
 * printing an empty column under "Sum of Region" is a broken pivot, not an
 * answer.
 */
export const defaultAgg = (c: Pick<Column, 'type'> | undefined): AggFn =>
  isMeasure(c) ? 'sum' : 'count'

const clone = (spec: PivotSpec): PivotSpec => ({
  ...spec,
  rows: [...spec.rows],
  cols: [...spec.cols],
  values: spec.values.map((v) => ({ ...v })),
  ...(spec.filters ? { filters: spec.filters.map((f) => ({ ...f })) } : {}),
})

/** The filter-area columns, in order. */
export const filterCols = (spec: PivotSpec): string[] =>
  (spec.filters ?? []).filter((f) => f && typeof f.col === 'string').map((f) => f.col)

/** Which areas hold this column. */
export function fieldAreas(spec: PivotSpec, col: string): Area[] {
  const out: Area[] = []
  if (filterCols(spec).includes(col)) out.push('filters')
  if (spec.cols.includes(col)) out.push('cols')
  if (spec.rows.includes(col)) out.push('rows')
  if (spec.values.some((v) => v.col === col)) out.push('values')
  return out
}

/** Take a column out of every GROUPING area (Values untouched). */
function ungroup(s: PivotSpec, col: string): void {
  s.rows = s.rows.filter((c) => c !== col)
  s.cols = s.cols.filter((c) => c !== col)
  if (s.filters) {
    s.filters = s.filters.filter((f) => f.col !== col)
    if (!s.filters.length) delete s.filters
  }
}

const clampAt = (at: number | undefined, len: number): number =>
  at === undefined || !Number.isFinite(at) ? len : Math.max(0, Math.min(len, Math.floor(at)))

/**
 * Put a column into an area, at a position (default: the end).
 *
 * Into a grouping area it MOVES — out of the other two first (rule 1) — and a
 * filter it carried along with it is dropped: a checklist on Region was a
 * statement about which regions to admit, and Region becoming a row field is
 * the author asking to see them all. Into Values it is ADDED with `fn` (or the
 * column's default), unless that exact column-and-function is already there
 * (rule 2), in which case the spec comes back unchanged.
 */
export function addField(
  spec: PivotSpec, col: string, area: Area,
  opts: { at?: number; fn?: AggFn; column?: Pick<Column, 'type'> } = {},
): PivotSpec {
  const s = clone(spec)
  if (area === 'values') {
    const fn = opts.fn ?? defaultAgg(opts.column)
    if (s.values.some((v) => v.col === col && v.fn === fn)) return spec
    s.values.splice(clampAt(opts.at, s.values.length), 0, { col, fn })
    return s
  }
  // Re-inserting into the SAME area at a later index: the index was measured
  // with the field still in the list, so it shifts by one once it is taken out.
  let at = opts.at
  const list = area === 'filters' ? filterCols(s) : s[area]
  const was = list.indexOf(col)
  if (was >= 0 && at !== undefined && at > was) at -= 1
  ungroup(s, col)
  if (area === 'filters') {
    const fs = s.filters ?? []
    fs.splice(clampAt(at, fs.length), 0, { col })
    s.filters = fs
  } else {
    s[area].splice(clampAt(at, s[area].length), 0, col)
  }
  return s
}

/** Take the `index`th entry out of an area. Out of range is a no-op. */
export function removeField(spec: PivotSpec, area: Area, index: number): PivotSpec {
  const s = clone(spec)
  if (area === 'values') {
    if (!s.values[index]) return spec
    s.values.splice(index, 1)
  } else if (area === 'filters') {
    if (!s.filters?.[index]) return spec
    s.filters.splice(index, 1)
    if (!s.filters.length) delete s.filters
  } else {
    if (index < 0 || index >= s[area].length) return spec
    s[area].splice(index, 1)
  }
  return s
}

/**
 * Drag from one area to another (or within one).
 *
 * Within an area it is a reorder — and a Values entry keeps its function and
 * its name through one. Across areas it is a remove then an add, so every rule
 * `addField` keeps is kept here too; a value dragged into Rows stops being a
 * value, and a row field dragged into Values becomes its default aggregate.
 */
export function moveField(
  spec: PivotSpec,
  from: { area: Area; index: number },
  to: { area: Area; index?: number },
  column?: Pick<Column, 'type'>,
): PivotSpec {
  const col = entryCol(spec, from.area, from.index)
  if (col === null) return spec
  if (from.area === to.area) {
    const s = clone(spec)
    if (from.area === 'values') {
      const [v] = s.values.splice(from.index, 1)
      let at = clampAt(to.index, s.values.length + 1)
      if (at > from.index) at -= 1
      s.values.splice(Math.min(at, s.values.length), 0, v)
      return s
    }
    if (from.area === 'filters') {
      const fs = s.filters!
      const [f] = fs.splice(from.index, 1)
      let at = clampAt(to.index, fs.length + 1)
      if (at > from.index) at -= 1
      fs.splice(Math.min(at, fs.length), 0, f)
      return s
    }
    return addField(spec, col, to.area, { at: to.index })
  }
  const out = removeField(spec, from.area, from.index)
  return addField(out, col, to.area, { at: to.index, column })
}

function entryCol(spec: PivotSpec, area: Area, index: number): string | null {
  if (area === 'values') return spec.values[index]?.col ?? null
  if (area === 'filters') return filterCols(spec)[index] ?? null
  return spec[area][index] ?? null
}

/**
 * Change a value's aggregate. A switch that would make it identical to another
 * value already in the list is refused (spec unchanged), for rule 2.
 */
export function setAgg(spec: PivotSpec, index: number, fn: AggFn): PivotSpec {
  const v = spec.values[index]
  if (!v || v.fn === fn) return spec
  if (spec.values.some((w, i) => i !== index && w.col === v.col && w.fn === fn)) return spec
  const s = clone(spec)
  s.values[index] = { ...s.values[index], fn }
  return s
}

/**
 * Set which values of a Filters-area column are admitted. `null` is "(All)":
 * the field stays in the area and filters nothing. A column not yet in the area
 * is added to it. `null` in `values` is the "(Blanks)" box.
 */
export function setFilterValues(spec: PivotSpec, col: string, values: unknown[] | null): PivotSpec {
  const s = filterCols(spec).includes(col) ? clone(spec) : addField(spec, col, 'filters')
  s.filters = (s.filters ?? []).map((f) => {
    if (f.col !== col) return f
    const next: PivotFilter = { ...f }
    if (values === null) delete next.pred
    else next.pred = { op: 'isOneOf', values: [...values] }
    return next
  })
  return s
}

/**
 * The tick in the field list. Ticked → it leaves every area. Unticked → a
 * measure goes to Values (summed), anything else to Rows — Excel's rule, and
 * the one that makes ticking three boxes produce a pivot somebody wanted.
 */
export function toggleField(spec: PivotSpec, col: string, column?: Pick<Column, 'type'>): PivotSpec {
  if (fieldAreas(spec, col).length) {
    const s = clone(spec)
    ungroup(s, col)
    s.values = s.values.filter((v) => v.col !== col)
    return s
  }
  return addField(spec, col, isMeasure(column) ? 'values' : 'rows', { column })
}

// --- the editor -------------------------------------------------------------

/** Display labels for the aggregate menu. LITERAL t() calls, so the i18n rig
 *  sees every one (cellprops.ts `kindLabel` says why). */
const aggLabel = (fn: AggFn): string =>
  fn === 'sum' ? t('Sum')
    : fn === 'avg' ? t('Average')
      : fn === 'count' ? t('Count')
        : fn === 'min' ? t('Min')
          : fn === 'max' ? t('Max')
            : fn === 'median' ? t('Median')
              : t('Distinct')

const areaLabel = (a: Area): string =>
  a === 'filters' ? t('Filters') : a === 'cols' ? t('Columns') : a === 'rows' ? t('Rows') : t('Values')

const AREAS: readonly Area[] = ['filters', 'cols', 'rows', 'values']

/** The MIME type of a field being dragged — custom, so a drag of some other
 *  text from elsewhere on the page is never mistaken for a field. */
const DRAG_TYPE = 'application/x-bento-dash-pivot-field'

interface DragPayload { col: string; area?: Area; index?: number }

/** How many values a filter checklist lists before it says it stopped. */
const FILTER_CAP = 300

export interface PivotFieldsHost {
  host: HTMLElement
  kit: PanelKit
  /** the pivot sheet being edited */
  sheet: PivotSheet
  /** its source dataset, or null when that sheet is gone */
  source: TableSheet | null
  /** the source's computed columns, for the filter checklist */
  computed?: Map<string, unknown[]>
  readOnly: boolean
  /** write the next spec. The caller commits ONE patch for it. */
  commit(spec: PivotSpec): void
}

const el = <K extends keyof HTMLElementTagNameMap>(
  tag: K, cls?: string, text?: string,
): HTMLElementTagNameMap[K] => {
  const e = document.createElement(tag)
  if (cls) e.className = cls
  if (text !== undefined) e.textContent = text
  return e
}

/** Filter checklists left open survive the rebuild every commit causes. */
const openLists = new Set<string>()

/**
 * Draw the field editor into the properties panel.
 *
 * Re-drawn whole after every commit, like every other section of that panel —
 * the spec is small and the panel is rebuilt from the document, which is what
 * keeps it from disagreeing with an undo.
 */
export function buildPivotFields(o: PivotFieldsHost): void {
  const { host, kit } = o
  const read = readPivotSpec(o.sheet)
  if (!read || !isPivotSheet(o.sheet)) return
  const spec: PivotSpec = read
  const src = o.source
  const colOf = (id: string): Column | undefined => src?.columns.find((c) => c.id === id)
  const nameOf = (id: string): string => colOf(id)?.name ?? id
  const ro = o.readOnly
  const commit = (next: PivotSpec): void => { if (!ro && next !== spec) o.commit(next) }

  kit.section(host, t('Pivot table'))
  kit.readonlyRow(host, t('Source'), src ? src.name : t('(missing sheet)'))
  if (!src) {
    kit.note(host, t('The sheet this pivot summarised is not in the workbook any more.'))
    return
  }

  // --- the field list ---------------------------------------------------------
  kit.section(host, t('Fields'))
  const list = el('div', 'dpf-list')
  list.setAttribute('role', 'list')
  for (const c of src.columns) {
    const used = fieldAreas(spec, c.id)
    const item = el('label', 'dpf-field')
    item.setAttribute('role', 'listitem')
    item.dataset.col = c.id
    item.draggable = !ro
    const box = document.createElement('input')
    box.type = 'checkbox'
    box.checked = used.length > 0
    box.disabled = ro
    box.addEventListener('change', () => commit(toggleField(spec, c.id, c)))
    const name = el('span', 'dpf-field-name', c.name)
    const kind = el('span', 'dpf-field-type', t(TYPE_LABEL[c.type]))
    item.append(box, name, kind)
    if (used.length) {
      item.title = used.map(areaLabel).join(' · ')
      item.classList.add('dpf-used')
    }
    item.addEventListener('dragstart', (e) => startDrag(e, { col: c.id }))
    list.append(item)
  }
  host.append(list)
  // A field dragged back onto the list is taken OUT — Excel's gesture for
  // removing one, and the only place a drag can mean "nowhere".
  dropTarget(list, (p) => {
    if (p.area === undefined || p.index === undefined) return
    commit(removeField(spec, p.area, p.index))
  })
  if (!ro) kit.note(host, t('Drag a field into an area below, or tick it.'))

  // --- the four areas ---------------------------------------------------------
  for (const area of AREAS) {
    kit.section(host, areaLabel(area))
    const well = el('div', 'dpf-well')
    well.dataset.area = area
    const entries: string[] = area === 'values' ? spec.values.map((v) => v.col)
      : area === 'filters' ? filterCols(spec) : spec[area]
    entries.forEach((col, index) => well.append(chip(area, col, index)))
    if (!entries.length) {
      well.append(el('div', 'dpf-empty', area === 'values'
        ? t('Drop a field here to summarise it')
        : t('Drop a field here')))
    }
    if (!ro) well.append(adder(area))
    host.append(well)
    dropTarget(well, (p, e) => {
      const at = dropIndex(well, e.clientY)
      if (p.area === undefined || p.index === undefined) {
        commit(addField(spec, p.col, area, { at, column: colOf(p.col) }))
      } else {
        commit(moveField(spec, { area: p.area, index: p.index }, { area, index: at }, colOf(p.col)))
      }
    })
  }

  // --- options ----------------------------------------------------------------
  kit.section(host, t('Totals'))
  const opt = (label: string, on: boolean, set: (v: boolean) => PivotSpec): void => {
    const cb = kit.check(on, (v) => commit(set(v)))
    cb.disabled = ro
    kit.row(host, label, cb)
  }
  const flag = (key: 'subtotals' | 'grandTotals' | 'blanks', v: boolean): PivotSpec => {
    const s = clone(spec)
    // DEFAULT TRUE, so ON is spelled by ABSENCE: switching one off and back on
    // leaves the file exactly as it was (panels.ts `totalsPatch` makes the same
    // argument), and a collaborator sees no op for a pivot that is unchanged.
    if (v) delete s[key]; else s[key] = false
    return s
  }
  opt(t('Subtotals'), spec.subtotals !== false, (v) => flag('subtotals', v))
  opt(t('Grand totals'), spec.grandTotals !== false, (v) => flag('grandTotals', v))
  opt(t('Show (blank) groups'), spec.blanks !== false, (v) => flag('blanks', v))

  // --- pieces -----------------------------------------------------------------

  function chip(area: Area, col: string, index: number): HTMLElement {
    const c = el('div', 'dpf-chip')
    c.dataset.col = col
    c.dataset.index = String(index)
    c.draggable = !ro
    c.addEventListener('dragstart', (e) => startDrag(e, { col, area, index }))
    const missing = !colOf(col)
    if (missing) c.classList.add('dpf-missing')
    if (area === 'values') {
      const v: PivotValue = spec.values[index]
      const known = (AGG_FNS as string[]).includes(v.fn)
      const sel = kit.select(
        known ? AGG_FNS.map((fn) => [fn, aggLabel(fn)] as const) : [[String(v.fn), String(v.fn)] as const],
        String(v.fn),
        (fn) => commit(setAgg(spec, index, fn as AggFn)))
      sel.className = 'dpf-agg'
      sel.disabled = ro || !known
      sel.title = t('How to summarise {name}', { name: nameOf(col) })
      sel.setAttribute('aria-label', sel.title)
      c.append(sel, el('span', 'dpf-of', t('of')))
    }
    c.append(el('span', 'dpf-chip-name', nameOf(col)))
    if (missing) c.title = t('This column is not in {sheet} any more.', { sheet: src!.name })
    if (area === 'filters') {
      const f = (spec.filters ?? [])[index]
      const btn = el('button', 'dpf-filter-btn', filterSummary(f, col))
      btn.type = 'button'
      btn.title = t('Choose which values to include')
      btn.setAttribute('aria-expanded', String(openLists.has(col)))
      btn.addEventListener('click', () => {
        if (openLists.has(col)) openLists.delete(col); else openLists.add(col)
        const next = c.querySelector('.dpf-check')
        if (next) { next.remove(); btn.setAttribute('aria-expanded', 'false'); return }
        btn.setAttribute('aria-expanded', 'true')
        c.append(checklist(col, f))
      })
      c.append(btn)
    }
    if (!ro) {
      const x = el('button', 'dpf-x', '✕')
      x.type = 'button'
      x.title = t('Remove {name}', { name: nameOf(col) })
      x.setAttribute('aria-label', x.title)
      x.addEventListener('click', () => commit(removeField(spec, area, index)))
      c.append(x)
    }
    if (area === 'filters' && openLists.has(col)) c.append(checklist(col, (spec.filters ?? [])[index]))
    return c
  }

  function adder(area: Area): HTMLElement {
    const opts: Array<readonly [string, string]> = [['', t('Add field…')]]
    for (const c of src!.columns) {
      // A grouping area offers what is not already in it; Values offers every
      // column, because Sum AND Average of one column is a fair question.
      if (area !== 'values' && fieldAreas(spec, c.id).includes(area)) continue
      opts.push([c.id, c.name])
    }
    const sel = kit.select(opts, '', (id) => {
      if (!id) return
      commit(addField(spec, id, area, { column: colOf(id) }))
    })
    sel.className = 'dpf-add'
    sel.title = t('Add a field to {area}', { area: areaLabel(area) })
    sel.setAttribute('aria-label', sel.title)
    return sel
  }

  function filterSummary(f: PivotFilter | undefined, col: string): string {
    const p = f?.pred as { op?: string; values?: unknown[]; set?: Set<unknown> } | undefined
    if (!p) return t('All')
    if (p.op === 'isOneOf') {
      const vals = p.values ?? (p.set ? [...p.set] : [])
      if (vals.length === 1) return vals[0] === null ? t('(Blanks)') : listLabel(vals[0], colOf(col))
      return t('{n} selected', { n: vals.length })
    }
    return t('Custom')
  }

  function checklist(col: string, f: PivotFilter | undefined): HTMLElement {
    const box = el('div', 'dpf-check')
    const column = colOf(col)
    const n = src!.rids.reduce((a, [, k]) => a + k, 0)
    const comp = o.computed?.get(col)
    const get = (_c: string, r: number): unknown => (comp ? comp[r] : readCell(src!.data[col], r))
    const all = distinctValues(get, col, n, FILTER_CAP)
    const p = f?.pred as { op?: string; values?: unknown[] } | undefined
    const ticked = new Set<string>(p?.op === 'isOneOf' && Array.isArray(p.values)
      ? p.values.map(matchKey)
      : all.values.map(matchKey))
    const allBox = document.createElement('input')
    allBox.type = 'checkbox'
    allBox.checked = all.values.every((v) => ticked.has(matchKey(v)))
    allBox.disabled = ro
    const allRow = el('label', 'dpf-check-item dpf-check-all')
    allRow.append(allBox, el('span', '', t('(Select all)')))
    allBox.addEventListener('change', () => commit(setFilterValues(spec, col, allBox.checked ? null : [])))
    box.append(allRow)
    for (const v of all.values) {
      const k = matchKey(v)
      const row = el('label', 'dpf-check-item')
      const cb = document.createElement('input')
      cb.type = 'checkbox'
      cb.checked = ticked.has(k)
      cb.disabled = ro
      cb.dataset.k = k
      cb.addEventListener('change', () => {
        if (cb.checked) ticked.add(k); else ticked.delete(k)
        const keep = all.values.filter((x) => ticked.has(matchKey(x)))
        // EVERYTHING TICKED IS "(All)", spelled by the absent predicate — not a
        // list of today's values, which would silently exclude every value a
        // later edit adds to the column.
        commit(setFilterValues(spec, col, keep.length === all.values.length && !all.truncated ? null : keep))
      })
      row.append(cb, el('span', '', k === BLANK_KEY ? t('(Blanks)') : listLabel(v, column)))
      box.append(row)
    }
    if (all.truncated) {
      box.append(el('div', 'dpf-note', t('Only the first {n} values are listed.', { n: FILTER_CAP })))
    }
    return box
  }

  function startDrag(e: DragEvent, p: DragPayload): void {
    if (ro || !e.dataTransfer) return
    e.dataTransfer.setData(DRAG_TYPE, JSON.stringify(p))
    e.dataTransfer.effectAllowed = 'move'
  }
}

/** Accept a dragged field. Only ours: anything else dragged over is ignored. */
function dropTarget(target: HTMLElement, onDrop: (p: DragPayload, e: DragEvent) => void): void {
  target.addEventListener('dragover', (e) => {
    if (!e.dataTransfer?.types.includes(DRAG_TYPE)) return
    e.preventDefault()
    e.dataTransfer.dropEffect = 'move'
    target.classList.add('dpf-over')
  })
  target.addEventListener('dragleave', (e) => {
    if (!target.contains(e.relatedTarget as Node | null)) target.classList.remove('dpf-over')
  })
  target.addEventListener('drop', (e) => {
    target.classList.remove('dpf-over')
    const raw = e.dataTransfer?.getData(DRAG_TYPE)
    if (!raw) return
    e.preventDefault()
    e.stopPropagation()
    let p: DragPayload
    try { p = JSON.parse(raw) as DragPayload } catch { return }
    if (!p || typeof p.col !== 'string') return
    onDrop(p, e)
  })
}

/** Where in a well a drop at `y` lands: before the first chip whose middle is
 *  below the pointer. */
function dropIndex(well: HTMLElement, y: number): number {
  const chips = [...well.querySelectorAll<HTMLElement>(':scope > .dpf-chip')]
  for (let i = 0; i < chips.length; i++) {
    const r = chips[i].getBoundingClientRect()
    if (y < r.top + r.height / 2) return i
  }
  return chips.length
}