1package functions23import (4 "cmp"5 "slices"67 "src.rest/cat/012/internal/value"8)910// Functions over aligned ranges (SUMIF, COUNTIFS, SUMPRODUCT...) visit11// only the cells their ranges hold; the rest are blank in all of them,12// so they are accounted for at once. SUMIF(A:A, "", B:B) over ten filled13// rows looks at ten cells, however tall the sheet, and ten cells spread14// over a million rows are still ten.1516// pos is a cell of same-sized matrices, by row and column from their17// top-left corner.18type pos struct{ r, c int }1920// storedPos returns the cells of m that hold something, row by row, and21// false when every cell of its data area must be read instead (as tests22// ask with denseReads).23func (m matrix) storedPos(get lookup) ([]pos, bool) {24 if !m.ref {25 return denseArea(m), true26 }27 if get.dense {28 return nil, false29 }30 r := Rect{From: m.origin, To: Addr{Col: m.origin.Col + m.cols - 1, Row: m.origin.Row + m.rows - 1}}31 var out []pos32 get.stored(m.sheet, r, func(a Addr) bool {33 out = append(out, pos{a.Row - r.From.Row, a.Col - r.From.Col})34 return true35 })36 return out, true37}3839// cellsOf returns the cells to visit of same-sized matrices: those any of40// them holds, in row-major order. Every other cell is blank in all of41// them.42func cellsOf(get lookup, ms ...matrix) []pos {43 var all []pos44 for _, m := range ms {45 p, ok := m.storedPos(get)46 if !ok {47 return denseArea(ms...)48 }49 all = append(all, p...)50 }51 if len(ms) > 1 {52 slices.SortFunc(all, func(a, b pos) int { return cmp.Or(cmp.Compare(a.r, b.r), cmp.Compare(a.c, b.c)) })53 all = slices.Compact(all)54 }55 return all56}5758// denseArea is every cell of the union of the matrices' data areas.59func denseArea(ms ...matrix) []pos {60 rows, cols := 0, 061 for _, m := range ms {62 rows = max(rows, min(m.dataRows, m.rows))63 cols = max(cols, min(m.dataCols, m.cols))64 }65 out := make([]pos, 0, rows*cols)66 for r := range rows {67 for c := range cols {68 out = append(out, pos{r, c})69 }70 }71 return out72}7374// masked is which cells of aligned ranges meet criteria: those visited75// that do, and whether the rest, all blank, do.76type masked struct {77 pass []pos78 tail int // cells not visited79 tailPass bool // whether they meet the criteria80}8182// applyCriteria tests each criterion on its range, over the cells the83// ranges and also (aligned with them) hold.84func applyCriteria(get lookup, ms []matrix, cs []criterion, also ...matrix) masked {85 cells := cellsOf(get, append(also, ms...)...)86 k := masked{tail: ms[0].size() - len(cells), tailPass: true}87 for _, p := range cells {88 if passes(ms, cs, p) {89 k.pass = append(k.pass, p)90 }91 }92 for j, m := range ms {93 k.tailPass = k.tailPass && cs[j].test(m.blank)94 }95 return k96}9798// passes reports whether the cell at p meets every criterion.99func passes(ms []matrix, cs []criterion, p pos) bool {100 for j, m := range ms {101 if !cs[j].test(m.cell(p.r, p.c)) {102 return false103 }104 }105 return true106}107108// sumMasked calls add with the numbers of m where the criteria pass, or109// returns the first error there.110func sumMasked(m matrix, k masked, add func(float64)) *Value {111 for _, p := range k.pass {112 switch v := m.cell(p.r, p.c); v.Kind {113 case value.Error:114 return &v115 case value.Number:116 add(v.Num)117 }118 }119 if k.tail > 0 && k.tailPass && m.blank.Kind == value.Error {120 return &m.blank121 }122 return nil123}124125// averageMasked averages the numbers of m where the criteria pass.126func averageMasked(m matrix, k masked) Value {127 sum, n := 0.0, 0128 for _, p := range k.pass {129 switch v := m.cell(p.r, p.c); v.Kind {130 case value.Error:131 return v132 case value.Number:133 sum += v.Num134 n++135 }136 }137 switch {138 case k.tail > 0 && k.tailPass && m.blank.Kind == value.Error:139 return m.blank140 case n == 0:141 return value.ErrDiv0142 }143 return num(sum / float64(n))144}145146// sumIf, sumIfs and sumProduct add in binary; their terms functions147// visit the same terms for decimal arithmetic too.148func sumIf(args []Node, get lookup) Value {149 total := 0.0150 if e := sumIfTerms(args, get, func(f float64) { total += f }); e != nil {151 return *e152 }153 return num(total)154}155156func sumIfs(args []Node, get lookup) Value {157 total := 0.0158 if e := sumIfsTerms(args, get, func(f float64) { total += f }); e != nil {159 return *e160 }161 return num(total)162}163164func sumProduct(args []Node, get lookup) Value {165 total := 0.0166 e := sumProductTerms(args, get, func(fs []float64) {167 p := 1.0168 for _, f := range fs {169 p *= f170 }171 total += p172 })173 if e != nil {174 return *e175 }176 return num(total)177}178179// sumIfTerms calls add with each number SUMIF sums, or returns the error180// it gives.181func sumIfTerms(args []Node, get lookup, add func(float64)) *Value {182 rng := matrixArg(args[0], get)183 cv := eval(args[1], get)184 if cv.Kind == value.Error {185 return &cv186 }187 sum := rng188 if len(args) > 2 {189 sum = matrixArg(args[2], get).resized(rng.rows, rng.cols, get)190 if sum.rows != rng.rows || sum.cols != rng.cols {191 return &value.ErrValue192 }193 }194 if streamable(get, rng, sum) {195 return sumStream(get, sum, []matrix{rng}, []criterion{newCriterion(cv)}, add)196 }197 return sumMasked(sum, applyCriteria(get, []matrix{rng}, []criterion{newCriterion(cv)}, sum), add)198}199200// sumIfsTerms calls add with each number SUMIFS sums, or returns the201// error it gives.202func sumIfsTerms(args []Node, get lookup, add func(float64)) *Value {203 sum := matrixArg(args[0], get)204 ms, cs, err := criteriaArgs(args, 1, get)205 switch {206 case err != nil:207 return err208 case len(ms) == 0 || ms[0].rows != sum.rows || ms[0].cols != sum.cols:209 return &value.ErrValue210 case streamable(get, append(ms, sum)...):211 return sumStream(get, sum, ms, cs, add)212 }213 return sumMasked(sum, applyCriteria(get, ms, cs, sum), add)214}215216func averageIf(args []Node, get lookup) Value {217 rng := matrixArg(args[0], get)218 cv := eval(args[1], get)219 if cv.Kind == value.Error {220 return cv221 }222 avg := rng223 if len(args) > 2 {224 avg = matrixArg(args[2], get).resized(rng.rows, rng.cols, get)225 }226 if streamable(get, rng, avg) && avg.rows == rng.rows && avg.cols == rng.cols {227 return averageStream(get, avg, []matrix{rng}, []criterion{newCriterion(cv)})228 }229 return averageMasked(avg, applyCriteria(get, []matrix{rng}, []criterion{newCriterion(cv)}, avg))230}231232func averageIfs(args []Node, get lookup) Value {233 avg := matrixArg(args[0], get)234 ms, cs, err := criteriaArgs(args, 1, get)235 switch {236 case err != nil:237 return *err238 case len(ms) == 0 || ms[0].rows != avg.rows || ms[0].cols != avg.cols:239 return value.ErrValue240 case streamable(get, append(ms, avg)...):241 return averageStream(get, avg, ms, cs)242 }243 return averageMasked(avg, applyCriteria(get, ms, cs, avg))244}245246func countIfs(args []Node, get lookup) Value {247 ms, cs, err := criteriaArgs(args, 0, get)248 if err != nil {249 return *err250 }251 if streamable(get, ms...) {252 return countStream(get, ms, cs)253 }254 k := applyCriteria(get, ms, cs)255 n := len(k.pass)256 if k.tailPass {257 n += k.tail258 }259 return num(float64(n))260}261262func countBlank(args []Node, get lookup) Value {263 m := matrixArg(args[0], get)264 if streamable(get, m) {265 return blankStream(get, m)266 }267 isBlank := func(v Value) bool { return v.Kind == value.Empty || v.Kind == value.Text && v.Str == "" }268 cells := cellsOf(get, m)269 n := 0270 for _, p := range cells {271 if isBlank(m.cell(p.r, p.c)) {272 n++273 }274 }275 if isBlank(m.blank) {276 n += m.size() - len(cells)277 }278 return num(float64(n))279}280281// sumProductTerms calls term with the factors of each entry of282// same-sized arrays that any of them holds (text and blanks count as 0;283// entries blank in all of them are 0 and left out), or returns the284// error SUMPRODUCT gives. The slice is reused between calls.285func sumProductTerms(args []Node, get lookup, term func(fs []float64)) *Value {286 ms := make([]matrix, len(args))287 for i, a := range args {288 ms[i] = matrixArg(a, get)289 if ms[i].rows != ms[0].rows || ms[i].cols != ms[0].cols {290 return &value.ErrValue291 }292 }293 if streamable(get, ms...) {294 return productStream(get, ms, term)295 }296 cells := cellsOf(get, ms...)297 fs := make([]float64, len(ms))298 for _, pc := range cells {299 for i, m := range ms {300 switch v := m.cell(pc.r, pc.c); v.Kind {301 case value.Error:302 return &v303 case value.Number, value.Bool:304 fs[i] = v.Num // TRUE is 1, as in Sheets305 default:306 fs[i] = 0307 }308 }309 term(fs)310 }311 if ms[0].size() > len(cells) {312 for _, m := range ms {313 if m.blank.Kind == value.Error {314 return &m.blank315 }316 }317 }318 return nil319}