Mercurial > gemma
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/xlsx/templater.go Thu May 27 01:22:10 2021 +0200 @@ -0,0 +1,384 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2021 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +package xlsx + +import ( + "bufio" + "context" + "database/sql" + "errors" + "fmt" + "io" + "os" + "strings" + + "github.com/360EntSecGroup-Skylar/excelize/v2" + "gopkg.in/yaml.v2" + + "github.com/PaesslerAG/gval" +) + +type Action struct { + Type string `yaml:"type"` + Actions []*Action `yaml:"actions"` + Location []string `yaml:"location"` + Source string `yaml:"source"` + Destination string `yaml:"destination"` + Statement string `yaml:"statement"` + Vars []string `yaml:"vars"` + Name string `yaml:"name"` + Expr string `yaml:"expr"` +} + +type frame struct { + res *sqlResult + index int +} + +type executor struct { + ctx context.Context + db *sql.Conn + template *excelize.File + keep map[string]bool + expressions map[string]gval.Evaluable + sourceSheet string + destinationSheet string + frames []frame +} + +func ActionFromFile(filename string) (*Action, error) { + f, err := os.Open(filename) + if err != nil { + return nil, err + } + defer f.Close() + return ActionFromReader(f) +} + +func ActionFromReader(r io.Reader) (*Action, error) { + action := new(Action) + err := yaml.NewDecoder(bufio.NewReader(r)).Decode(action) + return action, err +} + +func (a *Action) Execute( + ctx context.Context, + db *sql.Conn, + template *excelize.File, +) error { + + e := executor{ + ctx: ctx, + db: db, + template: template, + keep: map[string]bool{}, + expressions: map[string]gval.Evaluable{}, + } + + oldSheets := template.GetSheetList() + + if err := e.dispatch(a); err != nil { + return err + } + + for _, sheet := range oldSheets { + if !e.keep[sheet] { + template.DeleteSheet(sheet) + } + } + return nil +} + +func (e *executor) dispatch(action *Action) error { + if len(action.Vars) > 0 { + e.pushVars(action.Vars) + defer e.popFrame() + } + switch action.Type { + case "sheet": + return e.sheet(action) + case "copy": + return e.copy(action) + case "select": + return e.sel(action) + case "assign": + return e.assign(action) + case "": + return e.actions(action) + } + return fmt.Errorf("unknown type '%s'", action.Type) +} + +func (e *executor) pushVars(vars []string) { + e.frames = append(e.frames, frame{ + res: &sqlResult{ + columns: vars, + rows: [][]interface{}{make([]interface{}, len(vars))}, + }, + }) +} + +func (e *executor) popFrame() { + n := len(e.frames) + e.frames[n-1].res = nil + e.frames = e.frames[:n-1] +} + +func (e *executor) assign(action *Action) error { + if action.Name == "" { + return errors.New("missing name in assign") + } + if action.Expr == "" { + return errors.New("missing expr in assign") + } + + for i := len(e.frames) - 1; i >= 0; i-- { + fr := &e.frames[i] + if idx := fr.res.find(action.Name); idx >= 0 { + f, err := e.expr(action.Expr) + if err != nil { + return err + } + value, err := f(e.ctx, e.vars()) + if err != nil { + return err + } + fr.res.rows[fr.index][idx] = value + break + } + } + return e.actions(action) +} + +func (e *executor) copy(action *Action) error { + if len(action.Location) != 2 { + return fmt.Errorf("length location = %d (expect 2)", + len(action.Source)) + } + + vars := e.vars() + + var err error + expand := func(s string) string { + if err == nil { + s, err = e.expand(s, vars) + } + return s + } + split := func(s string) (int, int) { + var x, y int + if err == nil { + var cell string + if cell, y, err = excelize.SplitCellName(s); err == nil { + x, err = excelize.ColumnNameToNumber(cell) + } + } + return x, y + } + order := func(a, b int) (int, int) { + if a > b { + return b, a + } + return a, b + } + + var ( + s1 = expand(action.Location[0]) + s2 = expand(action.Location[1]) + d1 = expand(action.Destination) + sx1, sy1 = split(s1) + sx2, sy2 = split(s2) + dx1, dy1 = split(d1) + ) + if err != nil { + return err + } + sx1, sx2 = order(sx1, sx2) + sy1, sy1 = order(sy1, sy2) + + //log.Printf("%s/%s -> %s\n", sFrom, sTo, dTo) + + for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 { + for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 { + src, err1 := excelize.CoordinatesToCellName(x, y) + dst, err2 := excelize.CoordinatesToCellName(dx1+j, dy1+i) + if err1 != nil || err2 != nil { + continue + } + if s, err := e.template.GetCellStyle(e.sourceSheet, src); err == nil { + e.template.SetCellStyle(e.destinationSheet, dst, dst, s) + } + if s, err := e.template.GetCellFormula(e.sourceSheet, src); err == nil { + e.template.SetCellFormula(e.destinationSheet, dst, s) + } + if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil { + if s, err = e.expand(s, vars); err == nil { + e.template.SetCellStr(e.destinationSheet, dst, s) + } + } + } + } + + return nil +} + +func (e *executor) sel(action *Action) error { + vars := e.vars() + + eval := func(x string) (interface{}, error) { + f, err := e.expr(x) + if err != nil { + return nil, err + } + return f(e.ctx, vars) + } + + res, err := query(e.ctx, e.db, action.Statement, eval) + if err != nil { + return err + } + + e.frames = append(e.frames, frame{res: res}) + defer e.popFrame() + + for i := range res.rows { + e.frames[len(e.frames)-1].index = i + if err := e.actions(action); err != nil { + return err + } + } + + return nil +} + +func (e *executor) actions(action *Action) error { + for _, a := range action.Actions { + if err := e.dispatch(a); err != nil { + return err + } + } + return nil +} + +func (e *executor) sheet(action *Action) error { + + vars := e.vars() + source, err := e.expand(action.Source, vars) + if err != nil { + return err + } + + srcIdx := e.template.GetSheetIndex(source) + if srcIdx == -1 { + return fmt.Errorf("sheet '%s' not found", source) + } + + destination := action.Destination + if destination == "" { // same as source + e.keep[source] = true + destination = source + } else { // new sheet + destination, err = e.expand(destination, vars) + if err != nil { + return err + } + dstIdx := e.template.NewSheet(destination) + if len(action.Actions) == 0 { + // Only copy if there are no explicit instructions. + if err := e.template.CopySheet(srcIdx, dstIdx); err != nil { + return err + } + } + } + + if len(action.Actions) > 0 { + pSrc, pDst := e.sourceSheet, e.destinationSheet + defer func() { + e.sourceSheet, e.destinationSheet = pSrc, pDst + }() + e.sourceSheet, e.destinationSheet = source, destination + return e.actions(action) + } + + // Simple filling + + // "{{" only as a quick filter + result, err := e.template.SearchSheet(destination, "{{", true) + if err != nil { + return err + } + for _, axis := range result { + value, err := e.template.GetCellValue(destination, axis) + if err != nil { + return err + } + value, err = e.expand(value, vars) + if err != nil { + return err + } + if err := e.template.SetCellStr(destination, axis, value); err != nil { + return err + } + } + + return nil +} + +func (e *executor) expr(x string) (gval.Evaluable, error) { + if f := e.expressions[x]; f != nil { + return f, nil + } + f, err := gval.Full().NewEvaluable(x) + if err != nil { + return nil, err + } + e.expressions[x] = f + return f, nil +} + +func (e *executor) vars() map[string]interface{} { + vars := map[string]interface{}{} + if len(e.frames) > 0 { + vars["row_number"] = e.frames[len(e.frames)-1].index + } + for i := len(e.frames) - 1; i >= 0; i-- { + fr := &e.frames[i] + for j, n := range fr.res.columns { + if _, found := vars[n]; !found { + vars[n] = fr.res.rows[fr.index][j] + } + } + } + return vars +} + +func (e *executor) expand(str string, vars map[string]interface{}) (string, error) { + + var err error + + replace := func(s string) string { + if err != nil { + return "" + } + var eval gval.Evaluable + if eval, err = e.expr(strings.TrimSpace(s)); err != nil { + return "" + } + s, err = eval.EvalString(e.ctx, vars) + return s + } + + str = handlebars(str, replace) + return str, err +}