# HG changeset patch # User Sascha L. Teichmann # Date 1325874728 0 # Node ID 83c72bc075916da7215d519e3aca56f35d4f1a59 # Parent 82f5266f881b22745b6a98d60705dddc0f5e9156 Load all discharge tables from AFT and FLYS of a gauge which needs updating. flys-aft/trunk@3618 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 82f5266f881b -r 83c72bc07591 flys-aft/ChangeLog --- a/flys-aft/ChangeLog Fri Jan 06 17:16:46 2012 +0000 +++ b/flys-aft/ChangeLog Fri Jan 06 18:32:08 2012 +0000 @@ -1,3 +1,20 @@ +2012-01-06 Sascha L. Teichmann + + * src/main/resources/sql/flys-common.properties: Added statement + to load all discharge tables of a given gauge. + + * src/main/java/de/intevation/aft/DischargeTable.java: + + * src/main/java/de/intevation/aft/DIPSGauge.java: Store + the official number, too. + + * src/main/java/de/intevation/aft/River.java: In case of + updating a gauge load all discharge tables of that gauge + from FLYS and AFT. TODO: Do pairing based on the descriptions. + + * src/main/java/de/intevation/aft/DischargeTable.java: Code + to load the discharge table from FLYS and AFT. + 2012-01-06 Sascha L. Teichmann * src/main/java/de/intevation/aft/WQDiff.java: New. diff -r 82f5266f881b -r 83c72bc07591 flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java --- a/flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java Fri Jan 06 17:16:46 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/DIPSGauge.java Fri Jan 06 18:32:08 2012 +0000 @@ -93,6 +93,8 @@ protected String aftName; + protected Long officialNumber; + public DIPSGauge() { } @@ -179,5 +181,13 @@ public Datum getLatestDatum() { return datums.get(datums.size()-1); } + + public Long getOfficialNumber() { + return officialNumber; + } + + public void setOfficialNumber(Long officialNumber) { + this.officialNumber = officialNumber; + } } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 82f5266f881b -r 83c72bc07591 flys-aft/src/main/java/de/intevation/aft/DischargeTable.java --- a/flys-aft/src/main/java/de/intevation/aft/DischargeTable.java Fri Jan 06 17:16:46 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/DischargeTable.java Fri Jan 06 18:32:08 2012 +0000 @@ -1,6 +1,7 @@ package de.intevation.aft; import java.util.List; +import java.util.Date; import java.util.ArrayList; import java.sql.SQLException; @@ -139,5 +140,90 @@ .execute(); } } + + public static List loadFlysDischargeTables( + SyncContext context, + int gaugeId + ) + throws SQLException + { + List dts = new ArrayList(); + + ResultSet rs = context + .getFlysStatements() + .getStatement("select.gauge.discharge.tables") + .clearParameters() + .setInt("gauge_id", gaugeId) + .executeQuery(); + + while (rs.next()) { + int id = rs.getInt("id"); + String description = rs.getString("description"); + Date startTime = rs.getDate("start_time"); + Date stopTime = rs.getDate("stop_time"); + TimeInterval ti = startTime == null + ? null + : new TimeInterval(startTime, stopTime); + DischargeTable dt = new DischargeTable( + id, gaugeId, ti, description); + dts.add(dt); + } + rs.close(); + + return dts; + } + + public static List loadAftDischargeTables( + SyncContext context, + Long officialNumber + ) + throws SQLException + { + return loadAftDischargeTables(context, officialNumber, 0); + } + + public static List loadAftDischargeTables( + SyncContext context, + Long officialNumber, + int flysGaugeId + ) + throws SQLException + { + List dts = new ArrayList(); + + ResultSet rs = context + .getAftStatements() + .getStatement("select.abflusstafel") + .clearParameters() + .setString("number", "%" + officialNumber) + .executeQuery(); + + while (rs.next()) { + int dtId = rs.getInt("ABFLUSSTAFEL_NR"); + Date from = rs.getDate("GUELTIG_VON"); + Date to = rs.getDate("GUELTIG_BIS"); + String description = rs.getString("ABFLUSSTAFEL_BEZ"); + if (description == null) { + description = String.valueOf(officialNumber); + } + + double datumValue = rs.getDouble("PEGELNULLPUNKT"); + Double datum = rs.wasNull() ? null : datumValue; + + TimeInterval timeInterval = from == null + ? null + : new TimeInterval(from, to); + + DischargeTable dt = new DischargeTable( + dtId, + flysGaugeId, + timeInterval, + description); + dts.add(dt); + } + rs.close(); + + return dts; + } } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 82f5266f881b -r 83c72bc07591 flys-aft/src/main/java/de/intevation/aft/River.java --- a/flys-aft/src/main/java/de/intevation/aft/River.java Fri Jan 06 17:16:46 2012 +0000 +++ b/flys-aft/src/main/java/de/intevation/aft/River.java Fri Jan 06 18:32:08 2012 +0000 @@ -4,7 +4,6 @@ import java.util.ArrayList; import java.util.HashMap; import java.util.Map; -import java.util.Date; import java.sql.ResultSet; import java.sql.SQLException; @@ -76,6 +75,7 @@ continue; } dipsGauge.setAftName(name); + dipsGauge.setOfficialNumber(number); aftDIPSGauges.put(number, dipsGauge); } @@ -113,6 +113,47 @@ boolean modified = createGauges(context, aftDIPSGauges); + modified |= updateGauges(context, updateGauges); + + return modified; + } + + protected boolean updateGauges( + SyncContext context, + List gauges + ) + throws SQLException + { + boolean modified = false; + + for (DIPSGauge gauge: gauges) { + modified |= updateGauge(context, gauge); + } + + return modified; + } + + protected boolean updateGauge( + SyncContext context, + DIPSGauge gauge + ) + throws SQLException + { + // We need to load all discharge tables from both database + // of the gauge and do some pairing based on their descriptions. + + boolean modified = false; + + List flysDTs = + DischargeTable.loadFlysDischargeTables( + context, gauge.getFlysId()); + + List aftDTs = + DischargeTable.loadAftDischargeTables( + context, gauge.getOfficialNumber()); + + // TODO: Do pairing + return modified; } @@ -216,59 +257,8 @@ ) throws SQLException { - boolean debug = log.isDebugEnabled(); - - List dts = new ArrayList(); - - ResultSet rs = null; - try { - rs = context - .getAftStatements() - .getStatement("select.abflusstafel") - .clearParameters() - .setString("number", "%" + officialNumber). - executeQuery(); - - while (rs.next()) { - int dtId = rs.getInt("ABFLUSSTAFEL_NR"); - Date from = rs.getDate("GUELTIG_VON"); - Date to = rs.getDate("GUELTIG_BIS"); - String description = rs.getString("ABFLUSSTAFEL_BEZ"); - if (description == null) { - description = String.valueOf(officialNumber); - } - - double datumValue = rs.getDouble("PEGELNULLPUNKT"); - Double datum = rs.wasNull() ? null : datumValue; - - if (debug) { - log.debug("id: " + dtId); - log.debug("valid from: " + from); - log.debug("valid to: " + to); - log.debug("datum: " + datum); - log.debug("description: " + description); - } - - TimeInterval timeInterval = from == null - ? null - : new TimeInterval(from, to); - - DischargeTable dt = new DischargeTable( - dtId, - gauge.getFlysId(), - timeInterval, - description); - dts.add(dt); - } - } - finally { - if (rs != null) { - rs.close(); - rs = null; - } - } - - return dts; + return DischargeTable.loadAftDischargeTables( + context, officialNumber, gauge.getFlysId()); } protected void persistFlysTimeIntervals( diff -r 82f5266f881b -r 83c72bc07591 flys-aft/src/main/resources/sql/flys-common.properties --- a/flys-aft/src/main/resources/sql/flys-common.properties Fri Jan 06 17:16:46 2012 +0000 +++ b/flys-aft/src/main/resources/sql/flys-common.properties Fri Jan 06 18:32:08 2012 +0000 @@ -13,3 +13,6 @@ 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.description AS description, ti.start_time AS start_time, ti.stop_time AS stop_time \ + FROM discharge_tables dt LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ + WHERE gauge_id = :gauge_id