Mercurial > dive4elements > river
comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3981:6bcc50e2cc7d
More code for S(Q) relation.
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Fri, 28 Sep 2012 16:41:08 +0200 |
parents | f37323e86d59 |
children | a4930c43b806 |
comparison
equal
deleted
inserted
replaced
3980:6cc5186b9b48 | 3981:6bcc50e2cc7d |
---|---|
1 package de.intevation.flys.artifacts.model.sq; | 1 package de.intevation.flys.artifacts.model.sq; |
2 | 2 |
3 import java.util.Date; | |
3 import java.util.List; | 4 import java.util.List; |
4 | 5 |
5 import de.intevation.flys.artifacts.model.DateRange; | 6 import de.intevation.flys.artifacts.model.DateRange; |
6 | 7 |
7 import de.intevation.flys.backend.SedDBSessionHolder; | 8 import de.intevation.flys.backend.SedDBSessionHolder; |
23 private static final Logger log = | 24 private static final Logger log = |
24 Logger.getLogger(MeasurementFactory.class); | 25 Logger.getLogger(MeasurementFactory.class); |
25 | 26 |
26 public static final String SQL_MEASSURE = | 27 public static final String SQL_MEASSURE = |
27 "SELECT m.datum AS DATUM," + | 28 "SELECT m.datum AS DATUM," + |
29 "g.GLOTRECHTEID AS GLOTRECHTEID," + | |
30 "gp.LFDNR AS LFDNR," + | |
28 "g.UFERABST AS UFERABST," + | 31 "g.UFERABST AS UFERABST," + |
29 "g.UFERABLINKS AS UFERABLINKS," + | 32 "g.UFERABLINKS AS UFERABLINKS," + |
30 "m.TSCHWEB AS TSCHWEB," + | 33 "m.TSCHWEB AS TSCHWEB," + |
31 "m.TSAND AS TSAND," + | 34 "m.TSAND AS TSAND," + |
32 "gp.MESSDAUER AS MESSDAUER," + | 35 "gp.MESSDAUER AS MESSDAUER," + |
33 "gp.MENGE AS MENGE," + | 36 "gp.MENGE AS MENGE," + |
34 "gp.GTRIEB AS GTRIEB," + | 37 "gp.GTRIEB AS GTRIEB," + |
35 "gp.LFDNR AS LFDNR," + | |
36 "m.TGESCHIEBE AS TGESCHIEBE," + | 38 "m.TGESCHIEBE AS TGESCHIEBE," + |
37 "gs.RSIEB01 AS RSIEB01," + | 39 "gs.RSIEB01 AS RSIEB01," + |
38 "gs.RSIEB02 AS RSIEB02," + | 40 "gs.RSIEB02 AS RSIEB02," + |
39 "gs.RSIEB03 AS RSIEB03," + | 41 "gs.RSIEB03 AS RSIEB03," + |
40 "gs.RSIEB04 AS RSIEB04," + | 42 "gs.RSIEB04 AS RSIEB04," + |
53 "gs.RSIEB17 AS RSIEB17," + | 55 "gs.RSIEB17 AS RSIEB17," + |
54 "gs.RSIEB18 AS RSIEB18," + | 56 "gs.RSIEB18 AS RSIEB18," + |
55 "gs.RSIEB19 AS RSIEB19," + | 57 "gs.RSIEB19 AS RSIEB19," + |
56 "gs.RSIEB20 AS RSIEB20," + | 58 "gs.RSIEB20 AS RSIEB20," + |
57 "gs.RSIEB21 AS RSIEB21," + | 59 "gs.RSIEB21 AS RSIEB21," + |
58 "gs.REST AS REST," + | 60 "gs.REST AS REST, " + |
59 "g.GLOTRECHTEID AS GLOTRECHTEID " + | 61 "COALESCE(sie.SIEB01, 0) AS SIEB01, " + |
62 "COALESCE(sie.SIEB02, 0) AS SIEB02, " + | |
63 "COALESCE(sie.SIEB03, 0) AS SIEB03, " + | |
64 "COALESCE(sie.SIEB04, 0) AS SIEB04, " + | |
65 "COALESCE(sie.SIEB05, 0) AS SIEB05, " + | |
66 "COALESCE(sie.SIEB06, 0) AS SIEB06, " + | |
67 "COALESCE(sie.SIEB07, 0) AS SIEB07, " + | |
68 "COALESCE(sie.SIEB08, 0) AS SIEB08, " + | |
69 "COALESCE(sie.SIEB09, 0) AS SIEB09, " + | |
70 "COALESCE(sie.SIEB10, 0) AS SIEB10, " + | |
71 "COALESCE(sie.SIEB11, 0) AS SIEB11, " + | |
72 "COALESCE(sie.SIEB12, 0) AS SIEB12, " + | |
73 "COALESCE(sie.SIEB13, 0) AS SIEB13, " + | |
74 "COALESCE(sie.SIEB14, 0) AS SIEB14, " + | |
75 "COALESCE(sie.SIEB15, 0) AS SIEB15, " + | |
76 "COALESCE(sie.SIEB16, 0) AS SIEB16, " + | |
77 "COALESCE(sie.SIEB17, 0) AS SIEB17, " + | |
78 "COALESCE(sie.SIEB18, 0) AS SIEB18, " + | |
79 "COALESCE(sie.SIEB19, 0) AS SIEB19, " + | |
80 "COALESCE(sie.SIEB20, 0) AS SIEB20, " + | |
81 "COALESCE(sie.SIEB21, 0) AS SIEB21 " + | |
60 "FROM MESSUNG m " + | 82 "FROM MESSUNG m " + |
61 "JOIN STATION s ON m.STATIONID = s.STATIONID " + | 83 "JOIN STATION s ON m.STATIONID = s.STATIONID " + |
62 "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + | 84 "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + |
63 "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + | 85 "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + |
64 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + | 86 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + |
66 "g.NAME = :river_name " + | 88 "g.NAME = :river_name " + |
67 "AND m.Q_BPEGEL IS NOT NULL " + | 89 "AND m.Q_BPEGEL IS NOT NULL " + |
68 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + | 90 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + |
69 "AND m.DATUM BETWEEN :from AND :to " + | 91 "AND m.DATUM BETWEEN :from AND :to " + |
70 "AND m.TGESCHIEBE IS NOT NULL " + | 92 "AND m.TGESCHIEBE IS NOT NULL " + |
71 "ORDER BY m.DATUM"; | 93 "AND (" + |
94 "COALESCE(gs.RSIEB01, 0) + COALESCE(gs.RSIEB02, 0) +" + | |
95 "COALESCE(gs.RSIEB03, 0) + COALESCE(gs.RSIEB04, 0) +" + | |
96 "COALESCE(gs.RSIEB05, 0) + COALESCE(gs.RSIEB06, 0) +" + | |
97 "COALESCE(gs.RSIEB07, 0) + COALESCE(gs.RSIEB08, 0) +" + | |
98 "COALESCE(gs.RSIEB09, 0) + COALESCE(gs.RSIEB10, 0) +" + | |
99 "COALESCE(gs.RSIEB11, 0) + COALESCE(gs.RSIEB12, 0) +" + | |
100 "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" + | |
101 "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" + | |
102 "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" + | |
103 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" + | |
104 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " + | |
105 "ORDER BY m.DATUM, gp.LFDNR, g.UFERABST"; | |
72 | 106 |
73 public static final class MeasurementResultTransformer | 107 public static final class MeasurementResultTransformer |
74 extends BasicTransformerAdapter | 108 extends BasicTransformerAdapter |
75 { | 109 { |
76 public static MeasurementResultTransformer INSTANCE = | 110 public static MeasurementResultTransformer INSTANCE = |
107 } | 141 } |
108 | 142 |
109 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) | 143 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) |
110 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) | 144 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) |
111 .addScalar("DATUM", StandardBasicTypes.DATE) | 145 .addScalar("DATUM", StandardBasicTypes.DATE) |
146 .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER) | |
147 .addScalar("LFDNR", StandardBasicTypes.INTEGER) | |
112 .addScalar("UFERABST", StandardBasicTypes.DOUBLE) | 148 .addScalar("UFERABST", StandardBasicTypes.DOUBLE) |
113 .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE) | 149 .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE) |
114 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) | 150 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) |
115 .addScalar("TSAND", StandardBasicTypes.DOUBLE) | 151 .addScalar("TSAND", StandardBasicTypes.DOUBLE) |
116 .addScalar("MESSDAUER", StandardBasicTypes.DOUBLE) | 152 .addScalar("MESSDAUER", StandardBasicTypes.DOUBLE) |
117 .addScalar("MENGE", StandardBasicTypes.DOUBLE) | 153 .addScalar("MENGE", StandardBasicTypes.DOUBLE) |
118 .addScalar("GTRIEB", StandardBasicTypes.DOUBLE) | 154 .addScalar("GTRIEB", StandardBasicTypes.DOUBLE) |
119 .addScalar("LFDNR", StandardBasicTypes.DOUBLE) | |
120 .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE) | 155 .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE) |
121 .addScalar("RSIEB01", StandardBasicTypes.DOUBLE) | 156 .addScalar("RSIEB01", StandardBasicTypes.DOUBLE) |
122 .addScalar("RSIEB02", StandardBasicTypes.DOUBLE) | 157 .addScalar("RSIEB02", StandardBasicTypes.DOUBLE) |
123 .addScalar("RSIEB03", StandardBasicTypes.DOUBLE) | 158 .addScalar("RSIEB03", StandardBasicTypes.DOUBLE) |
124 .addScalar("RSIEB04", StandardBasicTypes.DOUBLE) | 159 .addScalar("RSIEB04", StandardBasicTypes.DOUBLE) |
138 .addScalar("RSIEB18", StandardBasicTypes.DOUBLE) | 173 .addScalar("RSIEB18", StandardBasicTypes.DOUBLE) |
139 .addScalar("RSIEB19", StandardBasicTypes.DOUBLE) | 174 .addScalar("RSIEB19", StandardBasicTypes.DOUBLE) |
140 .addScalar("RSIEB20", StandardBasicTypes.DOUBLE) | 175 .addScalar("RSIEB20", StandardBasicTypes.DOUBLE) |
141 .addScalar("RSIEB21", StandardBasicTypes.DOUBLE) | 176 .addScalar("RSIEB21", StandardBasicTypes.DOUBLE) |
142 .addScalar("REST", StandardBasicTypes.DOUBLE) | 177 .addScalar("REST", StandardBasicTypes.DOUBLE) |
143 .addScalar("GLOTRECHTEID", StandardBasicTypes.DOUBLE); | 178 .addScalar("SIEB01", StandardBasicTypes.DOUBLE) |
179 .addScalar("SIEB02", StandardBasicTypes.DOUBLE) | |
180 .addScalar("SIEB03", StandardBasicTypes.DOUBLE) | |
181 .addScalar("SIEB04", StandardBasicTypes.DOUBLE) | |
182 .addScalar("SIEB05", StandardBasicTypes.DOUBLE) | |
183 .addScalar("SIEB06", StandardBasicTypes.DOUBLE) | |
184 .addScalar("SIEB07", StandardBasicTypes.DOUBLE) | |
185 .addScalar("SIEB08", StandardBasicTypes.DOUBLE) | |
186 .addScalar("SIEB09", StandardBasicTypes.DOUBLE) | |
187 .addScalar("SIEB10", StandardBasicTypes.DOUBLE) | |
188 .addScalar("SIEB11", StandardBasicTypes.DOUBLE) | |
189 .addScalar("SIEB12", StandardBasicTypes.DOUBLE) | |
190 .addScalar("SIEB13", StandardBasicTypes.DOUBLE) | |
191 .addScalar("SIEB14", StandardBasicTypes.DOUBLE) | |
192 .addScalar("SIEB15", StandardBasicTypes.DOUBLE) | |
193 .addScalar("SIEB16", StandardBasicTypes.DOUBLE) | |
194 .addScalar("SIEB17", StandardBasicTypes.DOUBLE) | |
195 .addScalar("SIEB18", StandardBasicTypes.DOUBLE) | |
196 .addScalar("SIEB19", StandardBasicTypes.DOUBLE) | |
197 .addScalar("SIEB20", StandardBasicTypes.DOUBLE) | |
198 .addScalar("SIEB21", StandardBasicTypes.DOUBLE); | |
144 | 199 |
145 query.setString("river_name", river); | 200 query.setString("river_name", river); |
146 query.setDouble("location", location); | 201 query.setDouble("location", location); |
147 query.setDate("from", dateRange.getFrom()); | 202 query.setDate("from", dateRange.getFrom()); |
148 query.setDate("to", dateRange.getTo()); | 203 query.setDate("to", dateRange.getTo()); |
150 query.setResultTransformer(MeasurementResultTransformer.INSTANCE); | 205 query.setResultTransformer(MeasurementResultTransformer.INSTANCE); |
151 | 206 |
152 @SuppressWarnings("unchecked") | 207 @SuppressWarnings("unchecked") |
153 List<Measurement> measuments = (List<Measurement>)query.list(); | 208 List<Measurement> measuments = (List<Measurement>)query.list(); |
154 | 209 |
210 Integer lastLR = null; | |
211 | |
155 for (int i = 0, N = measuments.size(); i < N; ++i) { | 212 for (int i = 0, N = measuments.size(); i < N; ++i) { |
156 Measurement m = measuments.get(i); | 213 Measurement m = measuments.get(i); |
214 | |
215 Integer currentLR = (Integer)m.getData("GLOTRECHTEID"); | |
216 | |
217 boolean newDS = lastLR == null | |
218 || (currentLR != null && !lastLR.equals(currentLR)); | |
219 | |
157 Measurement p = i > 0 ? measuments.get(i-1) : null; | 220 Measurement p = i > 0 ? measuments.get(i-1) : null; |
158 Measurement n = i < N-1 ? measuments.get(i+1) : null; | 221 Measurement n = i < N-1 ? measuments.get(i+1) : null; |
159 m.setPrev(p); | 222 m.setPrev(newDS ? null : p); |
160 m.setNext(n); | 223 m.setNext(n); |
224 | |
225 if (p != null && newDS) { | |
226 p.setNext(null); | |
227 } | |
228 | |
229 lastLR = currentLR; | |
161 } | 230 } |
162 | 231 |
163 return new Measurements(measuments); | 232 return new Measurements(measuments); |
164 } | 233 } |
165 | 234 |