Mercurial > dive4elements > river
view 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 |
line wrap: on
line source
package de.intevation.flys.artifacts.model.sq; import java.util.Date; import java.util.List; import de.intevation.flys.artifacts.model.DateRange; import de.intevation.flys.backend.SedDBSessionHolder; import java.util.HashMap; import java.util.Map; import org.apache.log4j.Logger; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.transform.BasicTransformerAdapter; import org.hibernate.type.StandardBasicTypes; public class MeasurementFactory { private static final Logger log = Logger.getLogger(MeasurementFactory.class); public static final String SQL_MEASSURE = "SELECT m.datum AS DATUM," + "g.GLOTRECHTEID AS GLOTRECHTEID," + "gp.LFDNR AS LFDNR," + "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," + "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, " + "COALESCE(sie.SIEB01, 0) AS SIEB01, " + "COALESCE(sie.SIEB02, 0) AS SIEB02, " + "COALESCE(sie.SIEB03, 0) AS SIEB03, " + "COALESCE(sie.SIEB04, 0) AS SIEB04, " + "COALESCE(sie.SIEB05, 0) AS SIEB05, " + "COALESCE(sie.SIEB06, 0) AS SIEB06, " + "COALESCE(sie.SIEB07, 0) AS SIEB07, " + "COALESCE(sie.SIEB08, 0) AS SIEB08, " + "COALESCE(sie.SIEB09, 0) AS SIEB09, " + "COALESCE(sie.SIEB10, 0) AS SIEB10, " + "COALESCE(sie.SIEB11, 0) AS SIEB11, " + "COALESCE(sie.SIEB12, 0) AS SIEB12, " + "COALESCE(sie.SIEB13, 0) AS SIEB13, " + "COALESCE(sie.SIEB14, 0) AS SIEB14, " + "COALESCE(sie.SIEB15, 0) AS SIEB15, " + "COALESCE(sie.SIEB16, 0) AS SIEB16, " + "COALESCE(sie.SIEB17, 0) AS SIEB17, " + "COALESCE(sie.SIEB18, 0) AS SIEB18, " + "COALESCE(sie.SIEB19, 0) AS SIEB19, " + "COALESCE(sie.SIEB20, 0) AS SIEB20, " + "COALESCE(sie.SIEB21, 0) AS SIEB21 " + "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 " + "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 " + "AND m.TGESCHIEBE IS NOT NULL " + "AND (" + "COALESCE(gs.RSIEB01, 0) + COALESCE(gs.RSIEB02, 0) +" + "COALESCE(gs.RSIEB03, 0) + COALESCE(gs.RSIEB04, 0) +" + "COALESCE(gs.RSIEB05, 0) + COALESCE(gs.RSIEB06, 0) +" + "COALESCE(gs.RSIEB07, 0) + COALESCE(gs.RSIEB08, 0) +" + "COALESCE(gs.RSIEB09, 0) + COALESCE(gs.RSIEB10, 0) +" + "COALESCE(gs.RSIEB11, 0) + COALESCE(gs.RSIEB12, 0) +" + "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" + "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" + "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" + "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" + "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " + "ORDER BY m.DATUM, gp.LFDNR, g.UFERABST"; public static final class MeasurementResultTransformer extends BasicTransformerAdapter { public static MeasurementResultTransformer INSTANCE = new MeasurementResultTransformer(); public MeasurementResultTransformer() { } @Override public Object transformTuple(Object [] tuple, String [] aliases) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 0; i < tuple.length; ++i) { if (tuple[i] != null) { map.put(aliases[i], tuple[i]); } } return new Measurement(map); } } // class BasicTransformerAdapter private MeasurementFactory() { } protected static Measurements load( Session session, String river, double location, DateRange dateRange ) { boolean debug = log.isDebugEnabled(); if (debug) { log.debug(SQL_MEASSURE); } SQLQuery query = session.createSQLQuery(SQL_MEASSURE) .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) .addScalar("DATUM", StandardBasicTypes.DATE) .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER) .addScalar("LFDNR", StandardBasicTypes.INTEGER) .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("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("SIEB01", StandardBasicTypes.DOUBLE) .addScalar("SIEB02", StandardBasicTypes.DOUBLE) .addScalar("SIEB03", StandardBasicTypes.DOUBLE) .addScalar("SIEB04", StandardBasicTypes.DOUBLE) .addScalar("SIEB05", StandardBasicTypes.DOUBLE) .addScalar("SIEB06", StandardBasicTypes.DOUBLE) .addScalar("SIEB07", StandardBasicTypes.DOUBLE) .addScalar("SIEB08", StandardBasicTypes.DOUBLE) .addScalar("SIEB09", StandardBasicTypes.DOUBLE) .addScalar("SIEB10", StandardBasicTypes.DOUBLE) .addScalar("SIEB11", StandardBasicTypes.DOUBLE) .addScalar("SIEB12", StandardBasicTypes.DOUBLE) .addScalar("SIEB13", StandardBasicTypes.DOUBLE) .addScalar("SIEB14", StandardBasicTypes.DOUBLE) .addScalar("SIEB15", StandardBasicTypes.DOUBLE) .addScalar("SIEB16", StandardBasicTypes.DOUBLE) .addScalar("SIEB17", StandardBasicTypes.DOUBLE) .addScalar("SIEB18", StandardBasicTypes.DOUBLE) .addScalar("SIEB19", StandardBasicTypes.DOUBLE) .addScalar("SIEB20", StandardBasicTypes.DOUBLE) .addScalar("SIEB21", StandardBasicTypes.DOUBLE); query.setString("river_name", river); query.setDouble("location", location); query.setDate("from", dateRange.getFrom()); query.setDate("to", dateRange.getTo()); query.setResultTransformer(MeasurementResultTransformer.INSTANCE); @SuppressWarnings("unchecked") List<Measurement> measuments = (List<Measurement>)query.list(); Integer lastLR = null; for (int i = 0, N = measuments.size(); i < N; ++i) { Measurement m = measuments.get(i); Integer currentLR = (Integer)m.getData("GLOTRECHTEID"); boolean newDS = lastLR == null || (currentLR != null && !lastLR.equals(currentLR)); Measurement p = i > 0 ? measuments.get(i-1) : null; Measurement n = i < N-1 ? measuments.get(i+1) : null; m.setPrev(newDS ? null : p); m.setNext(n); if (p != null && newDS) { p.setNext(null); } lastLR = currentLR; } return new Measurements(measuments); } public static Measurements getMeasurements( String river, double location, DateRange dateRange ) { Session session = SedDBSessionHolder.HOLDER.get(); try { return load(session, river, location, dateRange); } finally { session.close(); } } } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :