Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 4008:fea3d9210948

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
S(Q) relation: Totals are now fetched with separate SQL statement. Fixed join to GEWAESSER table.
author Sascha L. Teichmann <teichmann@intevation.de>
date Tue, 02 Oct 2012 00:00:30 +0200
parents d4d272e56d3a
children 60d88ec49c3b
comparison
equal deleted inserted replaced
4007:d4d272e56d3a 4008:fea3d9210948
2 2
3 import java.util.ArrayList; 3 import java.util.ArrayList;
4 import java.util.Calendar; 4 import java.util.Calendar;
5 import java.util.Collections; 5 import java.util.Collections;
6 import java.util.Date; 6 import java.util.Date;
7 import java.util.HashMap;
7 import java.util.List; 8 import java.util.List;
9 import java.util.Map;
8 import java.util.TreeMap; 10 import java.util.TreeMap;
9
10 import de.intevation.flys.artifacts.model.DateRange;
11
12 import de.intevation.flys.backend.SedDBSessionHolder;
13
14 import java.util.HashMap;
15 import java.util.Map;
16
17 import org.apache.log4j.Logger;
18 11
19 import org.hibernate.SQLQuery; 12 import org.hibernate.SQLQuery;
20 import org.hibernate.Session; 13 import org.hibernate.Session;
21 14
22 import org.hibernate.transform.BasicTransformerAdapter; 15 import org.hibernate.transform.BasicTransformerAdapter;
23 16
24 import org.hibernate.type.StandardBasicTypes; 17 import org.hibernate.type.StandardBasicTypes;
25 18
19 import de.intevation.flys.artifacts.model.DateRange;
20
21 import de.intevation.flys.backend.SedDBSessionHolder;
22
26 public class MeasurementFactory 23 public class MeasurementFactory
27 { 24 {
28 private static final Logger log = 25 public static final String SQL_TOTALS =
29 Logger.getLogger(MeasurementFactory.class); 26 "SELECT " +
27 "m.Q_BPEGEL AS Q_BPEGEL,"+
28 "m.TSCHWEB AS TSCHWEB," +
29 "m.TSAND AS TSAND" +
30 "FROM MESSUNG m " +
31 "JOIN STATION s ON m.STATIONID = s.STATIONID " +
32 "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " +
33 "WHERE " +
34 "r.NAME = :river_name " +
35 "AND m.Q_BPEGEL IS NOT NULL " +
36 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
37 "AND m.DATUM BETWEEN :from AND :to " +
38 "AND m.DATUM IS NOT NULL";
30 39
31 public static final String SQL_FACTIONS = 40 public static final String SQL_FACTIONS =
32 "SELECT m.datum AS DATUM," + 41 "SELECT " +
42 "m.datum AS DATUM," +
43 "m.Q_BPEGEL AS Q_BPEGEL,"+
33 "g.GLOTRECHTEID AS GLOTRECHTEID," + 44 "g.GLOTRECHTEID AS GLOTRECHTEID," +
34 "gp.LFDNR AS LFDNR," + 45 "gp.LFDNR AS LFDNR," +
35 "g.UFERABST AS UFERABST," + 46 "g.UFERABST AS UFERABST," +
36 "g.UFERABLINKS AS UFERABLINKS," + 47 "g.UFERABLINKS AS UFERABLINKS," +
37 "m.TSCHWEB AS TSCHWEB," + 48 "m.TSCHWEB AS TSCHWEB," +
60 "gs.RSIEB17 AS RSIEB17, gs.RSIEB18 AS RSIEB18," + 71 "gs.RSIEB17 AS RSIEB17, gs.RSIEB18 AS RSIEB18," +
61 "gs.RSIEB19 AS RSIEB19, gs.RSIEB20 AS RSIEB20," + 72 "gs.RSIEB19 AS RSIEB19, gs.RSIEB20 AS RSIEB20," +
62 "gs.RSIEB21 AS RSIEB21, gs.REST AS REST " + 73 "gs.RSIEB21 AS RSIEB21, gs.REST AS REST " +
63 "FROM MESSUNG m " + 74 "FROM MESSUNG m " +
64 "JOIN STATION s ON m.STATIONID = s.STATIONID " + 75 "JOIN STATION s ON m.STATIONID = s.STATIONID " +
76 "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " +
65 "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + 77 "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " +
66 "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + 78 "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " +
67 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + 79 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " +
68 "WHERE " + 80 "WHERE " +
69 "g.NAME = :river_name " + 81 "r.NAME = :river_name " +
70 "AND m.Q_BPEGEL IS NOT NULL " + 82 "AND m.Q_BPEGEL IS NOT NULL " +
71 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + 83 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
72 "AND m.DATUM BETWEEN :from AND :to " + 84 "AND m.DATUM BETWEEN :from AND :to " +
73 "AND m.TGESCHIEBE IS NOT NULL " + 85 "AND m.TGESCHIEBE IS NOT NULL " +
74 "AND m.DATUM IS NOT NULL " + 86 "AND m.DATUM IS NOT NULL " +
82 "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" + 94 "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" +
83 "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" + 95 "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" +
84 "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" + 96 "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" +
85 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" + 97 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" +
86 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " + 98 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " +
87 "ORDER BY m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR"; 99 "ORDER BY " +
100 "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
101
102 public static final BasicTransformerAdapter TOTALS_TRANSFORMER =
103 new BasicTransformerAdapter() {
104 private static final long serialVersionUID = 1L;
105
106 @Override
107 public Object transformTuple(Object [] tuple, String [] aliases) {
108 Map<String, Object> map = new HashMap<String, Object>();
109 for (int i = 0; i < tuple.length; ++i) {
110 Object value = tuple[i];
111 if (value != null) {
112 map.put(aliases[i], value);
113 }
114 }
115 return new Measurement(map, Collections.<Sieve>emptyList());
116 }
117 };
88 118
89 private static final int index(String s) { 119 private static final int index(String s) {
90 return Integer.parseInt(s.substring(s.length()-2))-1; 120 return Integer.parseInt(s.substring(s.length()-2))-1;
91 } 121 }
92 122
93 public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER = 123 public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER =
94 new BasicTransformerAdapter() { 124 new BasicTransformerAdapter() {
125 private static final long serialVersionUID = 1L;
95 126
96 @Override 127 @Override
97 public Object transformTuple(Object [] tuple, String [] aliases) { 128 public Object transformTuple(Object [] tuple, String [] aliases) {
98 Map<String, Object> map = new HashMap<String, Object>(); 129 Map<String, Object> map = new HashMap<String, Object>();
99 130
132 }; 163 };
133 164
134 private MeasurementFactory() { 165 private MeasurementFactory() {
135 } 166 }
136 167
137 protected static Measurements load( 168 public static Measurements getMeasurements(
169 String river,
170 double location,
171 DateRange dateRange
172 ) {
173 Session session = SedDBSessionHolder.HOLDER.get();
174 try {
175 List<Measurement> totals = loadTotals(
176 session, river, location, dateRange);
177
178 List<Measurement> accumulated = loadFractions(
179 session, river, location, dateRange);
180
181 return new Measurements(totals, accumulated);
182 }
183 finally {
184 session.close();
185 }
186 }
187
188 protected static List<Measurement> loadTotals(
138 Session session, 189 Session session,
139 String river, 190 String river,
140 double location, 191 double location,
141 DateRange dateRange 192 DateRange dateRange
142 ) { 193 ) {
143 boolean debug = log.isDebugEnabled(); 194 SQLQuery query = session.createSQLQuery(SQL_TOTALS)
144 195 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE)
145 if (debug) { 196 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
146 log.debug(SQL_FACTIONS); 197 .addScalar("TSAND", StandardBasicTypes.DOUBLE);
147 } 198
148 199 query.setString("river_name", river);
200 query.setDouble("location", location);
201 query.setDate("from", dateRange.getFrom());
202 query.setDate("to", dateRange.getTo());
203
204 query.setResultTransformer(TOTALS_TRANSFORMER);
205
206 @SuppressWarnings("unchecked")
207 List<Measurement> result = (List<Measurement>)query.list();
208 return result;
209 }
210
211 protected static List<Measurement> loadFractions(
212 Session session,
213 String river,
214 double location,
215 DateRange dateRange
216 ) {
149 SQLQuery query = session.createSQLQuery(SQL_FACTIONS) 217 SQLQuery query = session.createSQLQuery(SQL_FACTIONS)
150 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) 218 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE)
151 .addScalar("DATUM", StandardBasicTypes.DATE) 219 .addScalar("DATUM", StandardBasicTypes.DATE)
152 .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER) 220 .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER)
153 .addScalar("LFDNR", StandardBasicTypes.INTEGER) 221 .addScalar("LFDNR", StandardBasicTypes.INTEGER)
238 306
239 for (Measurement m: accumulated) { 307 for (Measurement m: accumulated) {
240 m.adjustSieves(); 308 m.adjustSieves();
241 } 309 }
242 310
243 return new Measurements(measuments, separateByDate(accumulated)); 311 return separateByDate(accumulated);
244 } 312 }
245 313
246 protected static List<Measurement> separateByDate(List<Measurement> measurements) { 314 protected static List<Measurement> separateByDate(List<Measurement> measurements) {
247 315
248 List<Measurement> result = new ArrayList<Measurement>(); 316 List<Measurement> result = new ArrayList<Measurement>();
382 Map<String, Object> data = 450 Map<String, Object> data =
383 new HashMap<String, Object>(measuments.get(0).getData()); 451 new HashMap<String, Object>(measuments.get(0).getData());
384 452
385 return new Measurement(data, accumulatedSieves); 453 return new Measurement(data, accumulatedSieves);
386 } 454 }
387
388 public static Measurements getMeasurements(
389 String river,
390 double location,
391 DateRange dateRange
392 ) {
393 Session session = SedDBSessionHolder.HOLDER.get();
394 try {
395 return load(session, river, location, dateRange);
396 }
397 finally {
398 session.close();
399 }
400 }
401 } 455 }
402 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 : 456 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :
http://dive4elements.wald.intevation.org