Mercurial > dive4elements > river
annotate etl/src/main/resources/sql/flys-common.properties @ 8578:4eb1a3c71579
(issue1753) Implement PDF export of SQ Measurement data.
The SQRelationExporter now consists of two reports
(to avoid subreport woes). The old SQReleation PDF
only takes the Page count of the Measurement attachment
as argument to correctly print the page numbers. Otherwise
it is unchanged.
The new SQMeasurements report lists the measured data points
on which the calculation was based.
author | Andre Heinecke <andre.heinecke@intevation.de> |
---|---|
date | Mon, 16 Mar 2015 11:35:19 +0100 |
parents | e5675e85ac38 |
children |
rev | line source |
---|---|
4753
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
1 select.rivers = \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
2 SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
3 FROM rivers r \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
4 JOIN wsts w ON r.id = w.river_id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
5 JOIN wst_columns wc ON w.id = wc.wst_id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
6 JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
7 WHERE w.kind = 0 \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
8 GROUP BY r.id, r.name |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
9 select.gauges = \ |
6895
e5675e85ac38
Potential fix for flys/1453.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6894
diff
changeset
|
10 SELECT id, name, official_number, station \ |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
11 FROM gauges \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
12 WHERE river_id = :river_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
13 next.gauge.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
14 SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
15 insert.gauge = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
16 INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
17 VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum) |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
18 select.timeintervals = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
19 SELECT id, start_time, stop_time FROM time_intervals |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
20 next.timeinterval.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
21 SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
22 insert.timeinterval = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
23 INSERT INTO time_intervals (id, start_time, stop_time) \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
24 VALUES (:id, :start_time, :stop_time) |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
25 next.discharge.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
26 SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
27 insert.dischargetable = \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
28 INSERT INTO discharge_tables \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
29 (id, gauge_id, description, bfg_id, kind, time_interval_id) \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
30 VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
31 select.discharge.table.values = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
32 SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
33 next.discharge.table.values.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
34 SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
35 insert.discharge.table.value = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
36 INSERT INTO discharge_table_values (id, table_id, w, q) \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
37 VALUES (:id, :table_id, :w, :q) |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
38 delete.discharge.table.value = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
39 DELETE FROM discharge_table_values WHERE id = :id |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
40 select.gauge.discharge.tables = \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
41 SELECT \ |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
42 dt.id AS id, \ |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
43 dt.description AS description, \ |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
44 ti.start_time AS start_time, \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
45 ti.stop_time AS stop_time, \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
46 dt.bfg_id AS bfg_id \ |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
47 FROM discharge_tables dt \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
48 LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ |
5995
3bb6c2a3ef72
River ETL: Limit the discharge tables to load to the historical (kind = 1) ones. This prevents the wrong tables (values) to be deleted.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5930
diff
changeset
|
49 WHERE dt.gauge_id = :gauge_id AND dt.kind = 1 |
5258
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
50 select.gauge.master.discharge.table = \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
51 SELECT \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
52 dt.id AS id, \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
53 dt.bfg_id AS bfg_id \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
54 FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \ |
5479
11fba4dabe05
AFT/DIPS: Fixed wrond SQL statement.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5258
diff
changeset
|
55 WHERE g.id = :gauge_id AND dt.kind = 0 |
5258
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
56 update.bfg.id.discharge.table = \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
57 UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id |
5930
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
58 delete.discharge.table.values = \ |
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
59 DELETE from discharge_table_values where table_id = :id |
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
60 delete.discharge.table = \ |
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
61 DELETE from discharge_tables where id = :id and kind = 1 |