Mercurial > gemma
view pkg/xlsx/templater.go @ 5591:0011f50cf216 surveysperbottleneckid
Removed no longer used alternative api for surveys/ endpoint.
As bottlenecks in the summary for SR imports are now identified by
their id and no longer by the (not guarantied to be unique!) name,
there is no longer the need to request survey data by the name+date
tuple (which isn't reliable anyway). So the workaround was now
reversed.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Wed, 06 Apr 2022 13:30:29 +0200 |
parents | 5f47eeea988d |
children | 163f38c69a07 |
line wrap: on
line source
// 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" "strconv" "strings" "github.com/xuri/excelize/v2" "gopkg.in/yaml.v2" "github.com/PaesslerAG/gval" "gemma.intevation.de/gemma/pkg/log" ) 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 sheetAxis struct { sheet string axis string } type cellValue struct { value string err error } type executor struct { ctx context.Context tx *sql.Tx template *excelize.File keep map[string]bool expressions map[string]gval.Evaluable sourceSheet string destinationSheet string frames []frame // Fetching formulas out of cells is very expensive so we cache them. formulaCache map[sheetAxis]cellValue } type area struct { x1 int y1 int x2 int y2 int mc excelize.MergeCell } func mergeCellToArea(mc excelize.MergeCell) (area, error) { x1, y1, err := excelize.CellNameToCoordinates(mc.GetStartAxis()) if err != nil { return area{}, err } x2, y2, err := excelize.CellNameToCoordinates(mc.GetEndAxis()) if err != nil { return area{}, err } return area{ x1: x1, y1: y1, x2: x2, y2: y2, mc: mc, }, nil } func (a *area) contains(x, y int) bool { return a.x1 <= x && x <= a.x2 && a.y1 <= y && y <= a.y2 } 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, tx *sql.Tx, template *excelize.File, ) error { //if f, err := os.Create("cpu.prof"); err == nil { // pprof.StartCPUProfile(f) // defer pprof.StopCPUProfile() //} e := executor{ ctx: ctx, tx: tx, template: template, keep: map[string]bool{}, expressions: map[string]gval.Evaluable{}, formulaCache: map[sheetAxis]cellValue{}, } 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 } var reused int func (e *executor) getCellFormula(sheet, axis string) (string, error) { var ( k = sheetAxis{sheet: sheet, axis: axis} v cellValue ok bool ) if v, ok = e.formulaCache[k]; !ok { v.value, v.err = e.template.GetCellFormula(sheet, axis) e.formulaCache[k] = v } return v.value, v.err } func (e *executor) setCellFormula(sheet, axis, formula string) { e.formulaCache[sheetAxis{sheet: sheet, axis: axis}] = cellValue{value: formula} e.template.SetCellFormula(sheet, axis, formula) } 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 order(a, b int) (int, int) { if a < b { return a, b } return b, a } func (e *executor) copy(action *Action) error { if n := len(action.Location); !(n == 1 || n == 2) { return fmt.Errorf("length location = %d (expect 1 or 2)", len(action.Location)) } 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 { x, y, err = excelize.CellNameToCoordinates(s) } return x, y } var location []string if len(action.Location) == 1 { location = []string{action.Location[0], action.Location[0]} } else { location = action.Location } var destination string if action.Destination == "" { destination = location[0] } else { destination = action.Destination } var ( s1 = expand(location[0]) s2 = expand(location[1]) d1 = expand(destination) sx1, sy1 = split(s1) sx2, sy2 = split(s2) dx1, dy1 = split(d1) ) if err != nil { return err } sx1, sx2 = order(sx1, sx2) sy1, sy2 = order(sy1, sy2) var areas []area //log.Debugln("merged cells") if mcs, err := e.template.GetMergeCells(e.sourceSheet); err == nil { areas = make([]area, 0, len(mcs)) for _, mc := range mcs { if a, err := mergeCellToArea(mc); err == nil { areas = append(areas, a) } } } for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 { nextX: for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 { // check if cell is part of a merged cell for k := range areas { area := &areas[k] if area.contains(x, y) { ofsX := x - area.x1 ofsY := y - area.y1 sx := dx1 + j - ofsX sy := dy1 + i - ofsY ex := sx + (area.x2 - area.x1) ey := sy + (area.y2 - area.y1) // Copy over attributes for l := 0; l <= area.x2-area.x1; l++ { for m := 0; m <= area.y2-area.y1; m++ { src, err1 := excelize.CoordinatesToCellName(area.x1+l, area.y1+m) dst, err2 := excelize.CoordinatesToCellName(sx+l, sy+m) 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.getCellFormula(e.sourceSheet, src); err == nil { e.setCellFormula(e.destinationSheet, dst, s) } } } dst, err := excelize.CoordinatesToCellName(sx, sy) if err != nil { continue nextX } // Copy over expanded text if v, err := e.typedExpand(area.mc.GetCellValue(), vars); err == nil { e.template.SetCellValue(e.destinationSheet, dst, v) } // Finally merge the cells if end, err := excelize.CoordinatesToCellName(ex, ey); err == nil { e.template.MergeCell(e.destinationSheet, dst, end) } continue nextX } } // Regular cell src, err := excelize.CoordinatesToCellName(x, y) if err != nil { continue } dst, err := excelize.CoordinatesToCellName(dx1+j, dy1+i) if err != nil { continue } cn, err := excelize.ColumnNumberToName(x) if err != nil { continue } cw, err := e.template.GetColWidth(e.sourceSheet, cn) if err != nil { continue } rh, err := e.template.GetRowHeight(e.sourceSheet, y) if err != nil { continue } dc, err := excelize.ColumnNumberToName(dx1 + j) if err != nil { continue } if e.template.SetColWidth(e.destinationSheet, dc, dc, cw) != nil { continue } if e.template.SetRowHeight(e.destinationSheet, dy1+i, rh) != 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.getCellFormula(e.sourceSheet, src); err == nil { e.setCellFormula(e.destinationSheet, dst, s) } if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil { if v, err := e.typedExpand(s, vars); err == nil { e.template.SetCellValue(e.destinationSheet, dst, v) } } } } 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.tx, 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 } nvalue, err := e.typedExpand(value, vars) if err != nil { return err } if err := e.template.SetCellValue(destination, axis, nvalue); err != nil { return err } } return nil } func columnToNum(col interface{}) interface{} { var name string switch v := col.(type) { case string: name = v default: name = fmt.Sprintf("%v", col) } num, err := excelize.ColumnNameToNumber(name) if err != nil { log.Errorf("invalid column name '%v'\n", col) return 1 } return num } func asInt(i interface{}) (int, error) { switch v := i.(type) { case int: return v, nil case int8: return int(v), nil case int16: return int(v), nil case int32: return int(v), nil case int64: return int(v), nil case float32: return int(v), nil case float64: return int(v), nil case string: return strconv.Atoi(v) default: return 0, fmt.Errorf("invalid int '%v'", i) } } func coord2cell(ix, iy interface{}) interface{} { x, err := asInt(ix) if err != nil { log.Errorf("invalid x value: %v\n", err) return "A1" } y, err := asInt(iy) if err != nil { log.Errorf("invalid y value: %v\n", err) return "A1" } cell, err := excelize.CoordinatesToCellName(x, y) if err != nil { log.Errorf("invalid cell coord (%d, %d)\n", x, y) return "A1" } return cell } var templateLang = gval.Full( gval.Function("column2num", columnToNum), gval.Function("coord2cell", coord2cell), ) func (e *executor) expr(x string) (gval.Evaluable, error) { if f := e.expressions[x]; f != nil { return f, nil } f, err := templateLang.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) if err != nil { log.Errorf("'%s' '%s' %v\n", str, s, err) } return s } str = handlebars(str, replace) return str, err } func (e *executor) typedExpand( str string, vars map[string]interface{}, ) (interface{}, error) { var ( err error repCount int last interface{} ) replace := func(s string) string { if err != nil { return "" } var eval gval.Evaluable if eval, err = e.expr(strings.TrimSpace(s)); err != nil { return "" } repCount++ last, err = eval(e.ctx, vars) if err != nil { log.Errorf("'%s' '%s' %v\n", str, s, err) } return fmt.Sprintf("%v", last) } nstr := handlebars(str, replace) if err != nil { return nil, err } if repCount == 1 && strings.HasPrefix(str, "{{") && strings.HasSuffix(str, "}}") { return last, nil } return nstr, nil }