teichmann@5863: /* Copyright (C) 2011, 2012, 2013 by Bundesanstalt für Gewässerkunde teichmann@5863: * Software engineering by Intevation GmbH teichmann@5863: * teichmann@5994: * This file is Free Software under the GNU AGPL (>=v3) teichmann@5863: * and comes with ABSOLUTELY NO WARRANTY! Check out the teichmann@5994: * documentation coming with Dive4Elements River for details. teichmann@5863: */ teichmann@5863: teichmann@5831: package org.dive4elements.river.artifacts.model.minfo; raimund@3755: raimund@3755: import java.util.Date; raimund@3755: import java.util.HashMap; raimund@3755: import java.util.Map; raimund@3755: raimund@3769: import org.apache.log4j.Logger; raimund@3755: import org.hibernate.SQLQuery; raimund@3755: import org.hibernate.Session; raimund@3755: import org.hibernate.transform.BasicTransformerAdapter; raimund@3755: import org.hibernate.type.StandardBasicTypes; raimund@3755: teichmann@5831: import org.dive4elements.river.backend.SedDBSessionHolder; raimund@3755: raimund@3758: raimund@3755: public class QualityMeasurementFactory { raimund@3755: teichmann@8202: private static Logger log = Logger.getLogger(QualityMeasurementFactory.class); raimund@3769: raimund@3758: private static final String SQL_BED_MEASUREMENT = raimund@3758: "SELECT st.km as km," + raimund@3769: " st.datum as datum," + raimund@3769: " sp.tiefevon as depth1," + raimund@3769: " sp.tiefebis as depth2," + raimund@3758: " sa.d10 as d10," + felix@7630: " sa.dm as dm," + raimund@3758: " sa.d16 as d16," + raimund@3758: " sa.d20 as d20," + raimund@3758: " sa.d25 as d25," + raimund@3758: " sa.d30 as d30," + raimund@3758: " sa.d40 as d40," + raimund@3758: " sa.d50 as d50," + raimund@3758: " sa.d60 as d60," + raimund@3758: " sa.d70 as d70," + raimund@3758: " sa.d75 as d75," + raimund@3758: " sa.d80 as d80," + raimund@3758: " sa.d84 as d84," + raimund@3758: " sa.d90 as d90," + raimund@3758: " sa.dmin as dmin," + raimund@3758: " sa.dmax as dmax " + raimund@3769: "FROM sohltest st " + raimund@3769: " JOIN station sn ON sn.stationid = st.stationid " + raimund@3769: " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " + raimund@3769: " JOIN sohlprobe sp ON sp.sohltestid = st.sohltestid " + raimund@3758: " JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid " + raimund@3755: "WHERE gw.name = :name AND " + raimund@3755: " st.km IS NOT NULL AND " + raimund@3769: " sp.tiefevon IS NOT NULL AND " + ingo@3785: " sp.tiefebis IS NOT NULL AND " + // TODO: Test if char diameter ist null. raimund@3769: " st.km BETWEEN :from - 0.001 AND :to + 0.001 AND " + rrenkert@6358: " st.datum BETWEEN :start AND :end " + rrenkert@6358: "ORDER BY st.km"; raimund@3755: raimund@3758: private static final String SQL_BEDLOAD_MEASUREMENT = raimund@3756: "SELECT m.km as km," + raimund@3769: " m.datum as datum," + felix@7630: " m.dm as dm," + raimund@3756: " m.d10 as d10," + raimund@3756: " m.d16 as d16," + raimund@3756: " m.d20 as d20," + raimund@3756: " m.d25 as d25," + raimund@3756: " m.d30 as d30," + raimund@3756: " m.d40 as d40," + raimund@3756: " m.d50 as d50," + raimund@3756: " m.d60 as d60," + raimund@3756: " m.d70 as d70," + raimund@3756: " m.d75 as d75," + raimund@3756: " m.d80 as d80," + raimund@3756: " m.d84 as d84," + raimund@3756: " m.d90 as d90," + raimund@3756: " m.dmin as dmin," + raimund@3756: " m.dmax as dmax " + raimund@3756: "FROM messung m" + raimund@3756: " JOIN station sn ON sn.stationid = m.stationid" + raimund@3758: " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " + raimund@3756: "WHERE gw.name = :name AND " + raimund@3756: " m.km IS NOT NULL AND " + ingo@3785: " m.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter. raimund@3756: " m.km BETWEEN :from - 0.001 AND :to + 0.001 AND" + rrenkert@6358: " m.datum BETWEEN :start AND :end " + rrenkert@6358: "ORDER BY m.km"; raimund@3756: felix@6767: /** Transform query result into objects, use INSTANCE singleton. */ raimund@3755: public static final class QualityMeasurementResultTransformer raimund@3758: extends BasicTransformerAdapter { raimund@3758: felix@6767: // Make a singleton felix@6767: public static QualityMeasurementResultTransformer INSTANCE = felix@6767: new QualityMeasurementResultTransformer(); raimund@3755: felix@6767: private QualityMeasurementResultTransformer() { raimund@3755: } raimund@3755: felix@6781: /** tuples is a row. */ raimund@3755: @Override raimund@3758: public Object transformTuple(Object[] tuple, String[] aliases) { raimund@3755: Map map = new HashMap(); raimund@3755: double km = 0; raimund@3755: Date d = null; raimund@3769: double depth1 = Double.NaN; raimund@3769: double depth2 = Double.NaN; raimund@3755: for (int i = 0; i < tuple.length; ++i) { raimund@3755: if (tuple[i] != null) { raimund@3755: if (aliases[i].equals("km")) { raimund@3758: km = ((Number) tuple[i]).doubleValue(); raimund@3755: } raimund@3769: else if (aliases[i].equals("datum")) { raimund@3758: d = (Date) tuple[i]; raimund@3758: } raimund@3758: else if (aliases[i].equals("depth1")) { raimund@3758: depth1 = ((Number) tuple[i]).doubleValue(); raimund@3758: } raimund@3758: else if (aliases[i].equals("depth2")) { raimund@3758: depth2 = ((Number) tuple[i]).doubleValue(); raimund@3755: } raimund@3755: else { raimund@3769: map.put(aliases[i], ((Double) tuple[i])/1000); raimund@3755: } raimund@3755: } raimund@3755: } raimund@3758: return new QualityMeasurement(km, d, depth1, depth2, map); raimund@3755: } raimund@3755: } // class BasicTransformerAdapter raimund@3755: raimund@3755: private QualityMeasurementFactory() { raimund@3755: } raimund@3755: raimund@3755: protected static QualityMeasurements load( raimund@3755: Session session, raimund@3755: String river, raimund@3755: double from, raimund@3755: double to, raimund@3755: Date start, raimund@3756: Date end, raimund@3756: String statement raimund@3755: ) { raimund@3756: SQLQuery query = session.createSQLQuery(statement) raimund@3758: .addScalar("km", StandardBasicTypes.DOUBLE) raimund@3769: .addScalar("datum", StandardBasicTypes.DATE) felix@7630: .addScalar("dm", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d10", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d16", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d20", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d25", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d30", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d40", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d50", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d60", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d70", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d75", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d80", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d84", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d90", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("dmin", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("dmax", StandardBasicTypes.DOUBLE); raimund@3758: raimund@3758: if (statement.equals(SQL_BED_MEASUREMENT)) { raimund@3758: query.addScalar("depth1", StandardBasicTypes.DOUBLE); raimund@3758: query.addScalar("depth2", StandardBasicTypes.DOUBLE); raimund@3758: } raimund@3755: raimund@3755: query.setString("name", river); raimund@3755: query.setDouble("from", from); raimund@3758: query.setDouble("to", to); raimund@3755: query.setDate("start", start); raimund@3755: query.setDate("end", end); raimund@3755: raimund@3755: query.setResultTransformer( raimund@3755: QualityMeasurementResultTransformer.INSTANCE); raimund@3755: raimund@3755: return new QualityMeasurements(query.list()); raimund@3755: } raimund@3755: felix@6781: /** Get all measurements. */ raimund@3756: public static QualityMeasurements getBedMeasurements( raimund@3755: String river, raimund@3755: double from, raimund@3755: double to, raimund@3755: Date start, raimund@3758: Date end) { raimund@3755: Session session = SedDBSessionHolder.HOLDER.get(); raimund@3755: try { raimund@3758: return load(session, river, from, to, start, end, raimund@3758: SQL_BED_MEASUREMENT); raimund@3756: } raimund@3756: finally { raimund@3769: //session.close(); raimund@3756: } raimund@3756: } raimund@3756: raimund@3756: public static QualityMeasurements getBedloadMeasurements( raimund@3756: String river, raimund@3756: double from, raimund@3756: double to, raimund@3756: Date start, raimund@3756: Date end raimund@3758: ) { raimund@3756: Session session = SedDBSessionHolder.HOLDER.get(); raimund@3756: try { raimund@3756: return load( raimund@3756: session, raimund@3756: river, raimund@3756: from, raimund@3756: to, raimund@3756: start, raimund@3756: end, raimund@3756: SQL_BEDLOAD_MEASUREMENT); raimund@3755: } raimund@3755: finally { ingo@3785: //session.close(); raimund@3755: } raimund@3755: } felix@4807: } felix@4807: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :