Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3292:e06036fdf0c7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQ relation: add left joins to GSIEBSATZ and SSIEBUNG flys-artifacts/trunk@4960 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 12 Jul 2012 22:01:52 +0000
parents b52c4b34ec1b
children 5ccd51ca11ce
comparison
equal deleted inserted replaced
3291:b52c4b34ec1b 3292:e06036fdf0c7
19 public class MeasurementFactory 19 public class MeasurementFactory
20 { 20 {
21 private static final Logger log = 21 private static final Logger log =
22 Logger.getLogger(MeasurementFactory.class); 22 Logger.getLogger(MeasurementFactory.class);
23 23
24 public static final String [] GSIEBSATZ = {
25 "SIEB01", "SIEB02", "SIEB03", "SIEB04",
26 "SIEB05", "SIEB06", "SIEB07", "SIEB08",
27 "SIEB09", "SIEB10", "SIEB11", "SIEB13",
28 "SIEB13", "SIEB14", "SIEB15", "SIEB16",
29 "SIEB17", "SIEB18", "SIEB19", "SIEB20",
30 "SIEB21"
31 };
32
33 public static final String [] SSIEBUNG = {
34 "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04",
35 "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08",
36 "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13",
37 "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16",
38 "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20",
39 "RSIEB21", "REST"
40 };
41
24 public static final String SQL_MEASSURE = 42 public static final String SQL_MEASSURE =
25 "SELECT " + 43 "SELECT " +
26 "m.TSAND AS TSAND," + 44 "m.TSAND AS TSAND," +
27 "m.TSCHWEB AS TSCHWEB," + 45 "m.TSCHWEB AS TSCHWEB," +
28 "m.CSCHWEB AS CSCHWEB " + 46 "m.CSCHWEB AS CSCHWEB," +
29 "m.Q AS Q " + 47 "m.Q AS Q," +
30 /* TODO: Select more */ 48 "%GSIEBSATZ%" +
49 "%SSIEBUNG%" +
31 "FROM messung m " + 50 "FROM messung m " +
32 "JOIN station s ON m.stationid = s.stationid " + 51 "JOIN station s ON m.stationid = s.stationid " +
33 "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " + 52 "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " +
53 "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gewaesserid " +
54 "LEFT JOIN SSIEBUNG ss ON gs.gsiebsatzid = ss.gewaesserid " +
34 "WHERE " + 55 "WHERE " +
35 "g.name = :river_name AND " + 56 "g.name = :river_name AND " +
36 "s.km BETWEEN :location - 0.001 AND :location + 0.001 AND " + 57 "s.km BETWEEN :location - 0.001 AND :location + 0.001 AND " +
37 "m.datum BETWEEN :from AND :to AND " + 58 "m.datum BETWEEN :from AND :to AND " +
38 "m.Q IS NOT NULL"; 59 "m.Q IS NOT NULL"
60 .replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ))
61 .replace("%SSIEBUNG%", projection("ss", SSIEBUNG));
62
63 private static final String projection(
64 String prefix,
65 String [] columnNames
66 ) {
67 StringBuilder sb = new StringBuilder();
68 for (String columnName: columnNames) {
69 sb.append(prefix)
70 .append('.')
71 .append(columnName)
72 .append(" AS ")
73 .append(columnName)
74 .append(',');
75 }
76 return sb.toString();
77 }
39 78
40 public static final class MeasurementResultTransformer 79 public static final class MeasurementResultTransformer
41 extends BasicTransformerAdapter 80 extends BasicTransformerAdapter
42 { 81 {
43 public static MeasurementResultTransformer INSTANCE = 82 public static MeasurementResultTransformer INSTANCE =
71 .addScalar("TSAND", StandardBasicTypes.DOUBLE) 110 .addScalar("TSAND", StandardBasicTypes.DOUBLE)
72 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) 111 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
73 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) 112 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE)
74 .addScalar("Q", StandardBasicTypes.DOUBLE); 113 .addScalar("Q", StandardBasicTypes.DOUBLE);
75 114
115 for (String siebsatz: GSIEBSATZ) {
116 query.addScalar(siebsatz, StandardBasicTypes.DOUBLE);
117 }
118
119 for (String siebung: SSIEBUNG) {
120 query.addScalar(siebung, StandardBasicTypes.DOUBLE);
121 }
122
76 query.setString("river_name", river); 123 query.setString("river_name", river);
77 query.setDouble("location", location); 124 query.setDouble("location", location);
78 query.setDate("from", dateRange.getFrom()); 125 query.setDate("from", dateRange.getFrom());
79 query.setDate("to", dateRange.getTo()); 126 query.setDate("to", dateRange.getTo());
80 127
http://dive4elements.wald.intevation.org