Formulas

import { createGrid, registerFormulaFunction, type Row } from '@freegridjs/core';
import { button, separator, text, tr, type DemoContext } from '../ui';
import '../data'; // registers the grid's language packs

export default function mount(ctx: DemoContext) {
  const { lang } = ctx;
  const h = (en: string, ko: string) => tr(lang, { en, ko });
  const items = [
    [h('Laptop', '노트북'), 2, 1290], [h('Monitor', '모니터'), 4, 320], [h('Keyboard', '키보드'), 10, 59], [h('Mouse', '마우스'), 12, 25],
    [h('Desk', '책상'), 3, 410], [h('Chair', '의자'), 6, 230], [h('Headset', '헤드셋'), 5, 89], [h('Webcam', '웹캠'), 7, 75],
  ] as const;
  const data: Row[] = items.map(([item, qty, price], i) => ({ id: i + 1, item, qty, price, discount: i % 3 === 0 ? 0.1 : 0, note: '' }));
  // a function of your own: formulas can call it like any other
  registerFormulaFunction('VAT', ([amount]) => Math.round(Number(amount) * 0.1));

  const grid = createGrid(ctx.host, {
    columns: [
      { field: 'item', header: h('Item', '품목'), width: 120 },
      { field: 'qty', header: h('Qty', '수량'), type: 'number', width: 80, editable: true },
      { field: 'price', header: h('Price', '단가'), type: 'number', format: '#,##0', width: 100, editable: true },
      { field: 'discount', header: h('Discount', '할인'), type: 'number', format: '0%', width: 90, editable: true },
      // computed columns: a formula on the column, worked out for every row
      { id: 'amount', header: h('Amount', '금액'), type: 'number', format: '#,##0', width: 110, formula: '=ROUND(qty * price * (1 - discount), 0)' },
      { id: 'vat', header: h('VAT', '부가세'), type: 'number', format: '#,##0', width: 90, formula: '=VAT(amount)' },
      { id: 'share', header: h('Share', '비중'), type: 'number', format: '0.0%', width: 90, formula: '=amount / SUM(COL(amount))' },
      { id: 'running', header: h('Running total', '누계'), type: 'number', format: '#,##0', width: 120, formula: '=amount + PREV(running)' },
      { id: 'rank', header: h('Rank', '순위'), type: 'number', width: 70, formula: '=RANK(amount, COL(amount))' },
      { id: 'grade', header: h('Grade', '등급'), width: 90, formula: h('=IFS(share >= 0.25, "A", share >= 0.1, "B", TRUE, "C")', '=IFS(share >= 0.25, "A", share >= 0.1, "B", TRUE, "C")') },
      // a cell formula typed into the data (columns with formulas: true)
      { field: 'note', header: h('Your formula', '직접 입력'), width: 200, editable: true, formulas: true },
    ],
    data, locale: lang, getRowId: r => String(r.id),
    summary: { rows: [{ label: h('Total', '합계'), columns: { amount: 'sum', vat: 'sum' } }] },
  });
  grid.on('cellValueChanged', e => ctx.log(`cellValueChanged → ${e.colId}: ${String(e.oldValue)} → ${String(e.newValue)}`));

  text(ctx, h('Amount, VAT, share, running total, rank and grade are formulas. Edit Qty, Price or Discount and they follow; type =qty*2 or =UPPER(item) into the last column.',
    '금액·부가세·비중·누계·순위·등급은 수식입니다. 수량·단가·할인을 고치면 따라 바뀌고, 마지막 컬럼에 =qty*2나 =UPPER(item)을 입력해 보세요.'));
  separator(ctx);
  button(ctx, h('Sort by amount', '금액순 정렬'), () => grid.setSort([{ colId: 'amount', dir: 'desc' }]));
  button(ctx, h('Price +10%', '단가 10% 인상'), () => {
    grid.applyTransaction({ update: grid.getData().map(r => ({ id: r.id, price: Math.round(Number(r.price) * 1.1) })) });
  });
  button(ctx, h('Fill example formulas', '수식 예시 채우기'), () => {
    const examples = ['=qty * 2', '=UPPER(item)', '=IF(amount > 2000, "big", "small")', '=TEXT(price, "#,##0") & " won"', '=LEN(item)', '=ROUND(amount / qty, 1)', '=YEAR(TODAY())', '=COUNTIF(COL(discount), ">0")'];
    grid.applyTransaction({ update: grid.getData().map((r, i) => ({ id: r.id, note: examples[i % examples.length] })) });
  });
  return grid;
}