Mercurial > gemma
changeset 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 | 866eae1bd888 |
children | 80d9fd782f00 |
files | go.mod go.sum pkg/config/config.go pkg/controllers/report.go pkg/controllers/routes.go pkg/xlsx/handlebars.go pkg/xlsx/sql.go pkg/xlsx/templater.go |
diffstat | 8 files changed, 713 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/go.mod Wed May 26 19:10:45 2021 +0200 +++ b/go.mod Thu May 27 01:22:10 2021 +0200 @@ -3,6 +3,8 @@ go 1.13 require ( + github.com/360EntSecGroup-Skylar/excelize/v2 v2.4.0 + github.com/PaesslerAG/gval v1.1.0 github.com/cockroachdb/apd v1.1.0 // indirect github.com/fatih/structs v1.1.0 github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199 @@ -35,4 +37,5 @@ gopkg.in/gomail.v2 v2.0.0-20160411212932-81ebce5c23df gopkg.in/ini.v1 v1.62.0 // indirect gopkg.in/robfig/cron.v1 v1.2.0 + gopkg.in/yaml.v2 v2.4.0 )
--- a/go.sum Wed May 26 19:10:45 2021 +0200 +++ b/go.sum Thu May 27 01:22:10 2021 +0200 @@ -12,9 +12,15 @@ cloud.google.com/go/storage v1.0.0/go.mod h1:IhtSnM/ZTZV8YYJWCY8RULGVqBDmpoyjwiyrjsg+URw= dmitri.shuralyov.com/gpu/mtl v0.0.0-20190408044501-666a987793e9/go.mod h1:H6x//7gZCb22OMCxBHrMx7a5I7Hp++hsVxbQ4BYO7hU= gioui.org v0.0.0-20210308172011-57750fc8a0a6/go.mod h1:RSH6KIUZ0p2xy5zHDxgAM4zumjgTw83q2ge/PI+yyw8= +github.com/360EntSecGroup-Skylar/excelize/v2 v2.4.0 h1:X+2CWGf5W1tm2+W7Y/LLrAPLFSNlHATnqDudGoIzaxY= +github.com/360EntSecGroup-Skylar/excelize/v2 v2.4.0/go.mod h1:p9lGPoVX3HYEbFRfjgrPWaaKsHe/2u4EM9DB/qoctgU= github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU= github.com/BurntSushi/xgb v0.0.0-20160522181843-27f122750802/go.mod h1:IVnqGOEym/WlBOVXweHU+Q+/VP0lqqI8lqeDx9IjBqo= github.com/OneOfOne/xxhash v1.2.2/go.mod h1:HSdplMjZKSmBqAxg5vPj2TmRDmfkzw+cTzAElWljhcU= +github.com/PaesslerAG/gval v1.1.0 h1:k3RuxeZDO3eejD4cMPSt+74tUSvTnbGvLx0df4mdwFc= +github.com/PaesslerAG/gval v1.1.0/go.mod h1:y/nm5yEyTeX6av0OfKJNp9rBNj2XrGhAf5+v24IBN1I= +github.com/PaesslerAG/jsonpath v0.1.0 h1:gADYeifvlqK3R3i2cR5B4DGgxLXIPb3TRTH1mGi0jPI= +github.com/PaesslerAG/jsonpath v0.1.0/go.mod h1:4BzmtoM/PI8fPO4aQGIusjGxGir2BzcV0grWtFzq1Y8= github.com/ajstarks/svgo v0.0.0-20180226025133-644b8db467af/go.mod h1:K08gAheRH3/J6wwsYMMT4xOr94bZjxIelGM0+d/wbFw= github.com/alecthomas/template v0.0.0-20160405071501-a0175ee3bccc/go.mod h1:LOuyumcjzFXgccqObfd/Ljyb9UuFJ6TxHnclSeseNhc= github.com/alecthomas/units v0.0.0-20151022065526-2efee857e7cf/go.mod h1:ybxpYRFXyAe+OPACYpWeL0wqObRcbAqCMya13uyzqw0= @@ -162,6 +168,8 @@ github.com/mitchellh/mapstructure v1.4.1/go.mod h1:bFUtVrKA4DC2yAKiSyO/QUcy7e+RRV2QTWOzhPopBRo= github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd/go.mod h1:6dJC0mAP4ikYIbvyc7fijjWJddQyLn8Ig3JB5CqoB9Q= github.com/modern-go/reflect2 v1.0.1/go.mod h1:bx2lNnkwVCuqBIxFjflWJWanXIb3RllmbCylyMrvgv0= +github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826 h1:RWengNIwukTxcDr9M+97sNutRR1RKhG96O6jWumTTnw= +github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826/go.mod h1:TaXosZuwdSHYgviHp1DAtfrULt5eUgsSMsZf+YrPgl8= github.com/mwitkow/go-conntrack v0.0.0-20161129095857-cc309e4a2223/go.mod h1:qRWi+5nqEBWmkhHvq77mSJWrCKwh8bxhgT7d/eI7P4U= github.com/oklog/ulid v1.3.1/go.mod h1:CirwcVhetQ6Lv90oh/F+FBtV6XMibvdAFo93nm5qn4U= github.com/pascaldekloe/goe v0.0.0-20180627143212-57f6aae5913c/go.mod h1:lzWF7FIEvWOWxwDKqyGYQf6ZUaNfKdP144TG7ZOy1lc= @@ -187,6 +195,10 @@ github.com/prometheus/procfs v0.0.0-20181005140218-185b4288413d/go.mod h1:c3At6R/oaqEKCNdg8wHV1ftS6bRYblBhIjjI8uT2IGk= github.com/prometheus/procfs v0.0.0-20190507164030-5867b95ac084/go.mod h1:TjEm7ze935MbeOT/UhFTIMYKhuLP4wbCsTZCD3I8kEA= github.com/prometheus/tsdb v0.7.1/go.mod h1:qhTCs0VvXwvX/y3TZrWD7rabWM+ijKTux40TwIPHuXU= +github.com/richardlehane/mscfb v1.0.3 h1:rD8TBkYWkObWO0oLDFCbwMeZ4KoalxQy+QgniCj3nKI= +github.com/richardlehane/mscfb v1.0.3/go.mod h1:YzVpcZg9czvAuhk9T+a3avCpcFPMUWm7gK3DypaEsUk= +github.com/richardlehane/msoleps v1.0.1 h1:RfrALnSNXzmXLbGct/P2b4xkFz4e8Gmj/0Vj9M9xC1o= +github.com/richardlehane/msoleps v1.0.1/go.mod h1:BWev5JBpU9Ko2WAgmZEuiz4/u3ZYTKbjLycmwiWUfWg= github.com/rogpeppe/fastuuid v0.0.0-20150106093220-6724a57986af/go.mod h1:XWv6SoW27p1b0cqNHllgS5HIMJraePCO15w5zCzIWYg= github.com/rogpeppe/go-internal v1.3.0/go.mod h1:M8bDsm7K2OlrFYOpmOWEs/qY81heoFRclV5y23lUDJ4= github.com/rs/cors v1.7.0 h1:+88SsELBHx5r+hZ8TCkggzSstaWNbDvThkVK8H6f9ik= @@ -228,8 +240,9 @@ github.com/stretchr/objx v0.1.1/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= github.com/stretchr/testify v1.2.2/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs= github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= -github.com/stretchr/testify v1.4.0 h1:2E4SXV/wtOkTonXsotYi4li6zVWxYlZuYNCXe9XRJyk= github.com/stretchr/testify v1.4.0/go.mod h1:j7eGeouHqKxXV5pUuKE4zz7dFj8WfuZ+81PSLYec5m4= +github.com/stretchr/testify v1.6.1 h1:hDPOHmpOpP40lSULcqw7IrRb/u7w6RpDC9399XyoNd0= +github.com/stretchr/testify v1.6.1/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= github.com/subosito/gotenv v1.2.0 h1:Slr1R9HxAlEKefgq5jn9U+DnETlIUa6HfgEzj0g5d7s= github.com/subosito/gotenv v1.2.0/go.mod h1:N0PQaV/YGNqwC0u51sEeR/aUtSLEXKX9iv69rRypqCw= github.com/tidwall/cities v0.1.0 h1:CVNkmMf7NEC9Bvokf5GoSsArHCKRMTgLuubRTHnH0mE= @@ -242,6 +255,8 @@ github.com/tidwall/rtree v1.2.8/go.mod h1:S+JSsqPTI8LfWA4xHBo5eXzie8WJLVFeppAutSegl6M= github.com/tmc/grpc-websocket-proxy v0.0.0-20190109142713-0ad062ec5ee5/go.mod h1:ncp9v5uamzpCO7NfCPTXjqaC+bZgJeR0sMTm6dMHP7U= github.com/xiang90/probing v0.0.0-20190116061207-43a291ad63a2/go.mod h1:UETIi67q53MR2AWcXfiuqkDkRtnGDLqkBTpCHuJHxtU= +github.com/xuri/efp v0.0.0-20210322160811-ab561f5b45e3 h1:EpI0bqf/eX9SdZDwlMmahKM+CDBgNbsXMhsN28XrM8o= +github.com/xuri/efp v0.0.0-20210322160811-ab561f5b45e3/go.mod h1:ybY/Jr0T0GTCnYjKqmdwxyxn2BQf2RcQIIvex5QldPI= go.etcd.io/bbolt v1.3.2/go.mod h1:IbVyRI1SCnLcuJnV2u8VeU0CEYM7e686BmAb1XKL+uU= go.etcd.io/bbolt v1.3.5 h1:XAzx9gjCb0Rxj7EoqcClPD1d5ZBxZJk0jbuoPHenBt0= go.etcd.io/bbolt v1.3.5/go.mod h1:G5EMThwa9y8QZGBClrRx5EY+Yw9kAhnjy3bSjsnlVTQ= @@ -256,6 +271,7 @@ golang.org/x/crypto v0.0.0-20190510104115-cbcb75029529/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI= golang.org/x/crypto v0.0.0-20190605123033-f99c8df09eb5/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI= golang.org/x/crypto v0.0.0-20190820162420-60c769a6c586/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI= +golang.org/x/crypto v0.0.0-20210415154028-4f45737414dc/go.mod h1:T9bdIzuCu7OtxOm1hfPfRQxPLYneinmdGuTeoZ9dtd4= golang.org/x/crypto v0.0.0-20210513164829-c07d793c2f9a h1:kr2P4QFmQr29mSLA43kwrOcgcReGTfbE9N577tCTuBc= golang.org/x/crypto v0.0.0-20210513164829-c07d793c2f9a/go.mod h1:P+XmwS30IXTQdn5tA2iutPOUgjI07+tq3H3K9MVA1s8= golang.org/x/exp v0.0.0-20180321215751-8460e604b9de/go.mod h1:CJ0aWSM057203Lf6IL+f9T1iT9GByDxfZKAQTCR3kQA= @@ -277,6 +293,8 @@ golang.org/x/image v0.0.0-20200618115811-c13761719519/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0= golang.org/x/image v0.0.0-20201208152932-35266b937fa6/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0= golang.org/x/image v0.0.0-20210216034530-4410531fe030/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0= +golang.org/x/image v0.0.0-20210220032944-ac19c3e999fb h1:fqpd0EBDzlHRCjiphRR5Zo/RSWWQlWv34418dnEixWk= +golang.org/x/image v0.0.0-20210220032944-ac19c3e999fb/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0= golang.org/x/lint v0.0.0-20181026193005-c67002cb31c3/go.mod h1:UVdnD1Gm6xHRNCYTkRU2/jEulfH38KcIWyp/GAMgvoE= golang.org/x/lint v0.0.0-20190227174305-5b3e6a55c961/go.mod h1:wehouNa3lNwaWXcvxsM5YxQ5yQlVC4a0KAMCusXpPoU= golang.org/x/lint v0.0.0-20190301231843-5614ed5bae6f/go.mod h1:UVdnD1Gm6xHRNCYTkRU2/jEulfH38KcIWyp/GAMgvoE= @@ -303,6 +321,7 @@ golang.org/x/net v0.0.0-20190603091049-60506f45cf65/go.mod h1:HSz+uSET+XFnRR8LxR5pz3Of3rY3CfYBVs4xY44aLks= golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s= golang.org/x/net v0.0.0-20210226172049-e18ecbb05110/go.mod h1:m0MpNAwzfU5UDzcl9v0D8zg8gWTRqZa9RBIspLL5mdg= +golang.org/x/net v0.0.0-20210415231046-e915ea6b2b7d/go.mod h1:9tjilg8BloeKEkVJvy7fQ90B1CfIiPueXVOjqfkSzI8= golang.org/x/net v0.0.0-20210525063256-abc453219eb5 h1:wjuX4b5yYQnEQHzd+CBcrcC6OVR2J1CN6mUy0oSxIPo= golang.org/x/net v0.0.0-20210525063256-abc453219eb5/go.mod h1:9nx3DQGgdP8bBQD5qxJ1jj9UTztislL4KSBs9R2vV5Y= golang.org/x/oauth2 v0.0.0-20180821212333-d2e6202438be/go.mod h1:N/0e6XlmueqKjAGxoOufVs8QHGRruUQn6yWY3a++T0U= @@ -330,6 +349,7 @@ golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs= golang.org/x/sys v0.0.0-20201119102817-f84b799fce68/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs= golang.org/x/sys v0.0.0-20210304124612-50617c2ba197/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs= +golang.org/x/sys v0.0.0-20210330210617-4fbd30eecc44/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs= golang.org/x/sys v0.0.0-20210423082822-04245dca01da/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs= golang.org/x/sys v0.0.0-20210525143221-35b2ab0089ea h1:+WiDlPBBaO+h9vPNZi8uJ3k4BkKQB7Iow3aqwHVA5hI= golang.org/x/sys v0.0.0-20210525143221-35b2ab0089ea/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg= @@ -414,6 +434,8 @@ gopkg.in/yaml.v2 v2.2.4/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI= gopkg.in/yaml.v2 v2.4.0 h1:D8xgwECY7CYvx+Y2n4sBz93Jn9JRvxdiyyo8CTfuKaY= gopkg.in/yaml.v2 v2.4.0/go.mod h1:RDklbk79AGWmwhnvt/jBztapEOGDOx6ZbXqjP6csGnQ= +gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c h1:dUUwHk2QECo/6vqA44rthZ8ie2QXMNeKRTHCNY2nXvo= +gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= honnef.co/go/tools v0.0.0-20190102054323-c2f93a96b099/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4= honnef.co/go/tools v0.0.0-20190106161140-3f1c8253044a/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4= honnef.co/go/tools v0.0.0-20190418001031-e561f6794a2a/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
--- a/pkg/config/config.go Wed May 26 19:10:45 2021 +0200 +++ b/pkg/config/config.go Thu May 27 01:22:10 2021 +0200 @@ -122,6 +122,9 @@ // SOAPTimeout is the timeout till a SOAP request is canceled. func SOAPTimeout() time.Duration { return viper.GetDuration("soap-timeout") } +// ReportPath is a path where report templates are stored. +func ReportPath() string { return viper.GetString("report-path") } + var ( proxyKeyOnce sync.Once proxyKey []byte @@ -290,6 +293,8 @@ str("published-config", "", "path to a config file served to client.") + str("report-path", "", "path to a report templates.") + d("soap-timeout", 3*time.Minute, "Timeout till a SOAP request is canceled.") }
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/controllers/report.go Thu May 27 01:22:10 2021 +0200 @@ -0,0 +1,101 @@ +// 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 controllers + +import ( + "fmt" + "log" + "net/http" + "os" + "path/filepath" + + "gemma.intevation.de/gemma/pkg/config" + "gemma.intevation.de/gemma/pkg/middleware" + "gemma.intevation.de/gemma/pkg/xlsx" + + "github.com/360EntSecGroup-Skylar/excelize/v2" + "github.com/gorilla/mux" +) + +func report(rw http.ResponseWriter, req *http.Request) { + + vars := mux.Vars(req) + + name := vars["name"] + + config.WaitReady() + path := config.ReportPath() + if path == "" { + http.NotFound(rw, req) + return + } + + stat, err := os.Stat(path) + if err != nil { + http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError) + return + } + + if !stat.Mode().IsDir() { + http.NotFound(rw, req) + return + } + + xlsxFilename := filepath.Join(path, name+".xlsx") + yamlFilename := filepath.Join(path, name+".yaml") + + _, errX := os.Stat(xlsxFilename) + _, errY := os.Stat(yamlFilename) + if errX != nil || errX != nil { + if (errX != nil && os.IsNotExist(errX)) || (errY != nil && os.IsNotExist(errY)) { + http.NotFound(rw, req) + } else { + http.Error(rw, "Something is wrong", http.StatusInternalServerError) + } + return + } + + template, err := excelize.OpenFile(xlsxFilename) + if err != nil { + http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError) + log.Printf("error: %v\n", err) + return + } + + action, err := xlsx.ActionFromFile(yamlFilename) + if err != nil { + http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError) + log.Printf("error: %v\n", err) + return + } + + ctx := req.Context() + conn := middleware.GetDBConn(req) + + if err := action.Execute(ctx, conn, template); err != nil { + http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError) + log.Printf("error: %v\n", err) + return + } + rw.Header().Set( + "Content-Disposition", + fmt.Sprintf("attachment; filename=%s.xlsx", name)) + rw.Header().Set( + "Content-Type", + "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") + + if _, err := template.WriteTo(rw); err != nil { + log.Printf("error: %v\n", err) + } +}
--- a/pkg/controllers/routes.go Wed May 26 19:10:45 2021 +0200 +++ b/pkg/controllers/routes.go Thu May 27 01:22:10 2021 +0200 @@ -322,6 +322,11 @@ NoConn: true, })).Methods(http.MethodPut) + // Handler for reporting + + api.Handle("/data/report/{name:[a-zA-Z0-9_]+}", waterwayAdmin( + mw.DBConn(http.HandlerFunc(report)))).Methods(http.MethodGet) + // Handler to serve data to the client. api.Handle("/data/{type:availability|fairway}/{kind:stretch|section|bottleneck}/{name:.+}", any(
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/xlsx/handlebars.go Thu May 27 01:22:10 2021 +0200 @@ -0,0 +1,83 @@ +// 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 "strings" + +func handlebars(s string, replace func(string) string) string { + + var ( + out, repl strings.Builder + mode int + ) + + for _, c := range s { + switch mode { + case 0: + if c == '{' { + mode = 1 + } else { + out.WriteRune(c) + } + case 1: + if c == '{' { + mode = 2 + } else { + out.WriteByte('{') + out.WriteRune(c) + mode = 0 + } + case 2: + switch c { + case '\\': + mode = 3 + case '}': + mode = 4 + default: + repl.WriteRune(c) + } + case 3: + repl.WriteRune(c) + mode = 2 + case 4: + if c == '}' { + out.WriteString(replace(repl.String())) + repl.Reset() + mode = 0 + } else { + repl.WriteByte('}') + repl.WriteRune(c) + mode = 2 + } + } + } + + switch mode { + case 1: + out.WriteByte('{') + case 2: + out.WriteString("{{") + out.WriteString(repl.String()) + case 3: + out.WriteString("{{") + out.WriteString(repl.String()) + out.WriteByte('\\') + case 4: + out.WriteString("{{") + out.WriteString(repl.String()) + out.WriteByte('}') + } + + return out.String() +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/xlsx/sql.go Thu May 27 01:22:10 2021 +0200 @@ -0,0 +1,109 @@ +// 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 ( + "context" + "database/sql" + "strconv" + "strings" +) + +type sqlResult struct { + columns []string + rows [][]interface{} +} + +func (sr *sqlResult) find(column string) int { + for i, name := range sr.columns { + if name == column { + return i + } + } + return -1 +} + +func replaceStmt(stmt string) (string, []string) { + + var names []string + + add := func(name string) int { + for i, n := range names { + if n == name { + return i + } + } + names = append(names, name) + return len(names) + } + + replace := func(s string) string { + n := add(strings.TrimSpace(s)) + return "$" + strconv.Itoa(n) + } + + out := handlebars(stmt, replace) + return out, names +} + +func query( + ctx context.Context, + db *sql.Conn, + stmt string, + eval func(string) (interface{}, error), +) (*sqlResult, error) { + + nstmt, nargs := replaceStmt(stmt) + args := make([]interface{}, len(nargs)) + for i, n := range nargs { + var err error + if args[i], err = eval(n); err != nil { + return nil, err + } + } + + rs, err := db.QueryContext(ctx, nstmt, args...) + if err != nil { + return nil, err + } + defer rs.Close() + + columns, err := rs.Columns() + if err != nil { + return nil, err + } + var rows [][]interface{} + + ptrs := make([]interface{}, len(columns)) + + for rs.Next() { + row := make([]interface{}, len(columns)) + for i := range row { + ptrs[i] = &row[i] + } + if err := rs.Scan(ptrs...); err != nil { + return nil, err + } + rows = append(rows, row) + } + + if err := rs.Err(); err != nil { + return nil, err + } + + return &sqlResult{ + columns: columns, + rows: rows, + }, nil +}
--- /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 +}