sascha@3289: package de.intevation.flys.artifacts.model.sq; sascha@3289: teichmann@3992: import java.util.ArrayList; 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," + teichmann@3981: "g.GLOTRECHTEID AS GLOTRECHTEID," + teichmann@3981: "gp.LFDNR AS LFDNR," + sascha@3928: "g.UFERABST AS UFERABST," + sascha@3928: "g.UFERABLINKS AS UFERABLINKS," + sascha@3929: "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: "m.TGESCHIEBE AS TGESCHIEBE," + teichmann@3992: "sie.SIEB01 AS SIEB01," + teichmann@3992: "sie.SIEB02 AS SIEB02," + teichmann@3992: "sie.SIEB03 AS SIEB03," + teichmann@3992: "sie.SIEB04 AS SIEB04," + teichmann@3992: "sie.SIEB05 AS SIEB05," + teichmann@3992: "sie.SIEB06 AS SIEB06," + teichmann@3992: "sie.SIEB07 AS SIEB07," + teichmann@3992: "sie.SIEB08 AS SIEB08," + teichmann@3992: "sie.SIEB09 AS SIEB09," + teichmann@3992: "sie.SIEB10 AS SIEB10," + teichmann@3992: "sie.SIEB11 AS SIEB11," + teichmann@3992: "sie.SIEB12 AS SIEB12," + teichmann@3992: "sie.SIEB13 AS SIEB13," + teichmann@3992: "sie.SIEB14 AS SIEB14," + teichmann@3992: "sie.SIEB15 AS SIEB15," + teichmann@3992: "sie.SIEB16 AS SIEB16," + teichmann@3992: "sie.SIEB17 AS SIEB17," + teichmann@3992: "sie.SIEB18 AS SIEB18," + teichmann@3992: "sie.SIEB19 AS SIEB19," + teichmann@3992: "sie.SIEB20 AS SIEB20," + teichmann@3992: "sie.SIEB21 AS SIEB21," + 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," + teichmann@3992: "gs.REST AS REST " + 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 " + teichmann@3981: "AND (" + teichmann@3981: "COALESCE(gs.RSIEB01, 0) + COALESCE(gs.RSIEB02, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB03, 0) + COALESCE(gs.RSIEB04, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB05, 0) + COALESCE(gs.RSIEB06, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB07, 0) + COALESCE(gs.RSIEB08, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB09, 0) + COALESCE(gs.RSIEB10, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB11, 0) + COALESCE(gs.RSIEB12, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" + teichmann@3981: "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " + teichmann@3981: "ORDER BY m.DATUM, gp.LFDNR, g.UFERABST"; 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: teichmann@3992: private static final int index(String s) { teichmann@3992: return Integer.parseInt(s.substring(s.length()-2))-1; teichmann@3992: } teichmann@3992: sascha@3289: @Override sascha@3289: public Object transformTuple(Object [] tuple, String [] aliases) { sascha@3289: Map map = new HashMap(); teichmann@3992: teichmann@3992: Sieve [] sieves = new Sieve[20]; teichmann@3992: teichmann@3992: List validSieves = new ArrayList(20); teichmann@3992: sascha@3289: for (int i = 0; i < tuple.length; ++i) { teichmann@3992: Object value = tuple[i]; teichmann@3992: if (value == null) { teichmann@3992: continue; teichmann@3992: } teichmann@3992: String alias = aliases[i]; teichmann@3992: if (alias.startsWith("SIEB")) { teichmann@3992: Sieve s = new Sieve((Double)value, 0d); teichmann@3992: sieves[index(alias)] = s; teichmann@3992: } teichmann@3992: else if (alias.startsWith("RSIEB")) { teichmann@3992: Sieve s = sieves[index(alias)]; teichmann@3992: if (s != null) { teichmann@3992: s.setLoad((Double)value); teichmann@3992: validSieves.add(s); teichmann@3992: } teichmann@3992: } teichmann@3992: else if (alias.equals("REST")) { teichmann@3992: Sieve s = new Sieve(0d, (Double)value); teichmann@3992: validSieves.add(s); teichmann@3992: } teichmann@3992: else { teichmann@3992: map.put(alias, value); sascha@3291: } sascha@3289: } teichmann@3992: teichmann@3992: return new Measurement(map, validSieves); 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("Q_BPEGEL", StandardBasicTypes.DOUBLE) sascha@3928: .addScalar("DATUM", StandardBasicTypes.DATE) teichmann@3981: .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER) teichmann@3981: .addScalar("LFDNR", StandardBasicTypes.INTEGER) 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("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) teichmann@3981: .addScalar("SIEB01", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB02", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB03", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB04", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB05", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB06", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB07", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB08", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB09", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB10", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB11", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB12", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB13", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB14", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB15", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB16", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB17", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB18", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB19", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB20", StandardBasicTypes.DOUBLE) teichmann@3981: .addScalar("SIEB21", 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: teichmann@3981: Integer lastLR = null; teichmann@3981: sascha@3928: for (int i = 0, N = measuments.size(); i < N; ++i) { sascha@3928: Measurement m = measuments.get(i); teichmann@3981: teichmann@3981: Integer currentLR = (Integer)m.getData("GLOTRECHTEID"); teichmann@3981: teichmann@3981: boolean newDS = lastLR == null teichmann@3981: || (currentLR != null && !lastLR.equals(currentLR)); teichmann@3981: sascha@3928: Measurement p = i > 0 ? measuments.get(i-1) : null; sascha@3928: Measurement n = i < N-1 ? measuments.get(i+1) : null; teichmann@3981: m.setPrev(newDS ? null : p); sascha@3928: m.setNext(n); teichmann@3981: teichmann@3981: if (p != null && newDS) { teichmann@3981: p.setNext(null); teichmann@3981: } teichmann@3981: teichmann@3981: lastLR = currentLR; 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 :