Mercurial > dive4elements > river
diff flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3928:d3e2080d3ada
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 |
line wrap: on
line diff
--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java Thu Sep 27 13:25:51 2012 +0000 +++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java Thu Sep 27 16:31:21 2012 +0000 @@ -1,5 +1,7 @@ package de.intevation.flys.artifacts.model.sq; +import java.util.List; + import de.intevation.flys.artifacts.model.DateRange; import de.intevation.flys.backend.SedDBSessionHolder; @@ -21,63 +23,52 @@ private static final Logger log = Logger.getLogger(MeasurementFactory.class); - public static final String [] GSIEBSATZ = { - "SIEB01", "SIEB02", "SIEB03", "SIEB04", - "SIEB05", "SIEB06", "SIEB07", "SIEB08", - "SIEB09", "SIEB10", "SIEB11", "SIEB13", - "SIEB13", "SIEB14", "SIEB15", "SIEB16", - "SIEB17", "SIEB18", "SIEB19", "SIEB20", - "SIEB21" - }; - - public static final String [] SSIEBUNG = { - "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04", - "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08", - "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13", - "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16", - "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20", - "RSIEB21", "REST" - }; - public static final String SQL_MEASSURE = - ("SELECT " + - "m.TSAND AS TSAND," + - "m.TSCHWEB AS TSCHWEB," + - "m.CSCHWEB AS CSCHWEB," + - "m.Q_BPEGEL AS Q_BPEGEL," + - "m.TGESCHIEBE AS TGESCHIEBE " + - // ", %GSIEBSATZ% " + - // "%SSIEBUNG% " + - "FROM messung m " + - "JOIN station s ON m.stationid = s.stationid " + - "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " + - // "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gsiebsatzid " + - // "LEFT JOIN SSIEBUNG ss ON gs.gsiebsatzid = ss.gsiebsatzid " + + "SELECT m.datum AS DATUM," + + "g.UFERABST AS UFERABST," + + "g.UFERABLINKS AS UFERABLINKS," + + "m.tschweb AS TSCHWEB," + + "m.TSAND AS TSAND," + + "gp.MESSDAUER AS MESSDAUER," + + "gp.MENGE AS MENGE," + + "gp.GTRIEB AS GTRIEB," + + "gp.LFDNR AS LFDNR," + + "m.TGESCHIEBE AS TGESCHIEBE," + + "gs.RSIEB01 AS RSIEB01," + + "gs.RSIEB02 AS RSIEB02," + + "gs.RSIEB03 AS RSIEB03," + + "gs.RSIEB04 AS RSIEB04," + + "gs.RSIEB05 AS RSIEB05," + + "gs.RSIEB06 AS RSIEB06," + + "gs.RSIEB07 AS RSIEB07," + + "gs.RSIEB08 AS RSIEB08," + + "gs.RSIEB09 AS RSIEB09," + + "gs.RSIEB10 AS RSIEB10," + + "gs.RSIEB11 AS RSIEB11," + + "gs.RSIEB12 AS RSIEB12," + + "gs.RSIEB13 AS RSIEB13," + + "gs.RSIEB14 AS RSIEB14," + + "gs.RSIEB15 AS RSIEB15," + + "gs.RSIEB16 AS RSIEB16," + + "gs.RSIEB17 AS RSIEB17," + + "gs.RSIEB18 AS RSIEB18," + + "gs.RSIEB19 AS RSIEB19," + + "gs.RSIEB20 AS RSIEB20," + + "gs.RSIEB21 AS RSIEB21," + + "gs.REST AS REST," + + "g.GLOTRECHTEID AS GLOTRECHTEID " + + "FROM MESSUNG m " + + "JOIN STATION s ON m.STATIONID = s.STATIONID " + + "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + + "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + + "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + "WHERE " + - "g.name = :river_name " + + "g.NAME = :river_name " + "AND m.Q_BPEGEL IS NOT NULL " + - "AND s.km BETWEEN :location - 0.001 AND :location + 0.001 " + - "AND m.datum BETWEEN :from AND :to "); - //.replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ)); - //.replace("%SSIEBUNG%", projection("ss", SSIEBUNG)); - - private static final String projection( - String prefix, - String [] columnNames - ) { - StringBuilder sb = new StringBuilder(); - for (int i = 0; i < columnNames.length; ++i) { - if (i > 0) { - sb.append(','); - } - sb.append(prefix) - .append('.') - .append(columnNames[i]) - .append(" AS ") - .append(columnNames[i]); - } - return sb.toString(); - } + "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + + "AND m.DATUM BETWEEN :from AND :to " + + "AND m.TGESCHIEBE IS NOT NULL " + + "ORDER BY m.DATUM"; public static final class MeasurementResultTransformer extends BasicTransformerAdapter @@ -116,20 +107,43 @@ } SQLQuery query = session.createSQLQuery(SQL_MEASSURE) - .addScalar("TSAND", StandardBasicTypes.DOUBLE) - .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) - .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) - .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE); - - /* - for (String siebsatz: GSIEBSATZ) { - query.addScalar(siebsatz, StandardBasicTypes.DOUBLE); - } - - for (String siebung: SSIEBUNG) { - query.addScalar(siebung, StandardBasicTypes.DOUBLE); - } - */ + .addScalar("TSAND", StandardBasicTypes.DOUBLE) + .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) + .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) + .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) + .addScalar("DATUM", StandardBasicTypes.DATE) + .addScalar("UFERABST", StandardBasicTypes.DOUBLE) + .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE) + .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) + .addScalar("TSAND", StandardBasicTypes.DOUBLE) + .addScalar("MESSDAUER", StandardBasicTypes.DOUBLE) + .addScalar("MENGE", StandardBasicTypes.DOUBLE) + .addScalar("GTRIEB", StandardBasicTypes.DOUBLE) + .addScalar("LFDNR", StandardBasicTypes.DOUBLE) + .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB01", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB02", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB03", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB04", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB05", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB06", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB07", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB08", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB09", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB10", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB11", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB12", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB13", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB14", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB15", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB16", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB17", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB18", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB19", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB20", StandardBasicTypes.DOUBLE) + .addScalar("RSIEB21", StandardBasicTypes.DOUBLE) + .addScalar("REST", StandardBasicTypes.DOUBLE) + .addScalar("GLOTRECHTEID", StandardBasicTypes.DOUBLE); query.setString("river_name", river); query.setDouble("location", location); @@ -138,7 +152,18 @@ query.setResultTransformer(MeasurementResultTransformer.INSTANCE); - return new Measurements(query.list()); + @SuppressWarnings("unchecked") + List<Measurement> measuments = (List<Measurement>)query.list(); + + for (int i = 0, N = measuments.size(); i < N; ++i) { + Measurement m = measuments.get(i); + Measurement p = i > 0 ? measuments.get(i-1) : null; + Measurement n = i < N-1 ? measuments.get(i+1) : null; + m.setPrev(p); + m.setNext(n); + } + + return new Measurements(measuments); } public static Measurements getMeasurements(