1package functions23import (4 "sync"56 "github.com/cockroachdb/apd/v3"78 "src.rest/cat/012/internal/formula"9 "src.rest/cat/012/internal/numfmt"10 "src.rest/cat/012/internal/value"11)1213// Decimal arithmetic is an opt-in, per-workbook calculation setting for14// money: with it, 0.1+0.2 is exactly 0.3, so =0.1+0.2=0.3 is TRUE and a15// column of cents sums to what an accountant expects. Sheets and Excel use16// binary floating point, so it is off by default.17//18// The boundary is deliberately small. Values stay float64 everywhere (in19// cells, the file, formats and every other function); only these steps20// compute in decimal:21//22// - the operators + - * / and the postfix %23// - SUM, AVERAGE, PRODUCT, SUMIF, SUMIFS and SUMPRODUCT24// - ROUND, ROUNDUP, ROUNDDOWN and TRUNC25//26// Each step reads its float64 operands as the shortest decimal that27// round-trips (what the cell shows and what was typed: 0.1, not28// 0.1000000000000000055...), computes exactly or to 34 significant digits29// (IEEE decimal128) for division, and stores the float64 nearest to that30// decimal result. Comparisons, lookups and everything else then work on31// those float64s, so they see 0.3 rather than 0.30000000000000004.32// Exponents (^), statistics, finance and date functions stay binary. A33// step the decimal context can't represent (overflow past float64 range)34// falls back to the binary result, so the setting never adds errors.35//36// Like any decimal system, it doesn't make 1/3*3 equal 1: the quotient is37// rounded, and a float64 holds about 17 digits of it.3839// decCtx is the context of every decimal step: 34 digits, as decimal128.40var decCtx = func() *apd.Context {41 c := apd.BaseContext.WithPrecision(34)42 c.Rounding = apd.RoundHalfEven43 return c44}()4546// fromDec returns the value nearest to d, or false if it is out of47// float64 range.48func fromDec(d *apd.Decimal) (Value, bool) {49 f, err := d.Float64()50 if err != nil {51 return Value{}, false52 }53 return num(f), true54}5556// decArith computes a op b in decimal, or reports false for operators57// that stay binary and for results it can't represent.58func decArith(op string, a, b float64) (Value, bool) {59 var x, y, z apd.Decimal60 if _, err := x.SetFloat64(a); err != nil {61 return Value{}, false62 }63 if _, err := y.SetFloat64(b); err != nil {64 return Value{}, false65 }66 var err error67 switch op {68 case "+":69 _, err = decCtx.Add(&z, &x, &y)70 case "-":71 _, err = decCtx.Sub(&z, &x, &y)72 case "*":73 _, err = decCtx.Mul(&z, &x, &y)74 case "/":75 if b == 0 {76 return value.ErrDiv0, true77 }78 _, err = decCtx.Quo(&z, &x, &y)79 default:80 return Value{}, false81 }82 if err != nil {83 return Value{}, false84 }85 return fromDec(&z)86}8788// decRounding maps ROUND's modes to apd's: half away from zero, away89// from zero, toward zero.90var decRounding = [...]apd.Rounder{numfmt.HalfUp: apd.RoundHalfUp, numfmt.Up: apd.RoundUp, numfmt.Down: apd.RoundDown}9192// decRound rounds x to places decimal places (negative places round to93// tens, hundreds...) in decimal.94func decRound(x float64, places int, mode numfmt.Rounding) (Value, bool) {95 var d, z apd.Decimal96 if _, err := d.SetFloat64(x); err != nil {97 return Value{}, false98 }99 c := *decCtx100 c.Rounding = decRounding[mode]101 if _, err := c.Quantize(&z, &d, int32(-places)); err != nil {102 return Value{}, false103 }104 return fromDec(&z)105}106107// decAcc accumulates a sum or a product in decimal; ok turns false when108// a step can't be represented, and the caller falls back to binary.109type decAcc struct {110 d, x apd.Decimal111 ok bool112}113114func newDecAcc(start int64) *decAcc {115 a := &decAcc{ok: true}116 a.d.SetInt64(start)117 return a118}119120func (a *decAcc) add(f float64) { a.step(f, decCtx.Add) }121func (a *decAcc) mul(f float64) { a.step(f, decCtx.Mul) }122123func (a *decAcc) step(f float64, op func(d, x, y *apd.Decimal) (apd.Condition, error)) {124 if !a.ok {125 return126 }127 if _, err := a.x.SetFloat64(f); err != nil {128 a.ok = false129 } else if _, err := op(&a.d, &a.d, &a.x); err != nil {130 a.ok = false131 }132}133134// addDec adds a decimal to the sum.135func (a *decAcc) addDec(d *apd.Decimal) {136 if !a.ok {137 return138 }139 if _, err := decCtx.Add(&a.d, &a.d, d); err != nil {140 a.ok = false141 }142}143144// result is the accumulated value, or false to fall back.145func (a *decAcc) result() (Value, bool) {146 if !a.ok {147 return Value{}, false148 }149 return fromDec(&a.d)150}151152// decFold feeds the numbers SUM and PRODUCT take to step and counts153// them: in ranges only numbers, direct arguments coerced.154func decFold(args []Node, get lookup, step func(float64)) (n int, e *Value) {155 e = each(args, get, func(v Value, direct bool) *Value {156 switch v.Kind {157 case value.Error:158 return &v159 case value.Empty:160 return nil161 }162 if v.Kind != value.Number && !direct {163 return nil164 }165 f, err := toNum(v)166 if err != nil {167 return err168 }169 n++170 step(f)171 return nil172 })173 return n, e174}175176// decTerms builds the decimal twin of a function that visits the terms177// it adds (SUMIF, SUMIFS).178func decTerms(terms func([]Node, lookup, func(float64)) *Value) func([]Node, lookup) (Value, bool) {179 return func(args []Node, get lookup) (Value, bool) {180 acc := newDecAcc(0)181 if e := terms(args, get, acc.add); e != nil {182 return *e, true183 }184 return acc.result()185 }186}187188// decSumProduct is SUMPRODUCT with each entry's product and the sum in189// decimal.190func decSumProduct(args []Node, get lookup) (Value, bool) {191 acc := newDecAcc(0)192 e := sumProductTerms(args, get, func(fs []float64) {193 p := newDecAcc(1)194 for _, f := range fs {195 p.mul(f)196 }197 if !p.ok {198 acc.ok = false199 }200 acc.addDec(&p.d)201 })202 if e != nil {203 return *e, true204 }205 return acc.result()206}207208// decEvals are the decimal versions of functions, by name. Each reports209// false to fall back to the binary version.210var decEvals = map[string]func(args []Node, get lookup) (Value, bool){211 "SUM": func(args []Node, get lookup) (Value, bool) {212 acc := newDecAcc(0)213 if _, e := decFold(args, get, acc.add); e != nil {214 return *e, true215 }216 return acc.result()217 },218 "AVERAGE": func(args []Node, get lookup) (Value, bool) {219 acc := newDecAcc(0)220 n, e := decFold(args, get, acc.add)221 switch {222 case e != nil:223 return *e, true224 case n == 0:225 return value.ErrDiv0, true226 case !acc.ok:227 return Value{}, false228 }229 var q apd.Decimal230 if _, err := decCtx.Quo(&q, &acc.d, apd.New(int64(n), 0)); err != nil {231 return Value{}, false232 }233 return fromDec(&q)234 },235 "PRODUCT": func(args []Node, get lookup) (Value, bool) {236 acc := newDecAcc(1)237 n, e := decFold(args, get, acc.mul)238 switch {239 case e != nil:240 return *e, true241 case n == 0:242 return num(0), true243 }244 return acc.result()245 },246 "SUMIF": decTerms(sumIfTerms),247 "SUMIFS": decTerms(sumIfsTerms),248 "SUMPRODUCT": decSumProduct,249 "ROUND": decRounder(numfmt.HalfUp),250 "ROUNDUP": decRounder(numfmt.Up),251 "ROUNDDOWN": decRounder(numfmt.Down),252 "TRUNC": decRounder(numfmt.Down),253}254255func decRounder(mode numfmt.Rounding) func([]Node, lookup) (Value, bool) {256 return func(args []Node, get lookup) (Value, bool) {257 x, err := numArg(args[0], get)258 if err != nil {259 return *err, true260 }261 places, err := intArg(args, 1, 0, get)262 if err != nil {263 return *err, true264 }265 return decRound(x, clampInt(places, -308, 308), mode)266 }267}268269// decFuncs returns the decimal twins of the functions in decEvals: the270// same definitions (name, help, format inference) with a decimal eval271// that falls back to the binary one. Built on first use, after every272// function is defined.273var decFuncs = sync.OnceValue(func() map[string]*FuncDef {274 out := map[string]*FuncDef{}275 for name, dec := range decEvals {276 bin := funcs[name]277 twin := *bin278 twin.eval = func(args []Node, get lookup) Value {279 if v, ok := dec(args, get); ok {280 return v281 }282 return bin.eval(args, get)283 }284 out[name] = &twin285 }286 return out287})288289// Operators computed in decimal are marked by these types, which eval290// knows; parsed formulas never contain them.291type (292 decUnary formula.Unary293 decBinary formula.Binary294)295296// Decimalize returns a copy of n that evaluates in decimal: operators are297// marked, and functions with a decimal twin call it.298func Decimalize(n Node) Node {299 switch n := n.(type) {300 case formula.Unary:301 n.X = Decimalize(n.X)302 return decUnary(n)303 case formula.Binary:304 n.L, n.R = Decimalize(n.L), Decimalize(n.R)305 return decBinary(n)306 case formula.Call:307 n.Args = decimalizeAll(n.Args)308 if twin, ok := decFuncs()[funcOf(n).Name]; ok {309 n.Fn = twin310 }311 return n312 case formula.Invoke:313 n.Fn = Decimalize(n.Fn)314 n.Args = decimalizeAll(n.Args)315 return n316 case formula.Array:317 rows := make([][]Node, len(n.Rows))318 for i, row := range n.Rows {319 rows[i] = decimalizeAll(row)320 }321 return formula.Array{Rows: rows}322 }323 return n324}325326func decimalizeAll(ns []Node) []Node {327 out := make([]Node, len(ns))328 for i, a := range ns {329 out[i] = Decimalize(a)330 }331 return out332}