Formulas

import { createDataGrid, formula, sorting } from '@sv5ui/datagrid';

const grid = createDataGrid<Line>({
  columns: [
    { id: 'sku',     header: 'SKU' },
    { id: 'qty',     header: 'Qty',       align: 'right' },
    { id: 'price',   header: 'Price',     align: 'right', type: 'currency' },
    { id: 'total',   header: 'Total',     align: 'right', type: 'currency' },
    { id: 'withVat', header: 'With VAT',  align: 'right', type: 'currency' },
    { id: 'band',    header: 'Band' },
    { id: 'label',   header: 'Label' }
  ],
  data: lines,
  getRowId: (line) => String(line.id),
  features: [
    sorting<Line>(),
    formula({
      columns: {
        total:   '[qty] * [price]',
        withVat: 'ROUND([total] * 1.1, 2)',
        band:    'IF([total] > 500, "large", IF([total] > 200, "medium", "small"))',
        label:   'UPPER(LEFT([sku], 2)) & "-" & TEXT([qty])'
      }
    })
  ]
});

// [id] reads another column, and whitespace inside the brackets is kept, so
// [unit price] is a column with a space in its id.

import { isFormulaError } from '@sv5ui/datagrid';

// What the grid above runs. Its three rows differ, so a column is seen both
// failing and answering: qty is 0, 4, 8 and text is 'abc', 'abc', '12'.
formula({
  columns: {
    divZero:   '10 / [qty]',        // #DIV/0 on row 1 only
    badName:   'NOSUCH([qty])',     // #NAME  on every row
    wrongType: '[text] * 2',        // #VALUE until text holds a number
    negRoot:   'SQRT(0 - 1)',       // #NUM   on every row
    quiet:     { expression: '10 / [qty]', onError: 'blank' }
  }
});

// onParseError is a separate thing, and nothing above reaches it: it fires
// once at registration for an expression that will not parse at all.
formula({
  columns: { broken: '[qty] * ' },
  onParseError: (columnId, message, at) => {
    console.warn(`${columnId}: ${message} at character ${at}`);
  }
});

const value = grid.getValue(node, column);
if (isFormulaError(value)) {
  console.warn(value.code, value.detail);
  // '#CYCLE', '"total" refers back to itself'
}

formula({
  columns: {
    a: '[b] + 1',
    b: '[a] + 1',
    fine: '[qty] * 2'
  }
});

// a and b read #CYCLE down their length. fine keeps working: only the
// columns actually caught in the cycle are refused.

import { FUNCTION_NAMES } from '@sv5ui/datagrid';

FUNCTION_NAMES.length;   // 31, and nothing else resolves

formula({
  columns: {
    a: 'globalThis',          // a column called globalThis: the cell is blank
    b: 'fetch("/admin")',     // an unknown function: the cell reads #NAME
    c: '[qty] * '             // will not parse: onParseError fires once, and
                              // every cell of the column reads #NAME
  },
  onParseError: (columnId, message, at) => console.warn(columnId, message, at)
});

// A bare word is a column read, which is what makes a hostile expression
// inert. The parser also caps an expression at 32 levels of nesting and 512
// parts, both refused while it is being read.

const grid = createDataGrid<ServerRow>({
  columns, data: [], getRowId,
  rowModel: 'server',
  features: [
    sorting(),
    // Computed in the browser, per row, as each block arrives.
    formula<ServerRow>({
      columns: {
        monthly: 'ROUND([salary] / 12, 2)',
        band: 'IF([salary] > 120000, "senior", IF([salary] > 90000, "mid", "junior"))'
      }
    }),
    virtualization({ rowHeight: 40, overscan: 6 }),
    serverRowModel(source, {
      mode: 'infinite',
      blockSize: 100,
      // A formula column needs a value on a row that has not arrived, so the
      // placeholder carries one.
      placeholder: (index) => ({ ...blankPerson, id: -1 - index, monthly: 0, band: '' })
    })
  ]
});

// Sorting by salary is answered by the server. Sorting by monthly is not:
// toSortRequest sends { field: 'monthly' }, the backend has no such column,
// and the rows come back in the order they were already in.
//
// The grid cannot fall back to sorting them itself, because it holds one
// block of five thousand rows: sorting those would put the wrong hundred at
// the top and call it the answer.
//
// Two honest ways out. Say the column cannot be ordered:
{ id: 'monthly', header: 'Monthly', sortable: false }

// Or stop making it a formula, and let the backend send it as a column:
SELECT salary, ROUND(salary / 12, 2) AS monthly FROM people ORDER BY monthly