# HG changeset patch
# User Sascha L. Teichmann <teichmann@intevation.de>
# Date 1363112118 -3600
# Node ID da1e897c7224b465bee581e84123e25808037f06
# Parent  46e984a3d576c724a05c9fa855ba0b502666ac59
Set the BFG_ID for current discharge tables in FLYS.

diff -r 46e984a3d576 -r da1e897c7224 flys-aft/src/main/java/de/intevation/aft/River.java
--- a/flys-aft/src/main/java/de/intevation/aft/River.java	Mon Mar 11 12:55:09 2013 +0100
+++ b/flys-aft/src/main/java/de/intevation/aft/River.java	Tue Mar 12 19:15:18 2013 +0100
@@ -87,8 +87,8 @@
                 String num  = messstellenRs.getString("MESSSTELLE_NR");
                 double station = messstellenRs.getDouble("STATIONIERUNG");
 
-                if (!messstellenRs.wasNull() && station < from && station > to) {
-                    log.warn("Station found in AFT but in range: " + station);
+                if (!messstellenRs.wasNull() && !inside(station)) {
+                    log.warn("Station found in AFT but in not range: " + station);
                     continue;
                 }
 
@@ -171,12 +171,93 @@
         boolean modified = false;
 
         for (DIPSGauge gauge: gauges) {
+            modified |= updateBfGIdOnMasterDischargeTable(context, gauge);
             modified |= updateGauge(context, gauge);
         }
 
         return modified;
     }
 
+    protected boolean updateBfGIdOnMasterDischargeTable(
+        SyncContext context,
+        DIPSGauge   gauge
+    ) throws SQLException {
+        log.info(
+            "FLYS: Updating master discharge table bfg_id for '" +
+            gauge.getAftName() + "'");
+        ConnectedStatements flysStatements = context.getFlysStatements();
+
+        ResultSet rs = flysStatements
+            .getStatement("select.gauge.master.discharge.table")
+            .clearParameters()
+            .setInt("gauge_id", gauge.getFlysId())
+            .executeQuery();
+
+        int flysId;
+
+        try {
+            if (rs.next()) {
+                log.error(
+                    "FLYS: No master discharge table found for gauge '" +
+                    gauge.getAftName() + "'");
+                return false;
+            }
+            String bfgId = rs.getString("bfg_id");
+            if (!rs.wasNull()) { // already has BFG_ID
+                return false;
+            }
+            flysId = rs.getInt("id");
+        } finally {
+            rs.close();
+        }
+
+        // We need to find out the BFG_ID of the current discharge table
+        // for this gauge in AFT.
+
+        ConnectedStatements aftStatements = context.getAftStatements();
+
+        rs = aftStatements
+            .getStatement("select.bfg.id.current")
+            .clearParameters()
+            .setString("number", "%" + gauge.getOfficialNumber())
+            .executeQuery();
+
+        String bfgId = null;
+
+        try {
+            if (rs.next()) {
+                bfgId = rs.getString("BFG_ID");
+            }
+        } finally {
+            rs.close();
+        }
+
+        if (bfgId == null) {
+            log.warn(
+                "No BFG_ID found for current discharge table of gauge '" +
+                gauge + "'");
+            return false;
+        }
+
+        // Set the BFG_ID in FLYS.
+        flysStatements.beginTransaction();
+        try {
+            flysStatements
+                .getStatement("update.bfg.id.discharge.table")
+                .clearParameters()
+                .setInt("id", flysId)
+                .setString("bfg_id", bfgId)
+                .executeUpdate();
+            flysStatements.commitTransaction();
+        } catch (SQLException sqle) {
+            flysStatements.rollbackTransaction();
+            log.error(sqle, sqle);
+            return false;
+        }
+
+        return true;
+    }
+
     protected boolean updateGauge(
         SyncContext context,
         DIPSGauge   gauge
diff -r 46e984a3d576 -r da1e897c7224 flys-aft/src/main/resources/sql/aft-common.properties
--- a/flys-aft/src/main/resources/sql/aft-common.properties	Mon Mar 11 12:55:09 2013 +0100
+++ b/flys-aft/src/main/resources/sql/aft-common.properties	Tue Mar 12 19:15:18 2013 +0100
@@ -16,4 +16,10 @@
 select.tafelwert = \
     SELECT TAFELWERT_NR AS id, WASSERSTAND AS w, ABFLUSS AS q FROM TAFELWERT \
     WHERE ABFLUSSTAFEL_NR = :number
-
+select.bfg.id.current = \
+    SELECT BFG_ID AS BFG_ID FROM ABFLUSSTAFEL \
+    WHERE GUELTIG_VON IN ( \
+        SELECT min(GUELTIG_VON) FROM ABFLUSSTAFEL \
+        WHERE GUELTIG_VON IS NOT NULL AND GUELTIG_BIS IS NULL \
+        AND MESSSTELLE_NR LIKE :number) \
+    AND MESSSTELLE_NR :number
diff -r 46e984a3d576 -r da1e897c7224 flys-aft/src/main/resources/sql/flys-common.properties
--- a/flys-aft/src/main/resources/sql/flys-common.properties	Mon Mar 11 12:55:09 2013 +0100
+++ b/flys-aft/src/main/resources/sql/flys-common.properties	Tue Mar 12 19:15:18 2013 +0100
@@ -47,3 +47,11 @@
     FROM discharge_tables dt \
     LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \
     WHERE gauge_id = :gauge_id
+select.gauge.master.discharge.table = \
+    SELECT \
+        dt.id AS id, \
+        dt.bfg_id AS bfg_id \
+    FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \
+    WHERE g.id = :gauge_id AND g.kind = 0
+update.bfg.id.discharge.table = \
+    UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id
diff -r 46e984a3d576 -r da1e897c7224 flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties
--- a/flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties	Mon Mar 11 12:55:09 2013 +0100
+++ b/flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties	Tue Mar 12 19:15:18 2013 +0100
@@ -1,5 +1,8 @@
-next.gauge.id = SELECT GAUGES_ID_SEQ.NEXTVAL AS gauge_id FROM DUAL
-next.timeinterval.id = SELECT TIME_INTERVALS_ID_SEQ.NEXTVAL AS time_interval_id FROM DUAL
-next.discharge.id = SELECT DISCHARGE_TABLES_ID_SEQ.NEXTVAL AS discharge_table_id FROM DUAL
-next.discharge.table.values.id = SELECT DISCHARGE_TABLE_VALUES_ID_SEQ.NEXTVAL AS discharge_table_values_id FROM DUAL
-
+next.gauge.id = \
+    SELECT GAUGES_ID_SEQ.NEXTVAL AS gauge_id FROM DUAL
+next.timeinterval.id = \
+    SELECT TIME_INTERVALS_ID_SEQ.NEXTVAL AS time_interval_id FROM DUAL
+next.discharge.id = \
+    SELECT DISCHARGE_TABLES_ID_SEQ.NEXTVAL AS discharge_table_id FROM DUAL
+next.discharge.table.values.id = \
+    SELECT DISCHARGE_TABLE_VALUES_ID_SEQ.NEXTVAL AS discharge_table_values_id FROM DUAL