Mercurial > gemma

annotate pkg/imports/agm.go @ 1781:0a53c33bc7b2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed broken SQL statement (once again).
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 11 Jan 2019 16:49:55 +0100
parents 48791416bea5
children 206b1dd31112
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 // This is Free Software under GNU Affero General Public License v >= 3.0
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 // without warranty, see README.md and license for details.
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 //
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 // SPDX-License-Identifier: AGPL-3.0-or-later
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 // License-Filename: LICENSES/AGPL-3.0.txt
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 //
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 // Copyright (C) 2018 by via donau
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 // – Österreichische Wasserstraßen-Gesellschaft mbH
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 // Software engineering by Intevation GmbH
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 //
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 // Author(s):
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14 package imports
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 import (
1743
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
17 "bufio"
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 "context"
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 "database/sql"
1743
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
20 "encoding/csv"
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
21 "fmt"
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
22 "io"
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 "os"
1743
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
24 "path/filepath"
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
25 "strconv"
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
26 "strings"
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
27 "time"
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 "gemma.intevation.de/gemma/pkg/common"
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
30 "gemma.intevation.de/gemma/pkg/models"
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 )
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 type ApprovedGaugeMeasurements struct {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 Dir string `json:"dir"`
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 // GMAPJobKind is the unique name of an approved gauge measurements import job.
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 const AGMJobKind JobKind = "agm"
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 type agmJobCreator struct{}
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 func init() {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 RegisterJobCreator(AGMJobKind, agmJobCreator{})
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45
1754
807569b08513 Import queue: Auto acceptance is now a property of the import kind itself and is not configurable any more.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1743
diff changeset
46 func (agmJobCreator) AutoAccept() bool { return false }
807569b08513 Import queue: Auto acceptance is now a property of the import kind itself and is not configurable any more.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1743
diff changeset
47
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 func (agmJobCreator) Description() string {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 return "approved gauge measurements"
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 func (agmJobCreator) Create(_ JobKind, data string) (Job, error) {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53 agm := new(ApprovedGaugeMeasurements)
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 if err := common.FromJSONString(data, agm); err != nil {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 return nil, err
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 return agm, nil
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60 func (agmJobCreator) Depends() []string {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 return []string{
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 "gauges",
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 "gauge_measurements",
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 const (
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
68 // delete the old and keep the new measures.
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
69 agmStageDoneDeleteSQL = `
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
70 WITH staged AS (
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
71 SELECT key
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
72 FROM waterway.track_imports
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
73 WHERE import_id = $1 AND
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
74 relation = 'waterway.gauge_measurements'::regclass
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
75 ),
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
76 to_delete AS (
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
77 SELECT o.id AS id
1781
0a53c33bc7b2 Fixed broken SQL statement (once again).
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1780
diff changeset
78 FROM waterway.gauge_measurements o
0a53c33bc7b2 Fixed broken SQL statement (once again).
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1780
diff changeset
79 JOIN waterway.gauge_measurements n
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
80 ON n.fk_gauge_id = o.fk_gauge_id AND n.measure_date = o.measure_date
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
81 WHERE n.id IN (SELECT key FROM staged)
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
82 AND o.id NOT IN (SELECT key FROM staged)
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
83 )
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
84 DELETE FROM waterway.gauge_measurements WHERE id IN (SELECT id from to_delete)`
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
85
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 agmStageDoneSQL = `
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
87 UPDATE waterway.gauge_measurements SET staging_done = true
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
88 WHERE id IN (
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
89 SELECT key FROM waterway.track_imports
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90 WHERE import_id = $1 AND
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
91 relation = 'waterway.gauge_measurements'::regclass)`
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92 )
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94 func (agmJobCreator) StageDone(
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95 ctx context.Context,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 tx *sql.Tx,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97 id int64,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
98 ) error {
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
99 _, err := tx.ExecContext(ctx, agmStageDoneDeleteSQL, id)
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
100 if err == nil {
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
101 _, err = tx.ExecContext(ctx, agmStageDoneSQL, id)
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
102 }
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 return err
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 // CleanUp removes the folder containing the CSV file with the
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107 // the approved gauge measurements.
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 func (agm *ApprovedGaugeMeasurements) CleanUp() error {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 return os.RemoveAll(agm.Dir)
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110 }
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
112 func guessDate(s string) (time.Time, error) {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
113 var err error
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
114 var t time.Time
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
115 for _, layout := range [...]string{
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
116 "02.01.2006 15:04",
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
117 "2006-01-02T15:04:05-07:00",
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
118 } {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
119 if t, err = time.Parse(layout, s); err == nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
120 break
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
121 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
122 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
123 return t, err
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
124 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
125
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
126 // Do executes the actual approved gauge measurements import.
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
127 func (agm *ApprovedGaugeMeasurements) Do(
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
128 ctx context.Context,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
129 importID int64,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
130 conn *sql.Conn,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
131 feedback Feedback,
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
132 ) (interface{}, error) {
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
133
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
134 start := time.Now()
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
135
1743
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
136 f, err := os.Open(filepath.Join(agm.Dir, "agm.csv"))
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
137 if err != nil {
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
138 return nil, err
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
139 }
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
140 defer f.Close()
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
141
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
142 r := csv.NewReader(bufio.NewReader(f))
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
143 r.Comma = ';'
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
144 r.ReuseRecord = true
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
145
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
146 headers, err := r.Read()
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
147 if err != nil {
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
148 return nil, err
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
149 }
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
150
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
151 headerIndices := map[string]int{}
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
152
1743
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
153 for i, f := range headers {
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
154 headerIndices[strings.Replace(
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
155 strings.ToLower(
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
156 strings.TrimSpace(f)), " ", "_", -1)] = i
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
157 }
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
158
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
159 var missing []string
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
160
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
161 for _, m := range [...]string{
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
162 "fk_gauge_id",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
163 "measure_date",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
164 "from", // "sender",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
165 "language_code",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
166 "country_code",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
167 "date_issue",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
168 "reference_code",
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
169 "value", // "water_level",
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
170 "predicted",
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
171 // "is_waterlevel",
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
172 "value_min",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
173 "value_max",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
174 "date_info",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
175 "originator", // "source_organization",
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
176 } {
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
177 if _, found := headerIndices[m]; !found {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
178 missing = append(missing, m)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
179 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
180 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
181
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
182 if len(missing) > 0 {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
183 return nil, fmt.Errorf("Missing columns: %s", strings.Join(missing, ", "))
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
184 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
185
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
186 inCm, _ := rescale("cm")
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
187 scaler := func(row []string) (func(float32) float32, error) {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
188 idx, found := headerIndices["unit"]
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
189 if !found {
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
190 return inCm, nil
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1772
diff changeset
191 }
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
192 unit := row[idx]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
193 if unit == "cm" {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
194 return inCm, nil
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
195 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
196 s, err := rescale(unit)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
197 return s, err
1743
85d0f017fbee Approved gauges measurements: Open CSV and read headers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1741
diff changeset
198 }
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
199
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
200 tx, err := conn.BeginTx(ctx, nil)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
201 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
202 return nil, err
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
203 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
204 defer tx.Rollback()
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
205
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
206 insertStmt, err := tx.PrepareContext(ctx, insertGMSQL)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
207 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
208 return nil, err
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
209 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
210 defer insertStmt.Close()
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
211 trackStmt, err := tx.PrepareContext(ctx, trackImportSQL)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
212 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
213 return nil, err
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
214 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
215 defer trackStmt.Close()
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
216
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
217 ids := []int64{}
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
218
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
219 args := make([]interface{}, 19)
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
220
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
221 args[18] = false // staging_done
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
222
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
223 lines:
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
224 for line := 1; ; line++ {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
225
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
226 row, err := r.Read()
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
227 switch {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
228 case err == io.EOF || len(row) == 0:
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
229 break lines
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
230 case err != nil:
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
231 return nil, fmt.Errorf("CSV parsing failed: %v", err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
232 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
233 convert, err := scaler(row)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
234 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
235 return nil, fmt.Errorf("line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
236 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
237
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
238 gids := row[headerIndices["fk_gauge_id"]]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
239 gid, err := models.IsrsFromString(gids)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
240 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
241 return nil, fmt.Errorf("Invalid ISRS code line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
242 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
243
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
244 args[0] = gid.CountryCode
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
245 args[1] = gid.LoCode
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
246 args[2] = gid.FairwaySection
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
247 args[3] = gid.Orc
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
248 args[4] = gid.Hectometre
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
249
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
250 md, err := guessDate(row[headerIndices["measure_date"]])
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
251 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
252 return nil, fmt.Errorf("Invalid 'measure_date' line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
253 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
254 args[5] = md
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
255
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
256 args[6] = row[headerIndices["from"]]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
257 args[7] = row[headerIndices["language_code"]]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
258 args[8] = row[headerIndices["country_code"]]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
259
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
260 dis, err := guessDate(row[headerIndices["date_issue"]])
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
261 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
262 return nil, fmt.Errorf("Invalid 'date_issue' line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
263 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
264 args[9] = dis
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
265
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
266 args[10] = row[headerIndices["reference_code"]]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
267
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
268 value, err := strconv.ParseFloat(row[headerIndices["value"]], 32)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
269 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
270 return nil, fmt.Errorf("Invalid 'value' line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
271 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
272 args[11] = convert(float32(value))
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
273
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
274 predicted := strings.ToLower(row[headerIndices["predicted"]]) == "true"
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
275 args[12] = predicted
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
276
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
277 args[13] = true // is_waterlevel
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
278
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
279 valueMin, err := strconv.ParseFloat(row[headerIndices["value_min"]], 32)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
280 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
281 return nil, fmt.Errorf("Invalid 'value_min' line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
282 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
283 args[14] = convert(float32(valueMin))
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
284
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
285 valueMax, err := strconv.ParseFloat(row[headerIndices["value_max"]], 32)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
286 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
287 return nil, fmt.Errorf("Invalid 'value_max' line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
288 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
289 args[15] = convert(float32(valueMax))
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
290
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
291 din, err := guessDate(row[headerIndices["date_info"]])
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
292 if err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
293 return nil, fmt.Errorf("Invalid 'date_info' line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
294 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
295 args[16] = din
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
296
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
297 args[17] = row[headerIndices["originator"]]
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
298
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
299 // args[18] (staging_done) is set to true outside the loop.
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1779
diff changeset
300
1778
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
301 var id int64
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
302 if err := insertStmt.QueryRowContext(ctx, args...).Scan(&id); err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
303 return nil, fmt.Errorf("Failed to insert line %d: %v", line, err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
304 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
305 ids = append(ids, id)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
306
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
307 if _, err := trackStmt.ExecContext(
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
308 ctx, importID, "waterway.gauge_measurements", id,
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
309 ); err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
310 return nil, err
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
311 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
312 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
313
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
314 if err := tx.Commit(); err != nil {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
315 return nil, fmt.Errorf("Commit failed: %v", err)
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
316 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
317
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
318 feedback.Info("Importing approved gauge measurements took %s",
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
319 time.Since(start))
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
320
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
321 summary := struct {
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
322 IDs []int64 `json:"ids"`
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
323 }{
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
324 IDs: ids,
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
325 }
164b46ebd60d Approved gauge measurement import: Implemented. TODO: Fix staging.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1775
diff changeset
326 return &summary, nil
1741
44398a8bdf94 Approved gauge measurements: Added a stub to upload a CSV file for parsing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
327 }