| 1 | package sheets |
| 2 | |
| 3 | import ( |
| 4 | "encoding/json" |
| 5 | "errors" |
| 6 | "sort" |
| 7 | "strconv" |
| 8 | "strings" |
| 9 | |
| 10 | "gorm.io/gorm/clause" |
| 11 | |
| 12 | "amadan.net/oficina/sheets/internal/sheets/store" |
| 13 | ) |
| 14 | |
| 15 | // ViewSpec is how a tab is sorted and filtered. |
| 16 | // |
| 17 | // **Non-destructive, and that is a constraint rather than a |
| 18 | // preference.** calc's dependency graph is keyed on POSITION — |
| 19 | // store/handle.go's cellKey builds CellKey{rowPos+1, colPos+1} — so |
| 20 | // physically reordering rows changes what every existing formula |
| 21 | // points at, silently. Plan 1's review flagged exactly this ("valid |
| 22 | // only until rows move"). Sorting therefore reorders what is DISPLAYED |
| 23 | // and never what is stored; the graph, and every reference in it, is |
| 24 | // untouched. |
| 25 | // |
| 26 | // A destructive sort is a real thing to want and is not built. Its |
| 27 | // trigger is written into the spec's deferred table: it needs calc's |
| 28 | // key space rebuilt on stable ids first, which is a subsystem, not a |
| 29 | // feature. |
| 30 | type ViewSpec struct { |
| 31 | // SortCol is a zero-based column, or -1 for no sort. |
| 32 | SortCol int `json:"sortCol"` |
| 33 | SortDsc bool `json:"sortDesc"` |
| 34 | |
| 35 | // FilterCol is a zero-based column, or -1 for no filter. |
| 36 | FilterCol int `json:"filterCol"` |
| 37 | FilterOp string `json:"filterOp"` |
| 38 | FilterVal string `json:"filterVal"` |
| 39 | } |
| 40 | |
| 41 | // emptyView is what a tab has before anybody sorts it. |
| 42 | func emptyView() ViewSpec { return ViewSpec{SortCol: -1, FilterCol: -1} } |
| 43 | |
| 44 | // Active reports whether this view does anything. |
| 45 | func (v ViewSpec) Active() bool { return v.SortCol >= 0 || v.FilterCol >= 0 } |
| 46 | |
| 47 | // filterOps are the comparisons a filter may use. |
| 48 | // |
| 49 | // A small, closed set. Each is a thing somebody can hold in their head, |
| 50 | // and a closed set is what lets the criteria be validated on the way in |
| 51 | // rather than interpreted on the way out. |
| 52 | var filterOps = []string{"contains", "equals", "gt", "lt", "nonempty"} |
| 53 | |
| 54 | func filterOpValid(op string) bool { |
| 55 | for _, ok := range filterOps { |
| 56 | if op == ok { |
| 57 | return true |
| 58 | } |
| 59 | } |
| 60 | return false |
| 61 | } |
| 62 | |
| 63 | // Validate bounds a spec before it is stored. |
| 64 | func (v ViewSpec) Validate() error { |
| 65 | if v.SortCol < -1 || v.SortCol >= tableCols { |
| 66 | return errors.New("sheets: sort column out of range") |
| 67 | } |
| 68 | if v.FilterCol < -1 || v.FilterCol >= tableCols { |
| 69 | return errors.New("sheets: filter column out of range") |
| 70 | } |
| 71 | if v.FilterCol >= 0 && !filterOpValid(v.FilterOp) { |
| 72 | return errors.New("sheets: unknown filter comparison") |
| 73 | } |
| 74 | if len(v.FilterVal) > maxRawBytes { |
| 75 | return errors.New("sheets: filter value too long") |
| 76 | } |
| 77 | return nil |
| 78 | } |
| 79 | |
| 80 | // loadView reads a tab's view, or the empty one. |
| 81 | func (a *App) loadView(tabID uint64) ViewSpec { |
| 82 | if a.db == nil || a.sealer == nil { |
| 83 | return emptyView() |
| 84 | } |
| 85 | var row TabView |
| 86 | if err := a.db.Where("tab_id = ?", tabID).First(&row).Error; err != nil { |
| 87 | return emptyView() |
| 88 | } |
| 89 | body, err := a.sealer.Open("tab.view", row.SpecSealed) |
| 90 | if err != nil { |
| 91 | return emptyView() |
| 92 | } |
| 93 | spec := emptyView() |
| 94 | if err := json.Unmarshal(body, &spec); err != nil { |
| 95 | return emptyView() |
| 96 | } |
| 97 | if spec.Validate() != nil { |
| 98 | // A stored spec this build cannot make sense of shows the tab |
| 99 | // unsorted rather than refusing to render it. |
| 100 | return emptyView() |
| 101 | } |
| 102 | return spec |
| 103 | } |
| 104 | |
| 105 | // saveView stores a tab's view, sealed. |
| 106 | func (a *App) saveView(tabID uint64, spec ViewSpec, by Member) error { |
| 107 | if a.db == nil { |
| 108 | return errNoDB |
| 109 | } |
| 110 | if a.sealer == nil { |
| 111 | return errNoSealer |
| 112 | } |
| 113 | if err := spec.Validate(); err != nil { |
| 114 | return err |
| 115 | } |
| 116 | body, err := json.Marshal(spec) |
| 117 | if err != nil { |
| 118 | return err |
| 119 | } |
| 120 | sealed, err := a.sealer.Seal("tab.view", body) |
| 121 | if err != nil { |
| 122 | return err |
| 123 | } |
| 124 | return a.db.Clauses(clause.OnConflict{ |
| 125 | Columns: []clause.Column{{Name: "tab_id"}}, |
| 126 | DoUpdates: clause.AssignmentColumns([]string{"spec_sealed", "updated_by", "updated_at"}), |
| 127 | }).Create(&TabView{TabID: tabID, SpecSealed: sealed, UpdatedBy: by.ID}).Error |
| 128 | } |
| 129 | |
| 130 | // applyView turns a stored spec into the order rows should be shown in. |
| 131 | // |
| 132 | // It returns display positions: the row position to render at each |
| 133 | // place. Cells keep their real coordinates everywhere else in the app, |
| 134 | // so nothing downstream has to know a view is in effect — and a write |
| 135 | // still addresses the row it was always addressing. |
| 136 | func applyView(h *store.Handle, spec ViewSpec, first, count int) []int { |
| 137 | rows := make([]int, 0, count) |
| 138 | for _, r := range h.GridRows() { |
| 139 | rows = append(rows, r.Pos) |
| 140 | } |
| 141 | if len(rows) == 0 { |
| 142 | // Nothing stored yet: show the plain window, so an empty sheet |
| 143 | // still has rows to type into. |
| 144 | for i := 0; i < count; i++ { |
| 145 | rows = append(rows, first+i) |
| 146 | } |
| 147 | return rows |
| 148 | } |
| 149 | |
| 150 | idx := newPosIndex(h) |
| 151 | if spec.FilterCol >= 0 { |
| 152 | kept := rows[:0:0] |
| 153 | for _, rp := range rows { |
| 154 | if matches(h, idx, rp, spec) { |
| 155 | kept = append(kept, rp) |
| 156 | } |
| 157 | } |
| 158 | rows = kept |
| 159 | } |
| 160 | if spec.SortCol >= 0 { |
| 161 | sort.SliceStable(rows, func(i, j int) bool { |
| 162 | a := cellText(h, idx, rows[i], spec.SortCol) |
| 163 | b := cellText(h, idx, rows[j], spec.SortCol) |
| 164 | if spec.SortDsc { |
| 165 | return less(b, a) |
| 166 | } |
| 167 | return less(a, b) |
| 168 | }) |
| 169 | } |
| 170 | |
| 171 | // Page after ordering, not before: paging first would sort one |
| 172 | // page at a time, which looks like sorting and is not. |
| 173 | if first >= len(rows) { |
| 174 | return nil |
| 175 | } |
| 176 | end := first + count |
| 177 | if end > len(rows) { |
| 178 | end = len(rows) |
| 179 | } |
| 180 | return rows[first:end] |
| 181 | } |
| 182 | |
| 183 | // cellText reads a cell's displayed string at a position. |
| 184 | func cellText(h *store.Handle, idx posIndex, rowPos, colPos int) string { |
| 185 | rowID, ok := idx.rowID[rowPos] |
| 186 | if !ok { |
| 187 | return "" |
| 188 | } |
| 189 | colID, ok := idx.colID[colPos] |
| 190 | if !ok { |
| 191 | return "" |
| 192 | } |
| 193 | return display(h.Value(rowID, colID), h.Format(rowID, colID)) |
| 194 | } |
| 195 | |
| 196 | // matches applies a filter to one row. |
| 197 | func matches(h *store.Handle, idx posIndex, rowPos int, spec ViewSpec) bool { |
| 198 | got := cellText(h, idx, rowPos, spec.FilterCol) |
| 199 | want := spec.FilterVal |
| 200 | switch spec.FilterOp { |
| 201 | case "nonempty": |
| 202 | return strings.TrimSpace(got) != "" |
| 203 | case "equals": |
| 204 | return strings.EqualFold(strings.TrimSpace(got), strings.TrimSpace(want)) |
| 205 | case "contains": |
| 206 | return strings.Contains(strings.ToLower(got), strings.ToLower(want)) |
| 207 | case "gt", "lt": |
| 208 | g, err1 := strconv.ParseFloat(strings.TrimSpace(got), 64) |
| 209 | wv, err2 := strconv.ParseFloat(strings.TrimSpace(want), 64) |
| 210 | if err1 != nil || err2 != nil { |
| 211 | // A numeric comparison against something that is not a |
| 212 | // number excludes the row rather than guessing an order. |
| 213 | return false |
| 214 | } |
| 215 | if spec.FilterOp == "gt" { |
| 216 | return g > wv |
| 217 | } |
| 218 | return g < wv |
| 219 | } |
| 220 | return true |
| 221 | } |
| 222 | |
| 223 | // less orders two displayed strings, numbers before text. |
| 224 | // |
| 225 | // Numbers compare numerically, so 9 sorts before 10 — string order |
| 226 | // would put 10 first, which is the single most obvious way a |
| 227 | // spreadsheet sort can be wrong. Everything else compares |
| 228 | // case-insensitively, and empties sort last so a sorted column does not |
| 229 | // open with a screen of blanks. |
| 230 | func less(a, b string) bool { |
| 231 | af, aNum := parseNum(a) |
| 232 | bf, bNum := parseNum(b) |
| 233 | switch { |
| 234 | case a == "" && b == "": |
| 235 | return false |
| 236 | case a == "": |
| 237 | return false |
| 238 | case b == "": |
| 239 | return true |
| 240 | case aNum && bNum: |
| 241 | return af < bf |
| 242 | case aNum != bNum: |
| 243 | return aNum |
| 244 | } |
| 245 | return strings.ToLower(a) < strings.ToLower(b) |
| 246 | } |
| 247 | |
| 248 | func parseNum(s string) (float64, bool) { |
| 249 | f, err := strconv.ParseFloat(strings.TrimSpace(s), 64) |
| 250 | return f, err == nil |
| 251 | } |
| 252 | |
amadan