Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java @ 7698:7efeaa2058e1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQ Relation: Added CSV dumping to raw SedDB query results. If system property 'minfo.dump.sq.seddb.prefix' is set files are generate from this to contain the results of the raw SQL queries.
author Sascha L. Teichmann <teichmann@intevation.de>
date Mon, 16 Dec 2013 16:39:12 +0100
parents 90a3bcd8060a
children a877d2680a4a
comparison
equal deleted inserted replaced
7697:640342d9ab8a 7698:7efeaa2058e1
6 * documentation coming with Dive4Elements River for details. 6 * documentation coming with Dive4Elements River for details.
7 */ 7 */
8 8
9 package org.dive4elements.river.artifacts.model.sq; 9 package org.dive4elements.river.artifacts.model.sq;
10 10
11 import java.io.FileWriter;
12 import java.io.IOException;
13 import java.text.SimpleDateFormat;
11 import java.util.ArrayList; 14 import java.util.ArrayList;
12 import java.util.Calendar; 15 import java.util.Calendar;
13 import java.util.Collections; 16 import java.util.Collections;
14 import java.util.Date; 17 import java.util.Date;
15 import java.util.HashMap; 18 import java.util.HashMap;
28 31
29 import org.dive4elements.river.artifacts.model.DateRange; 32 import org.dive4elements.river.artifacts.model.DateRange;
30 33
31 import org.dive4elements.river.backend.SedDBSessionHolder; 34 import org.dive4elements.river.backend.SedDBSessionHolder;
32 35
36 import au.com.bytecode.opencsv.CSVWriter;
37
33 public class MeasurementFactory 38 public class MeasurementFactory
34 { 39 {
35 private static final Logger log = 40 private static final Logger log =
36 Logger.getLogger(MeasurementFactory.class); 41 Logger.getLogger(MeasurementFactory.class);
42
43 public static final String MINFO_DUMP_SQ_SEDDB_PREFIX = "minfo.dump.sq.seddb.prefix";
37 44
38 public static final String SQL_TOTALS = 45 public static final String SQL_TOTALS =
39 "SELECT " + 46 "SELECT " +
40 "m.Q_BPEGEL AS Q_BPEGEL,"+ 47 "m.Q_BPEGEL AS Q_BPEGEL,"+
41 "m.TSCHWEB AS TSCHWEB," + 48 "m.TSCHWEB AS TSCHWEB," +
111 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" + 118 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" +
112 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) >= 0 " + 119 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) >= 0 " +
113 "ORDER BY " + 120 "ORDER BY " +
114 "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR"; 121 "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
115 122
116 public static final BasicTransformerAdapter TOTALS_TRANSFORMER =
117 new BasicTransformerAdapter() {
118 private static final long serialVersionUID = 1L;
119
120 @Override
121 public Object transformTuple(Object [] tuple, String [] aliases) {
122 Map<String, Object> map = new HashMap<String, Object>();
123 for (int i = 0; i < tuple.length; ++i) {
124 Object value = tuple[i];
125 if (value != null) {
126 map.put(aliases[i], value);
127 }
128 }
129 return new Measurement(map, Collections.<Sieve>emptyList());
130 }
131 };
132
133 private static final int index(String s) { 123 private static final int index(String s) {
134 return Integer.parseInt(s.substring(s.length()-2))-1; 124 return Integer.parseInt(s.substring(s.length()-2))-1;
135 } 125 }
136 126
137 public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER = 127 public abstract static class CSVTransformer extends BasicTransformerAdapter {
138 new BasicTransformerAdapter() { 128 private static final long serialVersionUID = 1L;
139 private static final long serialVersionUID = 1L; 129
140 130 private CSVWriter rawWriter;
141 @Override 131 private boolean metaDataWritten;
142 public Object transformTuple(Object [] tuple, String [] aliases) { 132
143 Map<String, Object> map = new HashMap<String, Object>(); 133 public CSVTransformer() {
144 134 this(null);
145 Sieve [] sieves = new Sieve[21]; 135 }
146 136
147 List<Sieve> validSieves = new ArrayList<Sieve>(21); 137 public CSVTransformer(CSVWriter rawWriter) {
148 138 this.rawWriter = rawWriter;
149 for (int i = 0; i < tuple.length; ++i) { 139 }
150 Object value = tuple[i]; 140
151 if (value == null) { 141 protected void writeRaw(Object [] tuple, String [] aliases) {
152 continue; 142 if (rawWriter == null) {
143 return;
144 }
145
146 if (!metaDataWritten) {
147 rawWriter.writeNext(aliases);
148 metaDataWritten = true;
149 }
150
151 String [] nextLine = new String[tuple.length];
152 for (int i = 0; i < tuple.length; ++i) {
153 nextLine[i] = tuple[i] != null ? tuple[i].toString() : "";
154 }
155 rawWriter.writeNext(nextLine);
156 }
157 }
158
159 public static class FractionsTransformer extends CSVTransformer {
160
161 private static final long serialVersionUID = 1L;
162
163 public FractionsTransformer() {
164 this(null);
165 }
166
167 public FractionsTransformer(CSVWriter rawWriter) {
168 super(rawWriter);
169 }
170
171 @Override
172 public Object transformTuple(Object [] tuple, String [] aliases) {
173
174 writeRaw(tuple, aliases);
175
176 Map<String, Object> map = new HashMap<String, Object>();
177
178 Sieve [] sieves = new Sieve[21];
179
180 List<Sieve> validSieves = new ArrayList<Sieve>(21);
181
182 for (int i = 0; i < tuple.length; ++i) {
183 Object value = tuple[i];
184 if (value == null) {
185 continue;
186 }
187 String alias = aliases[i];
188 if (alias.startsWith("SIEB")
189 || alias.startsWith("RSIEB")) {
190 int idx = index(alias);
191 Sieve s = sieves[idx];
192 double v = (Double)value;
193 if (s == null) {
194 s = new Sieve();
195 sieves[idx] = s;
153 } 196 }
154 String alias = aliases[i]; 197 if (alias.startsWith("SIEB")) {
155 if (alias.startsWith("SIEB") 198 s.setDiameter(v);
156 || alias.startsWith("RSIEB")) {
157 int idx = index(alias);
158 Sieve s = sieves[idx];
159 double v = (Double)value;
160 if (s == null) {
161 s = new Sieve();
162 sieves[idx] = s;
163 }
164 if (alias.startsWith("SIEB")) {
165 s.setDiameter(v);
166 }
167 else {
168 s.setLoad(v);
169 }
170 }
171 else if (alias.equals("REST")) {
172 Sieve s = new Sieve(0d, (Double)value);
173 validSieves.add(s);
174 } 199 }
175 else { 200 else {
176 map.put(alias, value); 201 s.setLoad(v);
177 } 202 }
178 203 }
179 } 204 else if (alias.equals("REST")) {
180 for (Sieve s: sieves) { 205 Sieve s = new Sieve(0d, (Double)value);
181 if (s != null) { 206 validSieves.add(s);
182 validSieves.add(s); 207 }
183 } 208 else {
184 } 209 map.put(alias, value);
185 return new Measurement(map, validSieves); 210 }
186 } 211
187 }; 212 }
213 for (Sieve s: sieves) {
214 if (s != null) {
215 validSieves.add(s);
216 }
217 }
218 return new Measurement(map, validSieves);
219 }
220 } // class FractionsTransformer
221
222 public static class TotalsTransformer extends CSVTransformer {
223
224 private static final long serialVersionUID = 1L;
225
226 public TotalsTransformer() {
227 this(null);
228 }
229
230 public TotalsTransformer(CSVWriter rawWriter) {
231 super(rawWriter);
232 }
233
234 @Override
235 public Object transformTuple(Object [] tuple, String [] aliases) {
236
237 writeRaw(tuple, aliases);
238
239 Map<String, Object> map = new HashMap<String, Object>();
240 for (int i = 0; i < tuple.length; ++i) {
241 Object value = tuple[i];
242 if (value != null) {
243 map.put(aliases[i], value);
244 }
245 }
246 return new Measurement(map, Collections.<Sieve>emptyList());
247 }
248 } // class TotalsTransformer
188 249
189 private MeasurementFactory() { 250 private MeasurementFactory() {
190 } 251 }
191 252
192 public static Measurements getMeasurements( 253 public static Measurements getMeasurements(
208 finally { 269 finally {
209 session.close(); 270 session.close();
210 } 271 }
211 } 272 }
212 273
274 @SuppressWarnings("unchecked")
213 protected static List<Measurement> loadTotals( 275 protected static List<Measurement> loadTotals(
214 Session session, 276 Session session,
215 String river, 277 String river,
216 double location, 278 double location,
217 DateRange dateRange 279 DateRange dateRange
224 query.setString("river_name", river); 286 query.setString("river_name", river);
225 query.setDouble("location", location); 287 query.setDouble("location", location);
226 query.setDate("from", dateRange.getFrom()); 288 query.setDate("from", dateRange.getFrom());
227 query.setDate("to", dateRange.getTo()); 289 query.setDate("to", dateRange.getTo());
228 290
229 query.setResultTransformer(TOTALS_TRANSFORMER); 291 CSVWriter csvWriter =
230 292 getCVSWriter("totals", river, location, dateRange);
231 @SuppressWarnings("unchecked") 293
232 List<Measurement> result = (List<Measurement>)query.list(); 294 try {
233 return result; 295 TotalsTransformer totalTransformer = new TotalsTransformer(csvWriter);
234 } 296 query.setResultTransformer(totalTransformer);
235 297
298 return (List<Measurement>)query.list();
299 }
300 finally {
301 closeGraceful(csvWriter);
302 }
303 }
304
305 private static CSVWriter getCVSWriter(
306 String type,
307 String river,
308 double location,
309 DateRange dateRange
310 ) {
311 String dumpPrefix = System.getProperty(MINFO_DUMP_SQ_SEDDB_PREFIX);
312 if (dumpPrefix == null) {
313 return null;
314 }
315
316 SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-ddd");
317 StringBuilder sb = new StringBuilder(dumpPrefix);
318 Date from = dateRange.getFrom();
319 Date to = dateRange.getTo();
320
321 sb.append(type)
322 .append('-').append(df.format(new Date()))
323 .append('-').append(river)
324 .append('-').append(location)
325 .append('-').append(from != null ? df.format(from) : "")
326 .append('-').append(to != null ? df.format(to) : "")
327 .append(".csv");
328
329 String fileName = sb.toString();
330
331 try {
332 return new CSVWriter(new FileWriter(fileName), ';');
333 }
334 catch (IOException ioe) {
335 log.error("Cannot open '" + fileName + "' for writing.", ioe);
336 }
337
338 return null;
339 }
340
341 private static void closeGraceful(CSVWriter writer) {
342 if (writer != null) {
343 try {
344 writer.flush();
345 writer.close();
346 }
347 catch (IOException ioe) {
348 log.error(ioe);
349 }
350 }
351 }
352
353 @SuppressWarnings("unchecked")
236 protected static List<Measurement> loadFractions( 354 protected static List<Measurement> loadFractions(
237 Session session, 355 Session session,
238 String river, 356 String river,
239 double location, 357 double location,
240 DateRange dateRange 358 DateRange dateRange
299 query.setString("river_name", river); 417 query.setString("river_name", river);
300 query.setDouble("location", location); 418 query.setDouble("location", location);
301 query.setDate("from", dateRange.getFrom()); 419 query.setDate("from", dateRange.getFrom());
302 query.setDate("to", dateRange.getTo()); 420 query.setDate("to", dateRange.getTo());
303 421
304 query.setResultTransformer(FRACTIONS_TRANSFORMER); 422 List<Measurement> measuments;
305 423
306 @SuppressWarnings("unchecked") 424 CSVWriter csvWriter =
307 List<Measurement> measuments = (List<Measurement>)query.list(); 425 getCVSWriter("fractions", river, location, dateRange);
426
427 try {
428 FractionsTransformer fractionsTransformer =
429 new FractionsTransformer(csvWriter);
430
431 query.setResultTransformer(fractionsTransformer);
432
433 measuments = (List<Measurement>)query.list();
434 }
435 finally {
436 closeGraceful(csvWriter);
437 }
308 438
309 if (debug) { 439 if (debug) {
310 log.debug("num fraction results: " + measuments.size()); 440 log.debug("num fraction results: " + measuments.size());
311 } 441 }
312 442
http://dive4elements.wald.intevation.org