sascha@3289: package de.intevation.flys.artifacts.model.sq; sascha@3289: sascha@3928: import java.util.List; sascha@3928: sascha@3289: import de.intevation.flys.artifacts.model.DateRange; sascha@3289: sascha@3289: import de.intevation.flys.backend.SedDBSessionHolder; sascha@3289: sascha@3289: import java.util.HashMap; sascha@3289: import java.util.Map; sascha@3289: sascha@3289: import org.apache.log4j.Logger; sascha@3289: sascha@3289: import org.hibernate.SQLQuery; sascha@3289: import org.hibernate.Session; sascha@3289: sascha@3289: import org.hibernate.transform.BasicTransformerAdapter; sascha@3289: sascha@3289: import org.hibernate.type.StandardBasicTypes; sascha@3289: sascha@3289: public class MeasurementFactory sascha@3289: { sascha@3289: private static final Logger log = sascha@3289: Logger.getLogger(MeasurementFactory.class); sascha@3289: sascha@3289: public static final String SQL_MEASSURE = sascha@3928: "SELECT m.datum AS DATUM," + sascha@3928: "g.UFERABST AS UFERABST," + sascha@3928: "g.UFERABLINKS AS UFERABLINKS," + sascha@3928: "m.tschweb AS TSCHWEB," + sascha@3928: "m.TSAND AS TSAND," + sascha@3928: "gp.MESSDAUER AS MESSDAUER," + sascha@3928: "gp.MENGE AS MENGE," + sascha@3928: "gp.GTRIEB AS GTRIEB," + sascha@3928: "gp.LFDNR AS LFDNR," + sascha@3928: "m.TGESCHIEBE AS TGESCHIEBE," + sascha@3928: "gs.RSIEB01 AS RSIEB01," + sascha@3928: "gs.RSIEB02 AS RSIEB02," + sascha@3928: "gs.RSIEB03 AS RSIEB03," + sascha@3928: "gs.RSIEB04 AS RSIEB04," + sascha@3928: "gs.RSIEB05 AS RSIEB05," + sascha@3928: "gs.RSIEB06 AS RSIEB06," + sascha@3928: "gs.RSIEB07 AS RSIEB07," + sascha@3928: "gs.RSIEB08 AS RSIEB08," + sascha@3928: "gs.RSIEB09 AS RSIEB09," + sascha@3928: "gs.RSIEB10 AS RSIEB10," + sascha@3928: "gs.RSIEB11 AS RSIEB11," + sascha@3928: "gs.RSIEB12 AS RSIEB12," + sascha@3928: "gs.RSIEB13 AS RSIEB13," + sascha@3928: "gs.RSIEB14 AS RSIEB14," + sascha@3928: "gs.RSIEB15 AS RSIEB15," + sascha@3928: "gs.RSIEB16 AS RSIEB16," + sascha@3928: "gs.RSIEB17 AS RSIEB17," + sascha@3928: "gs.RSIEB18 AS RSIEB18," + sascha@3928: "gs.RSIEB19 AS RSIEB19," + sascha@3928: "gs.RSIEB20 AS RSIEB20," + sascha@3928: "gs.RSIEB21 AS RSIEB21," + sascha@3928: "gs.REST AS REST," + sascha@3928: "g.GLOTRECHTEID AS GLOTRECHTEID " + sascha@3928: "FROM MESSUNG m " + sascha@3928: "JOIN STATION s ON m.STATIONID = s.STATIONID " + sascha@3928: "JOIN glotrechte g ON m.MESSUNGID = g.MESSUNGID " + sascha@3928: "JOIN gprobe gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " + sascha@3928: "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " + sascha@3310: "WHERE " + sascha@3928: "g.NAME = :river_name " + sascha@3396: "AND m.Q_BPEGEL IS NOT NULL " + sascha@3928: "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " + sascha@3928: "AND m.DATUM BETWEEN :from AND :to " + sascha@3928: "AND m.TGESCHIEBE IS NOT NULL " + sascha@3928: "ORDER BY m.DATUM"; sascha@3289: sascha@3289: public static final class MeasurementResultTransformer sascha@3289: extends BasicTransformerAdapter sascha@3289: { sascha@3289: public static MeasurementResultTransformer INSTANCE = sascha@3289: new MeasurementResultTransformer(); sascha@3289: sascha@3289: public MeasurementResultTransformer() { sascha@3289: } sascha@3289: sascha@3289: @Override sascha@3289: public Object transformTuple(Object [] tuple, String [] aliases) { sascha@3289: Map map = new HashMap(); sascha@3289: for (int i = 0; i < tuple.length; ++i) { sascha@3291: if (tuple[i] != null) { sascha@3291: map.put(aliases[i], tuple[i]); sascha@3291: } sascha@3289: } sascha@3289: return new Measurement(map); sascha@3289: } sascha@3289: } // class BasicTransformerAdapter sascha@3289: sascha@3289: private MeasurementFactory() { sascha@3289: } sascha@3289: sascha@3290: protected static Measurements load( sascha@3289: Session session, sascha@3289: String river, sascha@3289: double location, sascha@3289: DateRange dateRange sascha@3289: ) { sascha@3308: boolean debug = log.isDebugEnabled(); sascha@3308: sascha@3308: if (debug) { sascha@3308: log.debug(SQL_MEASSURE); sascha@3308: } sascha@3308: sascha@3289: SQLQuery query = session.createSQLQuery(SQL_MEASSURE) sascha@3928: .addScalar("TSAND", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("DATUM", StandardBasicTypes.DATE) sascha@3928: .addScalar("UFERABST", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("TSAND", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("MESSDAUER", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("MENGE", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("GTRIEB", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("LFDNR", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB01", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB02", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB03", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB04", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB05", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB06", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB07", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB08", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB09", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB10", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB11", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB12", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB13", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB14", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB15", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB16", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB17", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB18", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB19", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB20", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("RSIEB21", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("REST", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("GLOTRECHTEID", StandardBasicTypes.DOUBLE); sascha@3292: sascha@3289: query.setString("river_name", river); sascha@3289: query.setDouble("location", location); sascha@3289: query.setDate("from", dateRange.getFrom()); sascha@3289: query.setDate("to", dateRange.getTo()); sascha@3289: sascha@3289: query.setResultTransformer(MeasurementResultTransformer.INSTANCE); sascha@3289: sascha@3928: @SuppressWarnings("unchecked") sascha@3928: List measuments = (List)query.list(); sascha@3928: sascha@3928: for (int i = 0, N = measuments.size(); i < N; ++i) { sascha@3928: Measurement m = measuments.get(i); sascha@3928: Measurement p = i > 0 ? measuments.get(i-1) : null; sascha@3928: Measurement n = i < N-1 ? measuments.get(i+1) : null; sascha@3928: m.setPrev(p); sascha@3928: m.setNext(n); sascha@3928: } sascha@3928: sascha@3928: return new Measurements(measuments); sascha@3289: } sascha@3289: sascha@3290: public static Measurements getMeasurements( sascha@3289: String river, sascha@3289: double location, sascha@3289: DateRange dateRange sascha@3289: ) { sascha@3289: Session session = SedDBSessionHolder.HOLDER.get(); sascha@3289: try { sascha@3289: return load(session, river, location, dateRange); sascha@3289: } sascha@3289: finally { sascha@3289: session.close(); sascha@3289: } sascha@3289: } sascha@3289: } sascha@3289: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :