sascha@3289: package de.intevation.flys.artifacts.model.sq;
sascha@3289: 
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@3292:     public static final String [] GSIEBSATZ = {
sascha@3292:         "SIEB01", "SIEB02", "SIEB03", "SIEB04",
sascha@3292:         "SIEB05", "SIEB06", "SIEB07", "SIEB08",
sascha@3292:         "SIEB09", "SIEB10", "SIEB11", "SIEB13",
sascha@3292:         "SIEB13", "SIEB14", "SIEB15", "SIEB16",
sascha@3292:         "SIEB17", "SIEB18", "SIEB19", "SIEB20",
sascha@3292:         "SIEB21"
sascha@3292:     };
sascha@3292: 
sascha@3292:     public static final String [] SSIEBUNG = {
sascha@3292:         "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04",
sascha@3292:         "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08",
sascha@3292:         "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13",
sascha@3292:         "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16",
sascha@3292:         "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20",
sascha@3292:         "RSIEB21", "REST"
sascha@3292:     };
sascha@3292: 
sascha@3289:     public static final String SQL_MEASSURE =
sascha@3289:         "SELECT " +
sascha@3289:             "m.TSAND   AS TSAND,"   +
sascha@3289:             "m.TSCHWEB AS TSCHWEB," +
sascha@3292:             "m.CSCHWEB AS CSCHWEB," +
sascha@3292:             "m.Q       AS Q," +
sascha@3292:             "%GSIEBSATZ%" +
sascha@3292:             "%SSIEBUNG%" +
sascha@3289:         "FROM messung m " +
sascha@3289:             "JOIN station   s ON m.stationid   = s.stationid " +
sascha@3289:             "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " +
sascha@3292:             "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gewaesserid " +
sascha@3292:             "LEFT JOIN SSIEBUNG  ss ON gs.gsiebsatzid = ss.gewaesserid " +
sascha@3289:             "WHERE " +
sascha@3289:                 "g.name = :river_name AND " +
sascha@3289:                 "s.km BETWEEN :location - 0.001 AND :location + 0.001 AND " +
sascha@3290:                 "m.datum BETWEEN :from AND :to AND " +
sascha@3292:                 "m.Q IS NOT NULL"
sascha@3292:         .replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ))
sascha@3292:         .replace("%SSIEBUNG%",  projection("ss", SSIEBUNG));
sascha@3292: 
sascha@3292:     private static final String projection(
sascha@3292:         String    prefix,
sascha@3292:         String [] columnNames
sascha@3292:     ) {
sascha@3292:         StringBuilder sb = new StringBuilder();
sascha@3292:         for (String columnName: columnNames) {
sascha@3292:             sb.append(prefix)
sascha@3292:               .append('.')
sascha@3292:               .append(columnName)
sascha@3292:               .append(" AS ")
sascha@3292:               .append(columnName)
sascha@3292:               .append(',');
sascha@3292:         }
sascha@3292:         return sb.toString();
sascha@3292:     }
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: 
sascha@3289:         @Override
sascha@3289:         public Object transformTuple(Object [] tuple, String [] aliases) {
sascha@3289:             Map<String, Object> map = new HashMap<String, Object>();
sascha@3289:             for (int i = 0; i < tuple.length; ++i) {
sascha@3291:                 if (tuple[i] != null) {
sascha@3291:                     map.put(aliases[i], tuple[i]);
sascha@3291:                 }
sascha@3289:             }
sascha@3289:             return new Measurement(map);
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@3289:         SQLQuery query = session.createSQLQuery(SQL_MEASSURE)
sascha@3289:             .addScalar("TSAND",   StandardBasicTypes.DOUBLE)
sascha@3289:             .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
sascha@3290:             .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE)
sascha@3290:             .addScalar("Q",       StandardBasicTypes.DOUBLE);
sascha@3289: 
sascha@3292:         for (String siebsatz: GSIEBSATZ) {
sascha@3292:             query.addScalar(siebsatz, StandardBasicTypes.DOUBLE);
sascha@3292:         }
sascha@3292: 
sascha@3292:         for (String siebung: SSIEBUNG) {
sascha@3292:             query.addScalar(siebung, StandardBasicTypes.DOUBLE);
sascha@3292:         }
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@3290:         return new Measurements(query.list());
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 :