# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1325526325 0
# Node ID aad1886ea226f202f3e24fa7e7bf21dfb82eac72
# Parent  ee0c60757a9487dd75195a398acb43cd6e62944c
Store new gauges in FLYS db.

flys-aft/trunk@3568 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r ee0c60757a94 -r aad1886ea226 flys-aft/ChangeLog
--- a/flys-aft/ChangeLog	Mon Jan 02 15:13:58 2012 +0000
+++ b/flys-aft/ChangeLog	Mon Jan 02 17:45:25 2012 +0000
@@ -1,3 +1,20 @@
+2012-01-02	Sascha L. Teichmann	<sascha.teichmann@inteavtion.de>
+
+	* src/main/resources/sql/flys-common.properties: Added statements
+	  to create gauges in FLYS.
+
+	* src/main/java/de/intevation/aft/DIPSGauge.java: Make more fields
+	  accessible for gauge creation in FLYS.
+
+	* src/main/java/de/intevation/aft/River.java: Store new gauges
+	  in FLYS.
+
+	* src/main/java/de/intevation/db/ConnectedStatements.java:
+	  Added logging, make methods of transaction handling public.
+
+	* src/main/java/de/intevation/db/SymbolicStatement.java(setDouble):
+	  Fixed argument type problem.
+
 2012-01-02	Sascha L. Teichmann	<sascha.teichmann@inteavtion.de>
 
 	* src/main/java/de/intevation/db/ConnectionBuilder.java:
diff -r ee0c60757a94 -r aad1886ea226 flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java
--- a/flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java	Mon Jan 02 15:13:58 2012 +0000
+++ b/flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java	Mon Jan 02 17:45:25 2012 +0000
@@ -61,10 +61,28 @@
 
             date = cal.getTime();
         }
+
+        public double getValue() {
+            return value;
+        }
+
+        public void setValue(double value) {
+            this.value = value;
+        }
+
+        public Date getDate() {
+            return date;
+        }
+
+        public void setDate(Date date) {
+            this.date = date;
+        }
     } // class datum
 
     protected double aeo;
 
+    protected double station;
+
     protected String name;
 
     protected String riverName;
@@ -80,8 +98,8 @@
 
     public DIPSGauge(Element element) {
 
-        name      = element.getAttribute("NAME");
-        riverName = element.getAttribute("GEWAESSER");
+        name          = element.getAttribute("NAME");
+        riverName     = element.getAttribute("GEWAESSER");
 
         String aeoString = element.getAttribute("EINZUGSGEBIET_AEO");
         if (aeoString.length() == 0) {
@@ -90,6 +108,16 @@
         }
         aeo = Double.parseDouble(aeoString);
 
+        String stationString = element.getAttribute("STATIONIERUNG");
+        if (stationString.length() == 0) {
+            log.warn("DIPS: Setting station of gauge '" + name + "' to zero.");
+            stationString = "0";
+        }
+        station = Double.parseDouble(stationString);
+        if (station == 0d) {
+            log.warn("DIPS: Station of gauge '" + name + "' is zero.");
+        }
+
         datums = new ArrayList<Datum>();
         NodeList nodes = element.getElementsByTagName("PNP");
         for (int i = 0, N = nodes.getLength(); i < N; ++i) {
@@ -128,6 +156,28 @@
         this.aftName = aftName;
     }
 
+    public double getStation() {
+        return station;
+    }
 
+    public double getAeo() {
+        return aeo;
+    }
+
+    public void setAeo(double aeo) {
+        this.aeo = aeo;
+    }
+
+    public void setStation(double station) {
+        this.station = station;
+    }
+
+    public boolean hasDatums() {
+        return !datums.isEmpty();
+    }
+
+    public Datum getLatestDatum() {
+        return datums.get(datums.size()-1);
+    }
 }
 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
diff -r ee0c60757a94 -r aad1886ea226 flys-aft/src/main/java/de/intevation/aft/River.java
--- a/flys-aft/src/main/java/de/intevation/aft/River.java	Mon Jan 02 15:13:58 2012 +0000
+++ b/flys-aft/src/main/java/de/intevation/aft/River.java	Mon Jan 02 17:45:25 2012 +0000
@@ -11,6 +11,7 @@
 import org.apache.log4j.Logger;
 
 import de.intevation.db.ConnectedStatements;
+import de.intevation.db.SymbolicStatement;
 
 public class River
 extends      IdPair
@@ -108,11 +109,71 @@
             updateGauges.add(aftDIPSGauge);
         }
 
+        createGauges(context, aftDIPSGauges);
+
         gaugesRs.close();
+    }
 
-        for (DIPSGauge gauge: aftDIPSGauges.values()) {
+    protected void createGauges(
+        SyncContext          context,
+        Map<Long, DIPSGauge> gauges
+    )
+    throws SQLException
+    {
+        ConnectedStatements flysStatements = context.getFlysStatements();
+
+        SymbolicStatement.Instance nextId =
+            flysStatements.getStatement("next.gauge.id");
+
+        SymbolicStatement.Instance insertStmnt =
+            flysStatements.getStatement("insert.gauge");
+
+        for (Map.Entry<Long, DIPSGauge> entry: gauges.entrySet()) {
+            Long      officialNumber = entry.getKey();
+            DIPSGauge gauge          = entry.getValue();
+
             log.info("Gauge '" + gauge.getAftName() +
                 "' not in FLYS but in AFT/DIPS. -> Create");
+
+            if (!gauge.hasDatums()) {
+                log.warn("FLYS: Gauge '" + 
+                    gauge.getAftName() + "' has no datum. Ignored.");
+                continue;
+            }
+
+            ResultSet rs = null;
+            flysStatements.beginTransaction();
+            try {
+                (rs = nextId.executeQuery()).next();
+                int gaugeId = rs.getInt("gauge_id");
+                rs.close(); rs = null;
+
+                insertStmnt
+                    .clearParameters()
+                    .setInt("id", gaugeId)
+                    .setString("name", gauge.getAftName())
+                    .setInt("river_id", id1)
+                    .setDouble("station", gauge.getStation())
+                    .setDouble("aeo", gauge.getAeo())
+                    .setDouble("official_number", officialNumber)
+                    .setDouble("datum", gauge.getLatestDatum().getValue());
+
+                insertStmnt.execute();
+
+                log.info("FLYS: Created gauge '" + gauge.getAftName() + 
+                    "' with id " + gaugeId + ".");
+
+                flysStatements.commitTransaction();
+            }
+            catch (SQLException sqle) {
+                flysStatements.rollbackTransaction();
+                throw sqle;
+            }
+            finally {
+                if (rs != null) {
+                    rs.close();
+                }
+            }
         }
     }
 
diff -r ee0c60757a94 -r aad1886ea226 flys-aft/src/main/java/de/intevation/db/ConnectedStatements.java
--- a/flys-aft/src/main/java/de/intevation/db/ConnectedStatements.java	Mon Jan 02 15:13:58 2012 +0000
+++ b/flys-aft/src/main/java/de/intevation/db/ConnectedStatements.java	Mon Jan 02 17:45:25 2012 +0000
@@ -8,9 +8,14 @@
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.Savepoint;
+import java.sql.DatabaseMetaData;
+
+import org.apache.log4j.Logger;
 
 public class ConnectedStatements
 {
+    private static Logger log = Logger.getLogger(ConnectedStatements.class);
+
     protected Connection connection;
 
     protected Map<String, SymbolicStatement> statements;
@@ -33,9 +38,16 @@
     }
 
     protected void checkSavePoints() throws SQLException {
-        if (connection.getMetaData().supportsSavepoints()) {
+        DatabaseMetaData metaData = connection.getMetaData();
+        if (metaData.supportsSavepoints()) {
+            log.info("Driver '" + metaData.getDriverName() +
+                "' does support savepoints.");
             savepoints = new ArrayDeque<Savepoint>();
         }
+        else {
+            log.info("Driver '" + metaData.getDriverName() + 
+                "' does not support savepoints.");
+        }
     }
 
     public SymbolicStatement.Instance getStatement(String key) 
@@ -56,20 +68,20 @@
         return stmnt;
     }
 
-    void beginTransaction() throws SQLException {
+    public void beginTransaction() throws SQLException {
         if (savepoints != null) {
             savepoints.push(connection.setSavepoint());
         }
     }
 
-    void commitTransaction() throws SQLException {
+    public void commitTransaction() throws SQLException {
         if (savepoints != null) {
             savepoints.pop();
         }
         connection.commit();
     }
 
-    void rollbackTransaction() throws SQLException {
+    public void rollbackTransaction() throws SQLException {
         if (savepoints != null) {
             Savepoint savepoint = savepoints.pop();
             connection.rollback(savepoint);
diff -r ee0c60757a94 -r aad1886ea226 flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java
--- a/flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java	Mon Jan 02 15:13:58 2012 +0000
+++ b/flys-aft/src/main/java/de/intevation/db/SymbolicStatement.java	Mon Jan 02 17:45:25 2012 +0000
@@ -94,7 +94,7 @@
             return this;
         }
 
-        public Instance setDouble(String key, int value)
+        public Instance setDouble(String key, double value)
         throws SQLException 
         {
             List<Integer> pos = positions.get(key.toLowerCase());
diff -r ee0c60757a94 -r aad1886ea226 flys-aft/src/main/resources/sql/flys-common.properties
--- a/flys-aft/src/main/resources/sql/flys-common.properties	Mon Jan 02 15:13:58 2012 +0000
+++ b/flys-aft/src/main/resources/sql/flys-common.properties	Mon Jan 02 17:45:25 2012 +0000
@@ -1,2 +1,5 @@
 select.river = SELECT id, name FROM rivers 
 select.gauges = SELECT id, name, official_number FROM gauges WHERE river_id = :river_id
+next.gauge.id = SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id
+insert.gauge = INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \
+                      VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum)