Mercurial > dive4elements > river

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
S(Q) relation. Extract more values from database. flys-artifacts/trunk@5622 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 27 Sep 2012 16:31:21 +0000
parents e1d3f6bccf2b
children f37323e86d59
comparison
equal deleted inserted replaced
3927:208a891c21b5 3928:d3e2080d3ada
1 package de.intevation.flys.artifacts.model.sq; 1 package de.intevation.flys.artifacts.model.sq;
2
3 import java.util.List;
2 4
3 import de.intevation.flys.artifacts.model.DateRange; 5 import de.intevation.flys.artifacts.model.DateRange;
4 6
5 import de.intevation.flys.backend.SedDBSessionHolder; 7 import de.intevation.flys.backend.SedDBSessionHolder;
6 8
19 public class MeasurementFactory 21 public class MeasurementFactory
20 { 22 {
21 private static final Logger log = 23 private static final Logger log =
22 Logger.getLogger(MeasurementFactory.class); 24 Logger.getLogger(MeasurementFactory.class);
23 25
24 public static final String [] GSIEBSATZ = {
25 "SIEB01", "SIEB02", "SIEB03", "SIEB04",
26 "SIEB05", "SIEB06", "SIEB07", "SIEB08",
27 "SIEB09", "SIEB10", "SIEB11", "SIEB13",
28 "SIEB13", "SIEB14", "SIEB15", "SIEB16",
29 "SIEB17", "SIEB18", "SIEB19", "SIEB20",
30 "SIEB21"
31 };
32
33 public static final String [] SSIEBUNG = {
34 "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04",
35 "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08",
36 "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13",
37 "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16",
38 "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20",
39 "RSIEB21", "REST"
40 };
41
42 public static final String SQL_MEASSURE = 26 public static final String SQL_MEASSURE =
43 ("SELECT " + 27 "SELECT m.datum AS DATUM," +
44 "m.TSAND AS TSAND," + 28 "g.UFERABST AS UFERABST," +
45 "m.TSCHWEB AS TSCHWEB," + 29 "g.UFERABLINKS AS UFERABLINKS," +
46 "m.CSCHWEB AS CSCHWEB," + 30 "m.tschweb AS TSCHWEB," +
47 "m.Q_BPEGEL AS Q_BPEGEL," + 31 "m.TSAND AS TSAND," +
48 "m.TGESCHIEBE AS TGESCHIEBE " + 32 "gp.MESSDAUER AS MESSDAUER," +
49 // ", %GSIEBSATZ% " + 33 "gp.MENGE AS MENGE," +
50 // "%SSIEBUNG% " + 34 "gp.GTRIEB AS GTRIEB," +
51 "FROM messung m " + 35 "gp.LFDNR AS LFDNR," +
52 "JOIN station s ON m.stationid = s.stationid " + 36 "m.TGESCHIEBE AS TGESCHIEBE," +
53 "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " + 37 "gs.RSIEB01 AS RSIEB01," +
54 // "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gsiebsatzid " + 38 "gs.RSIEB02 AS RSIEB02," +
55 // "LEFT JOIN SSIEBUNG ss ON gs.gsiebsatzid = ss.gsiebsatzid " + 39 "gs.RSIEB03 AS RSIEB03," +
40 "gs.RSIEB04 AS RSIEB04," +
41 "gs.RSIEB05 AS RSIEB05," +
42 "gs.RSIEB06 AS RSIEB06," +
43 "gs.RSIEB07 AS RSIEB07," +
44 "gs.RSIEB08 AS RSIEB08," +
45 "gs.RSIEB09 AS RSIEB09," +
46 "gs.RSIEB10 AS RSIEB10," +
47 "gs.RSIEB11 AS RSIEB11," +
48 "gs.RSIEB12 AS RSIEB12," +
49 "gs.RSIEB13 AS RSIEB13," +
50 "gs.RSIEB14 AS RSIEB14," +
51 "gs.RSIEB15 AS RSIEB15," +
52 "gs.RSIEB16 AS RSIEB16," +
53 "gs.RSIEB17 AS RSIEB17," +
54 "gs.RSIEB18 AS RSIEB18," +
55 "gs.RSIEB19 AS RSIEB19," +
56 "gs.RSIEB20 AS RSIEB20," +
57 "gs.RSIEB21 AS RSIEB21," +
58 "gs.REST AS REST," +
59 "g.GLOTRECHTEID AS GLOTRECHTEID " +
60 "FROM MESSUNG m " +
61 "JOIN STATION s ON m.STATIONID = s.STATIONID " +
62 "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " +
63 "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " +
64 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " +
56 "WHERE " + 65 "WHERE " +
57 "g.name = :river_name " + 66 "g.NAME = :river_name " +
58 "AND m.Q_BPEGEL IS NOT NULL " + 67 "AND m.Q_BPEGEL IS NOT NULL " +
59 "AND s.km BETWEEN :location - 0.001 AND :location + 0.001 " + 68 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
60 "AND m.datum BETWEEN :from AND :to "); 69 "AND m.DATUM BETWEEN :from AND :to " +
61 //.replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ)); 70 "AND m.TGESCHIEBE IS NOT NULL " +
62 //.replace("%SSIEBUNG%", projection("ss", SSIEBUNG)); 71 "ORDER BY m.DATUM";
63
64 private static final String projection(
65 String prefix,
66 String [] columnNames
67 ) {
68 StringBuilder sb = new StringBuilder();
69 for (int i = 0; i < columnNames.length; ++i) {
70 if (i > 0) {
71 sb.append(',');
72 }
73 sb.append(prefix)
74 .append('.')
75 .append(columnNames[i])
76 .append(" AS ")
77 .append(columnNames[i]);
78 }
79 return sb.toString();
80 }
81 72
82 public static final class MeasurementResultTransformer 73 public static final class MeasurementResultTransformer
83 extends BasicTransformerAdapter 74 extends BasicTransformerAdapter
84 { 75 {
85 public static MeasurementResultTransformer INSTANCE = 76 public static MeasurementResultTransformer INSTANCE =
114 if (debug) { 105 if (debug) {
115 log.debug(SQL_MEASSURE); 106 log.debug(SQL_MEASSURE);
116 } 107 }
117 108
118 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) 109 SQLQuery query = session.createSQLQuery(SQL_MEASSURE)
119 .addScalar("TSAND", StandardBasicTypes.DOUBLE) 110 .addScalar("TSAND", StandardBasicTypes.DOUBLE)
120 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) 111 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
121 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) 112 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE)
122 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE); 113 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE)
123 114 .addScalar("DATUM", StandardBasicTypes.DATE)
124 /* 115 .addScalar("UFERABST", StandardBasicTypes.DOUBLE)
125 for (String siebsatz: GSIEBSATZ) { 116 .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE)
126 query.addScalar(siebsatz, StandardBasicTypes.DOUBLE); 117 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
127 } 118 .addScalar("TSAND", StandardBasicTypes.DOUBLE)
128 119 .addScalar("MESSDAUER", StandardBasicTypes.DOUBLE)
129 for (String siebung: SSIEBUNG) { 120 .addScalar("MENGE", StandardBasicTypes.DOUBLE)
130 query.addScalar(siebung, StandardBasicTypes.DOUBLE); 121 .addScalar("GTRIEB", StandardBasicTypes.DOUBLE)
131 } 122 .addScalar("LFDNR", StandardBasicTypes.DOUBLE)
132 */ 123 .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE)
124 .addScalar("RSIEB01", StandardBasicTypes.DOUBLE)
125 .addScalar("RSIEB02", StandardBasicTypes.DOUBLE)
126 .addScalar("RSIEB03", StandardBasicTypes.DOUBLE)
127 .addScalar("RSIEB04", StandardBasicTypes.DOUBLE)
128 .addScalar("RSIEB05", StandardBasicTypes.DOUBLE)
129 .addScalar("RSIEB06", StandardBasicTypes.DOUBLE)
130 .addScalar("RSIEB07", StandardBasicTypes.DOUBLE)
131 .addScalar("RSIEB08", StandardBasicTypes.DOUBLE)
132 .addScalar("RSIEB09", StandardBasicTypes.DOUBLE)
133 .addScalar("RSIEB10", StandardBasicTypes.DOUBLE)
134 .addScalar("RSIEB11", StandardBasicTypes.DOUBLE)
135 .addScalar("RSIEB12", StandardBasicTypes.DOUBLE)
136 .addScalar("RSIEB13", StandardBasicTypes.DOUBLE)
137 .addScalar("RSIEB14", StandardBasicTypes.DOUBLE)
138 .addScalar("RSIEB15", StandardBasicTypes.DOUBLE)
139 .addScalar("RSIEB16", StandardBasicTypes.DOUBLE)
140 .addScalar("RSIEB17", StandardBasicTypes.DOUBLE)
141 .addScalar("RSIEB18", StandardBasicTypes.DOUBLE)
142 .addScalar("RSIEB19", StandardBasicTypes.DOUBLE)
143 .addScalar("RSIEB20", StandardBasicTypes.DOUBLE)
144 .addScalar("RSIEB21", StandardBasicTypes.DOUBLE)
145 .addScalar("REST", StandardBasicTypes.DOUBLE)
146 .addScalar("GLOTRECHTEID", StandardBasicTypes.DOUBLE);
133 147
134 query.setString("river_name", river); 148 query.setString("river_name", river);
135 query.setDouble("location", location); 149 query.setDouble("location", location);
136 query.setDate("from", dateRange.getFrom()); 150 query.setDate("from", dateRange.getFrom());
137 query.setDate("to", dateRange.getTo()); 151 query.setDate("to", dateRange.getTo());
138 152
139 query.setResultTransformer(MeasurementResultTransformer.INSTANCE); 153 query.setResultTransformer(MeasurementResultTransformer.INSTANCE);
140 154
141 return new Measurements(query.list()); 155 @SuppressWarnings("unchecked")
156 List<Measurement> measuments = (List<Measurement>)query.list();
157
158 for (int i = 0, N = measuments.size(); i < N; ++i) {
159 Measurement m = measuments.get(i);
160 Measurement p = i > 0 ? measuments.get(i-1) : null;
161 Measurement n = i < N-1 ? measuments.get(i+1) : null;
162 m.setPrev(p);
163 m.setNext(n);
164 }
165
166 return new Measurements(measuments);
142 } 167 }
143 168
144 public static Measurements getMeasurements( 169 public static Measurements getMeasurements(
145 String river, 170 String river,
146 double location, 171 double location,
http://dive4elements.wald.intevation.org