Mercurial > gemma
comparison pkg/xlsx/templater.go @ 5321:0919946f624b extented-report
Added a report controller.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 27 May 2021 01:22:10 +0200 |
parents | |
children | 313bf3f3a8b1 |
comparison
equal
deleted
inserted
replaced
5320:866eae1bd888 | 5321:0919946f624b |
---|---|
1 // This is Free Software under GNU Affero General Public License v >= 3.0 | |
2 // without warranty, see README.md and license for details. | |
3 // | |
4 // SPDX-License-Identifier: AGPL-3.0-or-later | |
5 // License-Filename: LICENSES/AGPL-3.0.txt | |
6 // | |
7 // Copyright (C) 2021 by via donau | |
8 // – Österreichische Wasserstraßen-Gesellschaft mbH | |
9 // Software engineering by Intevation GmbH | |
10 // | |
11 // Author(s): | |
12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de> | |
13 | |
14 package xlsx | |
15 | |
16 import ( | |
17 "bufio" | |
18 "context" | |
19 "database/sql" | |
20 "errors" | |
21 "fmt" | |
22 "io" | |
23 "os" | |
24 "strings" | |
25 | |
26 "github.com/360EntSecGroup-Skylar/excelize/v2" | |
27 "gopkg.in/yaml.v2" | |
28 | |
29 "github.com/PaesslerAG/gval" | |
30 ) | |
31 | |
32 type Action struct { | |
33 Type string `yaml:"type"` | |
34 Actions []*Action `yaml:"actions"` | |
35 Location []string `yaml:"location"` | |
36 Source string `yaml:"source"` | |
37 Destination string `yaml:"destination"` | |
38 Statement string `yaml:"statement"` | |
39 Vars []string `yaml:"vars"` | |
40 Name string `yaml:"name"` | |
41 Expr string `yaml:"expr"` | |
42 } | |
43 | |
44 type frame struct { | |
45 res *sqlResult | |
46 index int | |
47 } | |
48 | |
49 type executor struct { | |
50 ctx context.Context | |
51 db *sql.Conn | |
52 template *excelize.File | |
53 keep map[string]bool | |
54 expressions map[string]gval.Evaluable | |
55 sourceSheet string | |
56 destinationSheet string | |
57 frames []frame | |
58 } | |
59 | |
60 func ActionFromFile(filename string) (*Action, error) { | |
61 f, err := os.Open(filename) | |
62 if err != nil { | |
63 return nil, err | |
64 } | |
65 defer f.Close() | |
66 return ActionFromReader(f) | |
67 } | |
68 | |
69 func ActionFromReader(r io.Reader) (*Action, error) { | |
70 action := new(Action) | |
71 err := yaml.NewDecoder(bufio.NewReader(r)).Decode(action) | |
72 return action, err | |
73 } | |
74 | |
75 func (a *Action) Execute( | |
76 ctx context.Context, | |
77 db *sql.Conn, | |
78 template *excelize.File, | |
79 ) error { | |
80 | |
81 e := executor{ | |
82 ctx: ctx, | |
83 db: db, | |
84 template: template, | |
85 keep: map[string]bool{}, | |
86 expressions: map[string]gval.Evaluable{}, | |
87 } | |
88 | |
89 oldSheets := template.GetSheetList() | |
90 | |
91 if err := e.dispatch(a); err != nil { | |
92 return err | |
93 } | |
94 | |
95 for _, sheet := range oldSheets { | |
96 if !e.keep[sheet] { | |
97 template.DeleteSheet(sheet) | |
98 } | |
99 } | |
100 return nil | |
101 } | |
102 | |
103 func (e *executor) dispatch(action *Action) error { | |
104 if len(action.Vars) > 0 { | |
105 e.pushVars(action.Vars) | |
106 defer e.popFrame() | |
107 } | |
108 switch action.Type { | |
109 case "sheet": | |
110 return e.sheet(action) | |
111 case "copy": | |
112 return e.copy(action) | |
113 case "select": | |
114 return e.sel(action) | |
115 case "assign": | |
116 return e.assign(action) | |
117 case "": | |
118 return e.actions(action) | |
119 } | |
120 return fmt.Errorf("unknown type '%s'", action.Type) | |
121 } | |
122 | |
123 func (e *executor) pushVars(vars []string) { | |
124 e.frames = append(e.frames, frame{ | |
125 res: &sqlResult{ | |
126 columns: vars, | |
127 rows: [][]interface{}{make([]interface{}, len(vars))}, | |
128 }, | |
129 }) | |
130 } | |
131 | |
132 func (e *executor) popFrame() { | |
133 n := len(e.frames) | |
134 e.frames[n-1].res = nil | |
135 e.frames = e.frames[:n-1] | |
136 } | |
137 | |
138 func (e *executor) assign(action *Action) error { | |
139 if action.Name == "" { | |
140 return errors.New("missing name in assign") | |
141 } | |
142 if action.Expr == "" { | |
143 return errors.New("missing expr in assign") | |
144 } | |
145 | |
146 for i := len(e.frames) - 1; i >= 0; i-- { | |
147 fr := &e.frames[i] | |
148 if idx := fr.res.find(action.Name); idx >= 0 { | |
149 f, err := e.expr(action.Expr) | |
150 if err != nil { | |
151 return err | |
152 } | |
153 value, err := f(e.ctx, e.vars()) | |
154 if err != nil { | |
155 return err | |
156 } | |
157 fr.res.rows[fr.index][idx] = value | |
158 break | |
159 } | |
160 } | |
161 return e.actions(action) | |
162 } | |
163 | |
164 func (e *executor) copy(action *Action) error { | |
165 if len(action.Location) != 2 { | |
166 return fmt.Errorf("length location = %d (expect 2)", | |
167 len(action.Source)) | |
168 } | |
169 | |
170 vars := e.vars() | |
171 | |
172 var err error | |
173 expand := func(s string) string { | |
174 if err == nil { | |
175 s, err = e.expand(s, vars) | |
176 } | |
177 return s | |
178 } | |
179 split := func(s string) (int, int) { | |
180 var x, y int | |
181 if err == nil { | |
182 var cell string | |
183 if cell, y, err = excelize.SplitCellName(s); err == nil { | |
184 x, err = excelize.ColumnNameToNumber(cell) | |
185 } | |
186 } | |
187 return x, y | |
188 } | |
189 order := func(a, b int) (int, int) { | |
190 if a > b { | |
191 return b, a | |
192 } | |
193 return a, b | |
194 } | |
195 | |
196 var ( | |
197 s1 = expand(action.Location[0]) | |
198 s2 = expand(action.Location[1]) | |
199 d1 = expand(action.Destination) | |
200 sx1, sy1 = split(s1) | |
201 sx2, sy2 = split(s2) | |
202 dx1, dy1 = split(d1) | |
203 ) | |
204 if err != nil { | |
205 return err | |
206 } | |
207 sx1, sx2 = order(sx1, sx2) | |
208 sy1, sy1 = order(sy1, sy2) | |
209 | |
210 //log.Printf("%s/%s -> %s\n", sFrom, sTo, dTo) | |
211 | |
212 for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 { | |
213 for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 { | |
214 src, err1 := excelize.CoordinatesToCellName(x, y) | |
215 dst, err2 := excelize.CoordinatesToCellName(dx1+j, dy1+i) | |
216 if err1 != nil || err2 != nil { | |
217 continue | |
218 } | |
219 if s, err := e.template.GetCellStyle(e.sourceSheet, src); err == nil { | |
220 e.template.SetCellStyle(e.destinationSheet, dst, dst, s) | |
221 } | |
222 if s, err := e.template.GetCellFormula(e.sourceSheet, src); err == nil { | |
223 e.template.SetCellFormula(e.destinationSheet, dst, s) | |
224 } | |
225 if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil { | |
226 if s, err = e.expand(s, vars); err == nil { | |
227 e.template.SetCellStr(e.destinationSheet, dst, s) | |
228 } | |
229 } | |
230 } | |
231 } | |
232 | |
233 return nil | |
234 } | |
235 | |
236 func (e *executor) sel(action *Action) error { | |
237 vars := e.vars() | |
238 | |
239 eval := func(x string) (interface{}, error) { | |
240 f, err := e.expr(x) | |
241 if err != nil { | |
242 return nil, err | |
243 } | |
244 return f(e.ctx, vars) | |
245 } | |
246 | |
247 res, err := query(e.ctx, e.db, action.Statement, eval) | |
248 if err != nil { | |
249 return err | |
250 } | |
251 | |
252 e.frames = append(e.frames, frame{res: res}) | |
253 defer e.popFrame() | |
254 | |
255 for i := range res.rows { | |
256 e.frames[len(e.frames)-1].index = i | |
257 if err := e.actions(action); err != nil { | |
258 return err | |
259 } | |
260 } | |
261 | |
262 return nil | |
263 } | |
264 | |
265 func (e *executor) actions(action *Action) error { | |
266 for _, a := range action.Actions { | |
267 if err := e.dispatch(a); err != nil { | |
268 return err | |
269 } | |
270 } | |
271 return nil | |
272 } | |
273 | |
274 func (e *executor) sheet(action *Action) error { | |
275 | |
276 vars := e.vars() | |
277 source, err := e.expand(action.Source, vars) | |
278 if err != nil { | |
279 return err | |
280 } | |
281 | |
282 srcIdx := e.template.GetSheetIndex(source) | |
283 if srcIdx == -1 { | |
284 return fmt.Errorf("sheet '%s' not found", source) | |
285 } | |
286 | |
287 destination := action.Destination | |
288 if destination == "" { // same as source | |
289 e.keep[source] = true | |
290 destination = source | |
291 } else { // new sheet | |
292 destination, err = e.expand(destination, vars) | |
293 if err != nil { | |
294 return err | |
295 } | |
296 dstIdx := e.template.NewSheet(destination) | |
297 if len(action.Actions) == 0 { | |
298 // Only copy if there are no explicit instructions. | |
299 if err := e.template.CopySheet(srcIdx, dstIdx); err != nil { | |
300 return err | |
301 } | |
302 } | |
303 } | |
304 | |
305 if len(action.Actions) > 0 { | |
306 pSrc, pDst := e.sourceSheet, e.destinationSheet | |
307 defer func() { | |
308 e.sourceSheet, e.destinationSheet = pSrc, pDst | |
309 }() | |
310 e.sourceSheet, e.destinationSheet = source, destination | |
311 return e.actions(action) | |
312 } | |
313 | |
314 // Simple filling | |
315 | |
316 // "{{" only as a quick filter | |
317 result, err := e.template.SearchSheet(destination, "{{", true) | |
318 if err != nil { | |
319 return err | |
320 } | |
321 for _, axis := range result { | |
322 value, err := e.template.GetCellValue(destination, axis) | |
323 if err != nil { | |
324 return err | |
325 } | |
326 value, err = e.expand(value, vars) | |
327 if err != nil { | |
328 return err | |
329 } | |
330 if err := e.template.SetCellStr(destination, axis, value); err != nil { | |
331 return err | |
332 } | |
333 } | |
334 | |
335 return nil | |
336 } | |
337 | |
338 func (e *executor) expr(x string) (gval.Evaluable, error) { | |
339 if f := e.expressions[x]; f != nil { | |
340 return f, nil | |
341 } | |
342 f, err := gval.Full().NewEvaluable(x) | |
343 if err != nil { | |
344 return nil, err | |
345 } | |
346 e.expressions[x] = f | |
347 return f, nil | |
348 } | |
349 | |
350 func (e *executor) vars() map[string]interface{} { | |
351 vars := map[string]interface{}{} | |
352 if len(e.frames) > 0 { | |
353 vars["row_number"] = e.frames[len(e.frames)-1].index | |
354 } | |
355 for i := len(e.frames) - 1; i >= 0; i-- { | |
356 fr := &e.frames[i] | |
357 for j, n := range fr.res.columns { | |
358 if _, found := vars[n]; !found { | |
359 vars[n] = fr.res.rows[fr.index][j] | |
360 } | |
361 } | |
362 } | |
363 return vars | |
364 } | |
365 | |
366 func (e *executor) expand(str string, vars map[string]interface{}) (string, error) { | |
367 | |
368 var err error | |
369 | |
370 replace := func(s string) string { | |
371 if err != nil { | |
372 return "" | |
373 } | |
374 var eval gval.Evaluable | |
375 if eval, err = e.expr(strings.TrimSpace(s)); err != nil { | |
376 return "" | |
377 } | |
378 s, err = eval.EvalString(e.ctx, vars) | |
379 return s | |
380 } | |
381 | |
382 str = handlebars(str, replace) | |
383 return str, err | |
384 } |