sascha@3289: package de.intevation.flys.artifacts.model.sq; sascha@3289: teichmann@3992: import java.util.ArrayList; teichmann@4004: import java.util.Date; sascha@3928: import java.util.List; teichmann@4001: import java.util.TreeMap; 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@4004: "AND m.DATUM 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@4001: "ORDER BY m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR"; 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@4001: List same = new ArrayList(); teichmann@4001: teichmann@3981: Integer lastLR = null; teichmann@3981: teichmann@4001: List accumulated = new ArrayList(); teichmann@4001: teichmann@4001: for (Measurement m: measuments) { 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: teichmann@4001: if (newDS && !same.isEmpty()) { teichmann@4001: accumulated.add(accumulate(same)); teichmann@4001: same.clear(); teichmann@3981: } teichmann@3981: teichmann@3981: lastLR = currentLR; sascha@3928: } sascha@3928: teichmann@4001: if (!same.isEmpty()) { teichmann@4001: accumulated.add(accumulate(same)); teichmann@4001: } teichmann@4001: teichmann@4001: for (Measurement m: accumulated) { teichmann@4001: m.adjustSieves(); teichmann@4001: } teichmann@4001: teichmann@4001: return new Measurements(measuments, accumulated); teichmann@4001: } teichmann@4001: teichmann@4004: protected static List separateByDate(List measurements) { teichmann@4004: teichmann@4004: List result = new ArrayList(); teichmann@4004: teichmann@4004: List same = new ArrayList(); teichmann@4004: teichmann@4004: Date lastDate = null; teichmann@4004: teichmann@4004: for (Measurement m: measurements) { teichmann@4004: Date currentDate = (Date)m.getData("DATUM"); teichmann@4004: if ((lastDate == null teichmann@4004: || !equalDate(currentDate, lastDate)) teichmann@4004: && !same.isEmpty() teichmann@4004: ) { teichmann@4004: result.add(processSameDate(same)); teichmann@4004: same.clear(); teichmann@4004: } teichmann@4004: same.add(m); teichmann@4004: lastDate = currentDate; teichmann@4004: } teichmann@4004: teichmann@4004: if (!same.isEmpty()) { teichmann@4004: result.add(processSameDate(same)); teichmann@4004: } teichmann@4004: teichmann@4004: return result; teichmann@4004: } teichmann@4004: teichmann@4004: protected static Measurement processSameDate(List measurements) { teichmann@4004: int N = measurements.size(); teichmann@4004: if (N == 1) { teichmann@4004: Measurement current = measurements.get(0); teichmann@4004: double left = current.get("UFERABLINKS"); teichmann@4004: double right = current.get("UFERABST"); teichmann@4004: current.set("EFFWIDTH", left + right); teichmann@4004: } teichmann@4004: else { teichmann@4004: for (int i = 0; i < N; ++i) { teichmann@4004: Measurement current = measurements.get(i); teichmann@4004: teichmann@4004: if (i == 0) { teichmann@4004: Measurement next = measurements.get(i+1); teichmann@4004: double distCurrent = current.get("UFERABST"); teichmann@4004: double distNext = next.get("UFERABST"); teichmann@4004: current.set("EFFWIDTH", distNext - distCurrent); teichmann@4004: } teichmann@4004: else if (i == N-1) { teichmann@4004: Measurement prev = measurements.get(i-1); teichmann@4004: double distCurrent = current.get("UFERABST"); teichmann@4004: double distPrev = prev.get("UFERABST"); teichmann@4004: current.set("EFFWIDTH", distCurrent - distPrev); teichmann@4004: } teichmann@4004: else { teichmann@4004: Measurement prev = measurements.get(i-1); teichmann@4004: Measurement next = measurements.get(i+1); teichmann@4004: double distPrev = prev.get("UFERABST"); teichmann@4004: double distNext = next.get("UFERABST"); teichmann@4004: current.set("EFFWIDTH", 0.5*(distNext - distPrev)); teichmann@4004: } teichmann@4004: } teichmann@4004: } teichmann@4004: teichmann@4004: return null; teichmann@4004: } teichmann@4004: teichmann@4004: teichmann@4004: private static final boolean equalDate(Date a, Date b) { teichmann@4004: // TODO: compare only year, month and day. teichmann@4004: return a.equals(b); teichmann@4004: } teichmann@4004: teichmann@4004: teichmann@4001: protected static Measurement accumulate(List measuments) { teichmann@4001: teichmann@4001: int N = measuments.size(); teichmann@4001: if (N == 1) { teichmann@4001: return measuments.get(0); teichmann@4001: } teichmann@4001: teichmann@4001: TreeMap diameters = teichmann@4001: new TreeMap(Sieve.DIAMETER_CMP); teichmann@4001: teichmann@4001: for (Measurement m: measuments) { teichmann@4001: for (Sieve s: m.getSieves()) { teichmann@4001: Double key = s.getDiameter(); teichmann@4001: double [] sum = diameters.get(key); teichmann@4001: if (sum == null) { teichmann@4001: sum = new double[1]; teichmann@4001: diameters.put(key, sum); teichmann@4001: } teichmann@4001: sum[0] += s.getLoad(); teichmann@4001: } teichmann@4001: } teichmann@4001: List accumulatedSieves = new ArrayList(diameters.size()); teichmann@4001: for (Map.Entry entry: diameters.entrySet()) { teichmann@4001: accumulatedSieves.add( teichmann@4001: new Sieve(entry.getKey(), teichmann@4001: entry.getValue()[0]/N)); teichmann@4001: } teichmann@4001: teichmann@4001: Map data = teichmann@4001: new HashMap(measuments.get(0).getData()); teichmann@4001: teichmann@4001: return new Measurement(data, accumulatedSieves); 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 :