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@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@3755: public class QualityMeasurementFactory {
raimund@3755: 
raimund@3755:     private static final String SQL_MEASUREMENT = 
raimund@3755:         "SELECT st.km    as km," +
raimund@3755:         "       st.datum as date,"+
raimund@3755:         "       sa.d10   as d10," +
raimund@3755:         "       sa.d16   as d16," +
raimund@3755:         "       sa.d20   as d20," +
raimund@3755:         "       sa.d25   as d25," +
raimund@3755:         "       sa.d30   as d30," +
raimund@3755:         "       sa.d40   as d40," +
raimund@3755:         "       sa.d50   as d50," +
raimund@3755:         "       sa.d60   as d60," +
raimund@3755:         "       sa.d70   as d70," +
raimund@3755:         "       sa.d75   as d75," +
raimund@3755:         "       sa.d80   as d80," +
raimund@3755:         "       sa.d84   as d84," +
raimund@3755:         "       sa.d90   as d90," +
raimund@3755:         "       sa.dmin  as dmin," +
raimund@3755:         "       sa.dmax  as dmax " +
raimund@3755:         "FROM sohltest st" +
raimund@3755:         "    JOIN station sn ON sn.stationid = st.stationid" +
raimund@3755:         "    JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid" +
raimund@3755:         "    JOIN sohlprobe sp ON sp.sohltestid = st.sohltestid" +
raimund@3755:         "    JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid" +
raimund@3755:         "WHERE gw.name = :name AND " +
raimund@3755:         "      st.km IS NOT NULL AND " +
raimund@3755:         "      st.km BETWEEN :from - 0.001 AND :to + 0.001 AND" +
raimund@3755:         "      st.datum BETWEEN :start AND :end";
raimund@3755: 
raimund@3755:     public static final class QualityMeasurementResultTransformer
raimund@3755:     extends                   BasicTransformerAdapter
raimund@3755:     {
raimund@3755:         public static QualityMeasurementResultTransformer INSTANCE =
raimund@3755:             new QualityMeasurementResultTransformer();
raimund@3755: 
raimund@3755:         public QualityMeasurementResultTransformer() {
raimund@3755:         }
raimund@3755: 
raimund@3755:         @Override
raimund@3755:         public Object transformTuple(Object [] tuple, String [] aliases) {
raimund@3755:             Map<String, Double> map = new HashMap<String, Double>();
raimund@3755:             double km = 0;
raimund@3755:             Date d = null;
raimund@3755:             for (int i = 0; i < tuple.length; ++i) {
raimund@3755:                 if (tuple[i] != null) {
raimund@3755:                     if (aliases[i].equals("km")) {
raimund@3755:                         km = ((Number)tuple[i]).doubleValue();
raimund@3755:                     }
raimund@3755:                     else if (aliases[i].equals("date")) {
raimund@3755:                         d = (Date)tuple[i];
raimund@3755:                     }
raimund@3755:                     else {
raimund@3755:                         map.put(aliases[i], (Double)tuple[i]);
raimund@3755:                     }
raimund@3755:                 }
raimund@3755:             }
raimund@3755:             return new QualityMeasurement(km, d, 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@3755:         Date end
raimund@3755:     ) {
raimund@3755:         SQLQuery query = session.createSQLQuery(SQL_MEASUREMENT)
raimund@3755:             .addScalar("km",    StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("date",  StandardBasicTypes.DATE)
raimund@3755:             .addScalar("d10",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d16",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d20",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d25",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d30",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d40",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d50",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d60",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d70",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d75",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d80",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d84",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("d90",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("dmin",  StandardBasicTypes.DOUBLE)
raimund@3755:             .addScalar("dmax",  StandardBasicTypes.DOUBLE);
raimund@3755: 
raimund@3755:         query.setString("name", river);
raimund@3755:         query.setDouble("from", from);
raimund@3755:         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@3755:     public static QualityMeasurements getMeasurements(
raimund@3755:         String river,
raimund@3755:         double from,
raimund@3755:         double to,
raimund@3755:         Date start,
raimund@3755:         Date end
raimund@3755:     ) {
raimund@3755:         Session session = SedDBSessionHolder.HOLDER.get();
raimund@3755:         try {
raimund@3755:             return load(session, river, from, to, start, end);
raimund@3755:         }
raimund@3755:         finally {
raimund@3755:             session.close();
raimund@3755:         }
raimund@3755:     }
raimund@3755: }