Skip to content

Evenfire is an intelligence layer platform for running LLM agents that take real actions, on your infrastructure

TypeScript
12.5 KB
Raw Blame
/**
 * Number formats for XLSX columns: the keywords a caller may ask for, and the
 * format a column gets from its header and values when nobody asked.
 */
import { isModelledFormatCode } from '../artifacts/shownNumbers'
import { own } from './ownEntry'
import { CURRENCY_SYMBOLS } from './xlsxCells'

export const NUMBER_FORMATS = {
  currencyUsd: '"$"#,##0.00_);[Red]("$"#,##0.00)',
  currencyUsdInt: '"$"#,##0_);[Red]("$"#,##0)',
  percent: '0.0%',
  percentInt: '0%',
  integer: '#,##0',
  decimal: '#,##0.00',
  plain: '0',
  datetime: 'yyyy-mm-dd hh:mm',
  date: 'yyyy-mm-dd',
  text: '@',
}

/**
 * How a column is formatted. Decimals left undefined are chosen from the
 * values; `points` percents hold 45 for 45% and are divided by 100.
 */
export type FormatSpec =
  | { type: 'currency'; symbol?: string; decimals?: 0 | 2 }
  | { type: 'percent'; points: boolean; decimals?: 0 | 1 }
  | { type: 'number'; numFmt: string }
  | { type: 'date'; numFmt: string }
  | { type: 'text' }
  | { type: 'custom'; numFmt: string }

export function currencyFormat(symbol: string, decimals: number): string {
  const body = decimals > 0 ? '#,##0.00' : '#,##0'
  const literal = `"${symbol.replace(/"/g, '')}"`
  return `${literal}${body}_);[Red](${literal}${body})`
}

const KEYWORDS: Record<string, FormatSpec> = {
  currencyusd: { type: 'currency', symbol: '$', decimals: 2 },
  currencyusdint: { type: 'currency', symbol: '$', decimals: 0 },
  currency: { type: 'currency' },
  money: { type: 'currency' },
  percent: { type: 'percent', points: false, decimals: 1 },
  percentage: { type: 'percent', points: false, decimals: 1 },
  pct: { type: 'percent', points: false, decimals: 1 },
  '%': { type: 'percent', points: false, decimals: 1 },
  percentint: { type: 'percent', points: false, decimals: 0 },
  percentpoints: { type: 'percent', points: true },
  integer: { type: 'number', numFmt: NUMBER_FORMATS.integer },
  int: { type: 'number', numFmt: NUMBER_FORMATS.integer },
  decimal: { type: 'number', numFmt: NUMBER_FORMATS.decimal },
  number: { type: 'number', numFmt: NUMBER_FORMATS.decimal },
  plain: { type: 'number', numFmt: NUMBER_FORMATS.plain },
  year: { type: 'number', numFmt: NUMBER_FORMATS.plain },
  id: { type: 'number', numFmt: NUMBER_FORMATS.plain },
  date: { type: 'date', numFmt: NUMBER_FORMATS.date },
  datetime: { type: 'date', numFmt: NUMBER_FORMATS.datetime },
  timestamp: { type: 'date', numFmt: NUMBER_FORMATS.datetime },
  text: { type: 'text' },
  string: { type: 'text' },
  '@': { type: 'text' },
}

/**
 * Whether quotes and brackets close and there are at most four sections
 * (positive;negative;zero;text): Excel asks to repair a file with more.
 */
function wellFormedSections(code: string): boolean {
  let sections = 1
  for (let i = 0; i < code.length; i++) {
    const ch = code[i]
    if (ch === '\\') i++
    else if (ch === '"' || ch === '[') {
      const end = code.indexOf(ch === '"' ? '"' : ']', i + 1)
      if (end < 0) return false
      i = end
    } else if (ch === ']') return false
    else if (ch === ';') sections++
  }
  return sections <= 4
}

/** A format code, as opposed to a word: digit placeholders or date parts only. */
function looksLikeFormatCode(code: string): boolean {
  if (/^general$/i.test(code)) return true
  if (!wellFormedSections(code)) return false
  const bare = code
    .replace(/"[^"]*"/g, '')
    .replace(/\[[^\]]*\]/g, '')
    .replace(/\\./g, '')
  return /^[0#?@.,%+\-/ :()ymdhsAMPEe_*;$€£¥]+$/.test(bare) && /[0#?@ymdhs]/i.test(bare)
}

/** The format a `columnFormats` value asks for, or undefined when it names none. */
export function formatSpec(value: unknown): FormatSpec | undefined {
  if (typeof value !== 'string' || !value.trim()) return undefined
  const key = value
    .trim()
    .toLowerCase()
    .replace(/[\s_-]+/g, '')
  const keyword = own(KEYWORDS, key)
  if (keyword) return keyword
  const code = /^(?:currency|money)[:(]?([a-z]{3})\)?$/.exec(key)?.[1] ?? key
  if (/^[a-z]{3}$/.test(code)) {
    const upper = code.toUpperCase()
    if (CURRENCY_SYMBOLS[upper]) return { type: 'currency', symbol: CURRENCY_SYMBOLS[upper] }
    if (code !== key) return { type: 'currency', symbol: `${upper} ` }
  }
  const trimmed = value.trim()
  // A code is written only when the attachment check can read it as shown.
  return looksLikeFormatCode(trimmed) && isModelledFormatCode(trimmed)
    ? { type: 'custom', numFmt: trimmed }
    : undefined
}

/** Header words, lowercased and without accents: "Tasa de conversión" → tasa, de, conversion. */
function headerTokens(header: unknown): string[] {
  return String(header ?? '')
    .replace(/([a-z])([A-Z])/g, '$1 $2')
    .normalize('NFD')
    .replace(/\p{M}/gu, '')
    .toLowerCase()
    .split(/[^a-z0-9]+/)
    .filter(Boolean)
}

const words = (list: string): Set<string> => new Set(list.split(' '))

/** English money words show a dollar sign. */
const MONEY_WORDS_DOLLAR = words(
  'amount amounts revenue revenues price prices cost costs fee fees charge charges payment ' +
    'payments balance balances mrr arr salary salaries spend budget income profit profits expense expenses'
)
/** Spanish and Portuguese money words name no currency, so none is shown. */
const MONEY_WORDS = words(
  'monto montos importe importes ingreso ingresos precio precios costo costos coste costes pago ' +
    'pagos saldo saldos tarifa tarifas gasto gastos presupuesto salario sueldo ganancia ganancias ' +
    'preco precos custo custos receita receitas pagamento pagamentos faturamento montante orcamento'
)
const PERCENT_WORDS = words(
  'pct percent percentage porcentaje porcentagem percentual rate tasa taxa share participacion'
)
/** Rates that are not shares of a whole. */
const NOT_PERCENT_WORDS = words('exchange fx cambio heart hourly frame bit baud')
const DATE_WORDS = words('date dates fecha fechas timestamp datetime')
/** Events that date a row alone or with "at"/"on" ("Created at"); "Accounts created" is a count. */
const DATE_EVENT_WORDS = words('created updated started completed modified')
const DATE_EVENT_LINKS = words('at on el time')
const YEAR_WORDS = words('year years yr fy ano anos anio')
/** Identifiers read as numbers only lose digits or gain separators. */
const ID_WORDS = words(
  'id ids uuid code codes codigo codigos sku ean upc isbn zip zipcode postal postcode cp cep ' +
    'phone phones telephone telefono telefone tel mobile celular movil nif rfc dni cuit cuil ' +
    'iban folio ref reference tracking ticket'
)
/** Things that are numbered; with a word for "number" the header names an identifier. */
const NUMBERED_WORDS = words(
  'invoice factura fatura account cuenta conta employee empleado order pedido customer ' +
    'cliente document documento policy poliza serial contract contrato member socio case caso ' +
    'receipt recibo transaction transaccion'
)
/** "No.", "Nº", "Número": singular only, so "Number of employees" stays a count. */
const NUMBER_WORDS = words('number no num nro numero n')

const HEADER_CODE = new RegExp(
  `(?:^|[^A-Za-z])(${Object.keys(CURRENCY_SYMBOLS).join('|')})(?![A-Za-z])`,
  'gi'
)
/** Codes that are also English words ("pen", "cop", "ars") count only in capitals. */
const WORD_CODES = new Set(['PEN', 'COP', 'ARS'])
const HEADER_SYMBOLS: Array<[string, string]> = [
  ['US$', '$'],
  ['MX$', 'MX$'],
  ['CA$', 'CA$'],
  ['R$', 'R$'],
  ['A$', 'A$'],
  ['€', '€'],
  ['£', '£'],
  ['¥', '¥'],
  ['₹', '₹'],
  ['₩', '₩'],
  ['$', '$'],
]

/** The currency a header names by code or symbol, as the symbol to show. */
export function headerCurrency(header: unknown): string | undefined {
  const text = String(header ?? '')
  for (const [, code] of text.matchAll(HEADER_CODE)) {
    const upper = code.toUpperCase()
    if (!WORD_CODES.has(upper) || code === upper) return CURRENCY_SYMBOLS[upper]
  }
  return HEADER_SYMBOLS.find(([written]) => text.includes(written))?.[1]
}

/**
 * Whether a header names an identifier: an ID, code, postcode or phone number,
 * or a numbered thing such as "Invoice No.", "Invoice #" or "Número de factura".
 */
export function isIdentifierHeader(header: unknown): boolean {
  const tokens = headerTokens(header)
  // "Ticket price" and "Code coverage %" name amounts, not identifiers.
  const amount = [MONEY_WORDS_DOLLAR, MONEY_WORDS, PERCENT_WORDS].some(set =>
    tokens.some(t => set.has(t))
  )
  if (amount || String(header ?? '').includes('%')) return false
  if (tokens.some(t => ID_WORDS.has(t))) return true
  return (
    tokens.some(t => NUMBERED_WORDS.has(t)) &&
    (tokens.some(t => NUMBER_WORDS.has(t)) || String(header ?? '').includes('#'))
  )
}

function maxAbs(values: number[]): number {
  let max = 0
  for (const v of values) if (Math.abs(v) > max) max = Math.abs(v)
  return max
}

// Excel serials for 1950-01-01 and 2100-12-31: a number outside this range
// under a "date" header is a count, not a day.
const SERIAL_MIN = 18264
const SERIAL_MAX = 73415

/** The format a column's header and numbers suggest, when no format was asked for. */
export function detectFormat(header: unknown, numbers: number[]): FormatSpec | undefined {
  if (numbers.length === 0) return undefined
  const tokens = headerTokens(header)
  const has = (set: Set<string>) => tokens.some(t => set.has(t))
  const ints = numbers.every(n => Number.isInteger(n))

  if (isIdentifierHeader(header)) {
    return ints ? { type: 'number', numFmt: NUMBER_FORMATS.plain } : undefined
  }
  const symbol = headerCurrency(header)
  if (symbol || has(MONEY_WORDS_DOLLAR)) {
    return { type: 'currency', symbol: symbol ?? '$' }
  }
  if (has(MONEY_WORDS)) {
    return { type: 'number', numFmt: ints ? NUMBER_FORMATS.integer : NUMBER_FORMATS.decimal }
  }
  const percentSign = String(header ?? '').includes('%')
  if (percentSign || (has(PERCENT_WORDS) && !has(NOT_PERCENT_WORDS))) {
    // Fractions stop at 1 (100%); a column reaching past 1.5 holds points, 45 for
    // 45%. Under a "%" header, values past 1 are points too: "Churn %" of 1.1.
    const max = maxAbs(numbers)
    return { type: 'percent', points: max > 1.5 || (percentSign && max > 1) }
  }
  const dated =
    has(DATE_WORDS) || (has(DATE_EVENT_WORDS) && (tokens.length === 1 || has(DATE_EVENT_LINKS)))
  if (dated && numbers.every(n => n >= SERIAL_MIN && n <= SERIAL_MAX)) {
    return { type: 'date', numFmt: ints ? NUMBER_FORMATS.date : NUMBER_FORMATS.datetime }
  }
  if (has(YEAR_WORDS) || (ints && numbers.every(n => n >= 1900 && n <= 2100))) {
    return { type: 'number', numFmt: NUMBER_FORMATS.plain }
  }
  return { type: 'number', numFmt: ints ? NUMBER_FORMATS.integer : decimalFormat(numbers) }
}

/** Places `n` needs to show without rounding, up to six. */
function placesOf(n: number): number {
  for (let places = 0; places < 6; places++) {
    if (Math.abs(n - Number(n.toFixed(places))) <= 1e-9 * Math.max(1, Math.abs(n))) return places
  }
  return 6
}

/** Two places, or as many as a value needs: a p-value of 0.0012 must not show 0.00. */
function decimalFormat(numbers: number[]): string {
  let places = 2
  for (const n of numbers) places = Math.max(places, placesOf(n))
  return places === 2 ? NUMBER_FORMATS.decimal : `#,##0.${'0'.repeat(places)}`
}

function wholePercents(fractions: number[]): boolean {
  return fractions.every(v => Math.abs(v * 100 - Math.round(v * 100)) < 1e-9)
}

/** The numFmt a percent column gets for these stored fractions. */
export function percentFormat(decimals: 0 | 1 | undefined, fractions: number[]): string {
  const d = decimals ?? (wholePercents(fractions) ? 0 : 1)
  return d === 0 ? NUMBER_FORMATS.percentInt : NUMBER_FORMATS.percent
}

/** Approximate width of a number or date as `numFmt` displays it, in characters. */
export function displayedWidth(value: number | Date, numFmt: string | undefined): number {
  if (value instanceof Date) {
    if (!numFmt || !/h/i.test(numFmt)) return 10
    return /s/i.test(numFmt.replace(/"[^"]*"/g, '')) ? 19 : 16
  }
  if (!numFmt || numFmt === '@' || /^general$/i.test(numFmt)) {
    return Math.min(String(value).length, 11)
  }
  const section = numFmt.split(';')[0]
  const literals = (section.match(/"[^"]*"/g) ?? []).join('').replace(/"/g, '').length
  const bare = section.replace(/"[^"]*"/g, '')
  const decimals = /\.(0+)/.exec(bare)?.[1].length ?? 0
  const percent = bare.includes('%')
  const shown = Math.abs(percent ? value * 100 : value).toLocaleString('en-US', {
    useGrouping: bare.includes(','),
    minimumFractionDigits: decimals,
    maximumFractionDigits: decimals,
  })
  const padding = bare.includes('_') ? 1 : 0
  const sign = value < 0 ? (numFmt.includes('(') ? 2 : 1) : 0
  return shown.length + literals + (percent ? 1 : 0) + padding + sign
}