Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3981:6bcc50e2cc7d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
http://dive4elements.wald.intevation.org