sascha@3289: package de.intevation.flys.artifacts.model.sq; sascha@3289: teichmann@3992: import java.util.ArrayList; teichmann@4005: import java.util.Calendar; teichmann@4005: import java.util.Collections; 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: teichmann@4007: public static final String SQL_FACTIONS = teichmann@4007: "SELECT m.datum AS DATUM," + teichmann@4007: "g.GLOTRECHTEID AS GLOTRECHTEID," + teichmann@4007: "gp.LFDNR AS LFDNR," + teichmann@4007: "g.UFERABST AS UFERABST," + teichmann@4007: "g.UFERABLINKS AS UFERABLINKS," + teichmann@4007: "m.TSCHWEB AS TSCHWEB," + teichmann@4007: "m.TSAND AS TSAND," + teichmann@4007: "gp.GTRIEB AS GTRIEB," + teichmann@4007: "m.TGESCHIEBE AS TGESCHIEBE," + teichmann@4007: "sie.SIEB01 AS SIEB01, sie.SIEB02 AS SIEB02," + teichmann@4007: "sie.SIEB03 AS SIEB03, sie.SIEB04 AS SIEB04," + teichmann@4007: "sie.SIEB05 AS SIEB05, sie.SIEB06 AS SIEB06," + teichmann@4007: "sie.SIEB07 AS SIEB07, sie.SIEB08 AS SIEB08," + teichmann@4007: "sie.SIEB09 AS SIEB09, sie.SIEB10 AS SIEB10," + teichmann@4007: "sie.SIEB11 AS SIEB11, sie.SIEB12 AS SIEB12," + teichmann@4007: "sie.SIEB13 AS SIEB13, sie.SIEB14 AS SIEB14," + teichmann@4007: "sie.SIEB15 AS SIEB15, sie.SIEB16 AS SIEB16," + teichmann@4007: "sie.SIEB17 AS SIEB17, sie.SIEB18 AS SIEB18," + teichmann@4007: "sie.SIEB19 AS SIEB19, sie.SIEB20 AS SIEB20," + teichmann@4007: "sie.SIEB21 AS SIEB21," + teichmann@4007: "gs.RSIEB01 AS RSIEB01, gs.RSIEB02 AS RSIEB02," + teichmann@4007: "gs.RSIEB03 AS RSIEB03, gs.RSIEB04 AS RSIEB04," + teichmann@4007: "gs.RSIEB05 AS RSIEB05, gs.RSIEB06 AS RSIEB06," + teichmann@4007: "gs.RSIEB07 AS RSIEB07, gs.RSIEB08 AS RSIEB08," + teichmann@4007: "gs.RSIEB09 AS RSIEB09, gs.RSIEB10 AS RSIEB10," + teichmann@4007: "gs.RSIEB11 AS RSIEB11, gs.RSIEB12 AS RSIEB12," + teichmann@4007: "gs.RSIEB13 AS RSIEB13, gs.RSIEB14 AS RSIEB14," + teichmann@4007: "gs.RSIEB15 AS RSIEB15, gs.RSIEB16 AS RSIEB16," + teichmann@4007: "gs.RSIEB17 AS RSIEB17, gs.RSIEB18 AS RSIEB18," + teichmann@4007: "gs.RSIEB19 AS RSIEB19, gs.RSIEB20 AS RSIEB20," + teichmann@4007: "gs.RSIEB21 AS RSIEB21, 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: teichmann@4007: private static final int index(String s) { teichmann@4007: return Integer.parseInt(s.substring(s.length()-2))-1; teichmann@4007: } teichmann@3992: teichmann@4007: public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER = teichmann@4007: new BasicTransformerAdapter() { teichmann@3992: teichmann@4007: @Override teichmann@4007: public Object transformTuple(Object [] tuple, String [] aliases) { teichmann@4007: Map map = new HashMap(); teichmann@4007: teichmann@4007: Sieve [] sieves = new Sieve[21]; teichmann@4007: teichmann@4007: List validSieves = new ArrayList(21); teichmann@4007: teichmann@4007: for (int i = 0; i < tuple.length; ++i) { teichmann@4007: Object value = tuple[i]; teichmann@4007: if (value == null) { teichmann@4007: continue; teichmann@4007: } teichmann@4007: String alias = aliases[i]; teichmann@4007: if (alias.startsWith("SIEB")) { teichmann@4007: Sieve s = new Sieve((Double)value, 0d); teichmann@4007: sieves[index(alias)] = s; teichmann@4007: } teichmann@4007: else if (alias.startsWith("RSIEB")) { teichmann@4007: Sieve s = sieves[index(alias)]; teichmann@4007: if (s != null) { teichmann@4007: s.setLoad((Double)value); teichmann@4007: validSieves.add(s); teichmann@4007: } teichmann@4007: } teichmann@4007: else if (alias.equals("REST")) { teichmann@4007: Sieve s = new Sieve(0d, (Double)value); teichmann@3992: validSieves.add(s); teichmann@3992: } teichmann@4007: else { teichmann@4007: map.put(alias, value); teichmann@4007: } teichmann@3992: } teichmann@4007: teichmann@4007: return new Measurement(map, validSieves); sascha@3289: } teichmann@4007: }; 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) { teichmann@4007: log.debug(SQL_FACTIONS); sascha@3308: } sascha@3308: teichmann@4007: SQLQuery query = session.createSQLQuery(SQL_FACTIONS) 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("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: teichmann@4007: query.setResultTransformer(FRACTIONS_TRANSFORMER); sascha@3289: sascha@3928: @SuppressWarnings("unchecked") teichmann@4006: 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@4005: return new Measurements(measuments, separateByDate(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@4005: 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@4005: double sumSandF = 0d; teichmann@4005: double sumCoarseF = 0d; teichmann@4005: double sumGravelF = 0d; teichmann@4005: double sumNorm = 0d; teichmann@4005: teichmann@4005: for (Measurement m: measurements) { teichmann@4005: SieveArray sa = m.getSieveArray(); teichmann@4005: double sandF = sa.sandNormFraction(); teichmann@4005: double coarseF = sa.coarseNormFraction(); teichmann@4005: double gravelF = sa.gravelNormFraction(); teichmann@4005: double effWidth = m.get("EFFWIDTH"); teichmann@4005: double gt = m.get("GTRIEB"); teichmann@4005: double scale = effWidth*gt; teichmann@4005: sumSandF += scale*sandF; teichmann@4005: sumCoarseF += scale*coarseF; teichmann@4005: sumGravelF += scale*gravelF; teichmann@4005: sumNorm += scale; teichmann@4005: } teichmann@4005: teichmann@4005: Map data = teichmann@4005: new HashMap(measurements.get(0).getData()); teichmann@4005: teichmann@4005: Measurement m = new Measurement(data, Collections.emptyList()); teichmann@4005: teichmann@4005: sumNorm = 1d/sumNorm; teichmann@4005: teichmann@4005: m.set("BL_S", sumNorm*sumSandF); teichmann@4005: m.set("BL_G", sumNorm*sumGravelF); teichmann@4005: m.set("BL_C", sumNorm*sumCoarseF); teichmann@4005: teichmann@4005: return m; teichmann@4004: } teichmann@4004: teichmann@4004: teichmann@4004: private static final boolean equalDate(Date a, Date b) { teichmann@4005: Calendar ca = Calendar.getInstance(); teichmann@4005: Calendar cb = Calendar.getInstance(); teichmann@4005: ca.setTime(a); teichmann@4005: cb.setTime(b); teichmann@4006: return ca.get(Calendar.YEAR) == cb.get(Calendar.YEAR) teichmann@4006: && ca.get(Calendar.MONTH) == cb.get(Calendar.MONTH) teichmann@4005: && ca.get(Calendar.DAY_OF_MONTH) == cb.get(Calendar.DAY_OF_MONTH); 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 :