raimund@3755: package de.intevation.flys.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: raimund@3755: import de.intevation.flys.backend.SedDBSessionHolder; raimund@3755: raimund@3758: raimund@3755: public class QualityMeasurementFactory { raimund@3755: raimund@3769: private static Logger logger = 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," + 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 " + raimund@3874: " 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 " + raimund@3755: " st.datum BETWEEN :start AND :end"; raimund@3755: raimund@3758: private static final String SQL_BEDLOAD_MEASUREMENT = raimund@3756: "SELECT m.km as km," + raimund@3769: " m.datum as datum," + 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 " + raimund@3874: " 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" + raimund@3756: " m.datum BETWEEN :start AND :end"; raimund@3756: raimund@3755: public static final class QualityMeasurementResultTransformer raimund@3758: extends BasicTransformerAdapter { raimund@3758: raimund@3758: public static QualityMeasurementResultTransformer INSTANCE = new QualityMeasurementResultTransformer(); raimund@3755: raimund@3755: public QualityMeasurementResultTransformer() { raimund@3755: } raimund@3755: 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) 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: 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 { raimund@3874: //session.close(); raimund@3755: } raimund@3755: } raimund@3755: }