# HG changeset patch
# User Sascha L. Teichmann <teichmann@intevation.de>
# Date 1387208352 -3600
# Node ID 7efeaa2058e1f9d1bc48523fd237052497a74fc4
# Parent  640342d9ab8a1ee130558ba156c1a760db24a497
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.

diff -r 640342d9ab8a -r 7efeaa2058e1 artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java	Mon Dec 16 16:24:07 2013 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java	Mon Dec 16 16:39:12 2013 +0100
@@ -8,6 +8,9 @@
 
 package org.dive4elements.river.artifacts.model.sq;
 
+import java.io.FileWriter;
+import java.io.IOException;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Collections;
@@ -30,11 +33,15 @@
 
 import org.dive4elements.river.backend.SedDBSessionHolder;
 
+import au.com.bytecode.opencsv.CSVWriter;
+
 public class MeasurementFactory
 {
     private static final Logger log =
         Logger.getLogger(MeasurementFactory.class);
 
+    public static final String MINFO_DUMP_SQ_SEDDB_PREFIX = "minfo.dump.sq.seddb.prefix";
+
     public static final String SQL_TOTALS =
         "SELECT " +
             "m.Q_BPEGEL AS Q_BPEGEL,"+
@@ -113,78 +120,132 @@
         "ORDER BY " +
             "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
 
-    public static final BasicTransformerAdapter TOTALS_TRANSFORMER =
-        new BasicTransformerAdapter() {
-            private static final long serialVersionUID = 1L;
-
-            @Override
-            public Object transformTuple(Object [] tuple, String [] aliases) {
-                Map<String, Object> map = new HashMap<String, Object>();
-                for (int i = 0; i < tuple.length; ++i) {
-                    Object value = tuple[i];
-                    if (value != null) {
-                        map.put(aliases[i], value);
-                    }
-                }
-                return new Measurement(map, Collections.<Sieve>emptyList());
-            }
-        };
-
     private static final int index(String s) {
         return Integer.parseInt(s.substring(s.length()-2))-1;
     }
 
-    public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER =
-        new BasicTransformerAdapter() {
-            private static final long serialVersionUID = 1L;
-
-            @Override
-            public Object transformTuple(Object [] tuple, String [] aliases) {
-                Map<String, Object> map = new HashMap<String, Object>();
+    public abstract static class CSVTransformer extends BasicTransformerAdapter {
+        private static final long serialVersionUID = 1L;
 
-                Sieve [] sieves = new Sieve[21];
-
-                List<Sieve> validSieves = new ArrayList<Sieve>(21);
+        private CSVWriter rawWriter;
+        private boolean   metaDataWritten;
 
-                for (int i = 0; i < tuple.length; ++i) {
-                    Object value = tuple[i];
-                    if (value == null) {
-                        continue;
+        public CSVTransformer() {
+            this(null);
+        }
+
+        public CSVTransformer(CSVWriter rawWriter) {
+            this.rawWriter = rawWriter;
+        }
+
+        protected void writeRaw(Object [] tuple, String [] aliases) {
+            if (rawWriter == null) {
+                return;
+            }
+
+            if (!metaDataWritten) {
+                rawWriter.writeNext(aliases);
+                metaDataWritten = true;
+            }
+
+            String [] nextLine = new String[tuple.length];
+            for (int i = 0; i < tuple.length; ++i) {
+                nextLine[i] = tuple[i] != null ? tuple[i].toString() : "";
+            }
+            rawWriter.writeNext(nextLine);
+        }
+    }
+
+    public static class FractionsTransformer extends CSVTransformer {
+
+        private static final long serialVersionUID = 1L;
+
+        public FractionsTransformer() {
+            this(null);
+        }
+
+        public FractionsTransformer(CSVWriter rawWriter) {
+            super(rawWriter);
+        }
+
+        @Override
+        public Object transformTuple(Object [] tuple, String [] aliases) {
+
+            writeRaw(tuple, aliases);
+
+            Map<String, Object> map = new HashMap<String, Object>();
+
+            Sieve [] sieves = new Sieve[21];
+
+            List<Sieve> validSieves = new ArrayList<Sieve>(21);
+
+            for (int i = 0; i < tuple.length; ++i) {
+                Object value = tuple[i];
+                if (value == null) {
+                    continue;
+                }
+                String alias = aliases[i];
+                if (alias.startsWith("SIEB")
+                ||  alias.startsWith("RSIEB")) {
+                    int idx = index(alias);
+                    Sieve s = sieves[idx];
+                    double v = (Double)value;
+                    if (s == null) {
+                        s = new Sieve();
+                        sieves[idx] = s;
                     }
-                    String alias = aliases[i];
-                    if (alias.startsWith("SIEB")
-                    ||  alias.startsWith("RSIEB")) {
-                        int idx = index(alias);
-                        Sieve s = sieves[idx];
-                        double v = (Double)value;
-                        if (s == null) {
-                            s = new Sieve();
-                            sieves[idx] = s;
-                        }
-                        if (alias.startsWith("SIEB")) {
-                            s.setDiameter(v);
-                        }
-                        else {
-                            s.setLoad(v);
-                        }
-                    }
-                    else if (alias.equals("REST")) {
-                        Sieve s = new Sieve(0d, (Double)value);
-                        validSieves.add(s);
+                    if (alias.startsWith("SIEB")) {
+                        s.setDiameter(v);
                     }
                     else {
-                        map.put(alias, value);
-                    }
-
-                }
-                for (Sieve s: sieves) {
-                    if (s != null) {
-                        validSieves.add(s);
+                        s.setLoad(v);
                     }
                 }
-                return new Measurement(map, validSieves);
+                else if (alias.equals("REST")) {
+                    Sieve s = new Sieve(0d, (Double)value);
+                    validSieves.add(s);
+                }
+                else {
+                    map.put(alias, value);
+                }
+
             }
-        };
+            for (Sieve s: sieves) {
+                if (s != null) {
+                    validSieves.add(s);
+                }
+            }
+            return new Measurement(map, validSieves);
+        }
+    } // class FractionsTransformer
+
+    public static class TotalsTransformer extends CSVTransformer {
+
+        private static final long serialVersionUID = 1L;
+
+        public TotalsTransformer() {
+            this(null);
+        }
+
+        public TotalsTransformer(CSVWriter rawWriter) {
+            super(rawWriter);
+        }
+
+        @Override
+        public Object transformTuple(Object [] tuple, String [] aliases) {
+
+            writeRaw(tuple, aliases);
+
+            Map<String, Object> map = new HashMap<String, Object>();
+            for (int i = 0; i < tuple.length; ++i) {
+                Object value = tuple[i];
+                if (value != null) {
+                    map.put(aliases[i], value);
+                }
+            }
+            return new Measurement(map, Collections.<Sieve>emptyList());
+        }
+    } // class TotalsTransformer
 
     private MeasurementFactory() {
     }
@@ -210,6 +271,7 @@
         }
     }
 
+    @SuppressWarnings("unchecked")
     protected static List<Measurement> loadTotals(
         Session   session,
         String    river,
@@ -226,13 +288,69 @@
         query.setDate("from", dateRange.getFrom());
         query.setDate("to", dateRange.getTo());
 
-        query.setResultTransformer(TOTALS_TRANSFORMER);
+        CSVWriter csvWriter =
+            getCVSWriter("totals", river, location, dateRange);
 
-        @SuppressWarnings("unchecked")
-        List<Measurement> result = (List<Measurement>)query.list();
-        return result;
+        try {
+            TotalsTransformer totalTransformer = new TotalsTransformer(csvWriter);
+            query.setResultTransformer(totalTransformer);
+
+            return (List<Measurement>)query.list();
+        }
+        finally {
+            closeGraceful(csvWriter);
+        }
     }
 
+    private static CSVWriter getCVSWriter(
+        String    type,
+        String    river,
+        double    location,
+        DateRange dateRange
+    ) {
+        String dumpPrefix = System.getProperty(MINFO_DUMP_SQ_SEDDB_PREFIX);
+        if (dumpPrefix == null) {
+            return null;
+        }
+
+        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-ddd");
+        StringBuilder sb = new StringBuilder(dumpPrefix);
+        Date from = dateRange.getFrom();
+        Date to   = dateRange.getTo();
+
+        sb.append(type)
+          .append('-').append(df.format(new Date()))
+          .append('-').append(river)
+          .append('-').append(location)
+          .append('-').append(from != null ? df.format(from) : "")
+          .append('-').append(to != null ? df.format(to) : "")
+          .append(".csv");
+
+        String fileName = sb.toString();
+
+        try {
+            return new CSVWriter(new FileWriter(fileName), ';');
+        }
+        catch (IOException ioe) {
+            log.error("Cannot open '" + fileName + "' for writing.", ioe);
+        }
+
+        return null;
+    }
+
+    private static void closeGraceful(CSVWriter writer) {
+        if (writer != null) {
+            try {
+                writer.flush();
+                writer.close();
+            }
+            catch (IOException ioe) {
+                log.error(ioe);
+            }
+        }
+    }
+
+    @SuppressWarnings("unchecked")
     protected static List<Measurement> loadFractions(
         Session   session,
         String    river,
@@ -301,10 +419,22 @@
         query.setDate("from", dateRange.getFrom());
         query.setDate("to", dateRange.getTo());
 
-        query.setResultTransformer(FRACTIONS_TRANSFORMER);
+        List<Measurement> measuments;
 
-        @SuppressWarnings("unchecked")
-        List<Measurement> measuments = (List<Measurement>)query.list();
+        CSVWriter csvWriter =
+            getCVSWriter("fractions", river, location, dateRange);
+
+        try {
+            FractionsTransformer fractionsTransformer =
+                new FractionsTransformer(csvWriter);
+
+            query.setResultTransformer(fractionsTransformer);
+
+            measuments = (List<Measurement>)query.list();
+        }
+        finally {
+            closeGraceful(csvWriter);
+        }
 
         if (debug) {
             log.debug("num fraction results: " + measuments.size());