# HG changeset patch # User Sascha L. Teichmann # Date 1363112165 -3600 # Node ID 7a11271f5593f7785f31b46e7595498453611929 # Parent da1e897c7224b465bee581e84123e25808037f06# Parent 0c45b8797baab2efcd5a41911f8ee2a081555145 Merged diff -r 0c45b8797baa -r 7a11271f5593 flys-aft/src/main/java/de/intevation/aft/River.java --- a/flys-aft/src/main/java/de/intevation/aft/River.java Tue Mar 12 18:40:15 2013 +0100 +++ b/flys-aft/src/main/java/de/intevation/aft/River.java Tue Mar 12 19:16:05 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 0c45b8797baa -r 7a11271f5593 flys-aft/src/main/resources/sql/aft-common.properties --- a/flys-aft/src/main/resources/sql/aft-common.properties Tue Mar 12 18:40:15 2013 +0100 +++ b/flys-aft/src/main/resources/sql/aft-common.properties Tue Mar 12 19:16:05 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 0c45b8797baa -r 7a11271f5593 flys-aft/src/main/resources/sql/flys-common.properties --- a/flys-aft/src/main/resources/sql/flys-common.properties Tue Mar 12 18:40:15 2013 +0100 +++ b/flys-aft/src/main/resources/sql/flys-common.properties Tue Mar 12 19:16:05 2013 +0100 @@ -6,29 +6,52 @@ JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \ WHERE w.kind = 0 \ GROUP BY r.id, r.name -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) -select.timeintervals = SELECT id, start_time, stop_time FROM time_intervals -next.timeinterval.id = SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id -insert.timeinterval = INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:id, :start_time, :stop_time) -next.discharge.id = SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id +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) +select.timeintervals = \ + SELECT id, start_time, stop_time FROM time_intervals +next.timeinterval.id = \ + SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id +insert.timeinterval = \ + INSERT INTO time_intervals (id, start_time, stop_time) \ + VALUES (:id, :start_time, :stop_time) +next.discharge.id = \ + SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id insert.dischargetable = \ INSERT INTO discharge_tables \ (id, gauge_id, description, bfg_id, kind, time_interval_id) \ VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) -select.discharge.table.values = SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id -next.discharge.table.values.id = SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id -insert.discharge.table.value = INSERT INTO discharge_table_values (id, table_id, w, q) VALUES (:id, :table_id, :w, :q) -delete.discharge.table.value = DELETE FROM discharge_table_values WHERE id = :id +select.discharge.table.values = \ + SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id +next.discharge.table.values.id = \ + SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id +insert.discharge.table.value = \ + INSERT INTO discharge_table_values (id, table_id, w, q) \ + VALUES (:id, :table_id, :w, :q) +delete.discharge.table.value = \ + DELETE FROM discharge_table_values WHERE id = :id select.gauge.discharge.tables = \ SELECT \ - dt.id AS id, \ + dt.id AS id, \ dt.description AS description, \ - ti.start_time AS start_time, \ - ti.stop_time AS stop_time, \ - dt.bfg_id AS bfg_id \ + ti.start_time AS start_time, \ + ti.stop_time AS stop_time, \ + dt.bfg_id AS bfg_id \ 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 0c45b8797baa -r 7a11271f5593 flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties --- a/flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties Tue Mar 12 18:40:15 2013 +0100 +++ b/flys-aft/src/main/resources/sql/flys-oracle-jdbc-oracledriver.properties Tue Mar 12 19:16:05 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