Mercurial > gemma

comparison pkg/xlsx/templater.go @ 5321:0919946f624b extented-report

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 }