sascha@4090: package de.intevation.aft; sascha@4090: sascha@4091: import java.util.List; sascha@4097: import java.util.Date; sascha@4091: import java.util.ArrayList; sascha@4103: import java.util.TreeSet; sascha@4103: import java.util.Set; sascha@4091: sascha@4091: import java.sql.SQLException; sascha@4091: import java.sql.ResultSet; sascha@4102: import java.sql.Types; sascha@4091: sascha@4091: import de.intevation.db.SymbolicStatement; sascha@4092: import de.intevation.db.ConnectedStatements; sascha@4091: sascha@4101: import org.apache.log4j.Logger; sascha@4101: sascha@4090: public class DischargeTable sascha@4090: { sascha@4101: private static Logger log = Logger.getLogger(DischargeTable.class); sascha@4101: sascha@4090: protected int id; sascha@4090: protected int gaugeId; sascha@4090: protected TimeInterval timeInterval; sascha@4090: protected String description; sascha@4103: protected Set values; sascha@4090: sascha@4090: public DischargeTable() { sascha@4090: } sascha@4090: sascha@4090: public DischargeTable( teichmann@4736: int gaugeId, teichmann@4736: TimeInterval timeInterval, sascha@4090: String description sascha@4090: ) { sascha@4090: this.gaugeId = gaugeId; sascha@4090: this.timeInterval = timeInterval; sascha@4090: this.description = description; sascha@4103: values = new TreeSet(WQ.EPS_CMP); sascha@4090: } sascha@4090: sascha@4090: public DischargeTable( teichmann@4736: int id, teichmann@4736: int gaugeId, teichmann@4736: TimeInterval timeInterval, sascha@4090: String description sascha@4090: ) { sascha@4090: this(gaugeId, timeInterval, description); sascha@4090: this.id = id; sascha@4090: } sascha@4090: sascha@4090: public int getId() { sascha@4090: return id; sascha@4090: } sascha@4090: sascha@4090: public void setId(int id) { sascha@4090: this.id = id; sascha@4090: } sascha@4090: sascha@4090: public int getGaugeId() { sascha@4090: return gaugeId; sascha@4090: } sascha@4090: sascha@4090: public void setGaugeId(int gaugeId) { sascha@4090: this.gaugeId = gaugeId; sascha@4090: } sascha@4090: sascha@4090: public TimeInterval getTimeInterval() { sascha@4090: return timeInterval; sascha@4090: } sascha@4090: sascha@4090: public void setTimeInterval(TimeInterval timeInterval) { sascha@4090: this.timeInterval = timeInterval; sascha@4090: } sascha@4090: sascha@4090: public String getDescription() { sascha@4090: return description; sascha@4090: } sascha@4090: sascha@4090: public void setDescription(String description) { sascha@4090: this.description = description; sascha@4090: } sascha@4091: sascha@4093: public void clearValues() { sascha@4093: values.clear(); sascha@4093: } sascha@4093: sascha@4103: public Set getValues() { sascha@4099: return values; sascha@4099: } sascha@4099: sascha@4103: public void setValues(Set values) { sascha@4099: this.values = values; sascha@4099: } sascha@4099: sascha@4099: teichmann@4736: protected void loadValues(SymbolicStatement.Instance query) sascha@4091: throws SQLException sascha@4091: { sascha@4091: ResultSet rs = query.executeQuery(); sascha@4091: while (rs.next()) { sascha@4091: int id = rs.getInt("id"); sascha@4091: double w = rs.getDouble("w"); sascha@4091: double q = rs.getDouble("q"); sascha@4103: if (!values.add(new WQ(id, w, q))) { sascha@4110: log.warn("FLYS/AFT: Value duplication w="+w+" q="+q+". -> ignore."); sascha@4103: } sascha@4091: } sascha@4091: rs.close(); sascha@4091: } sascha@4091: sascha@4091: public void loadAftValues(SyncContext context) throws SQLException { sascha@4091: loadValues(context.getAftStatements() sascha@4091: .getStatement("select.tafelwert") sascha@4091: .clearParameters() sascha@4091: .setInt("number", getId())); sascha@4091: } sascha@4091: sascha@4091: public void loadFlysValues(SyncContext context) throws SQLException { sascha@4091: loadValues(context.getFlysStatements() sascha@4091: .getStatement("select.discharge.table.values") sascha@4091: .clearParameters() sascha@4091: .setInt("table_id", getId())); sascha@4091: } sascha@4092: sascha@4092: public void storeFlysValues( sascha@4092: SyncContext context, sascha@4092: int dischargeTableId sascha@4092: ) sascha@4092: throws SQLException sascha@4092: { sascha@4092: ConnectedStatements flysStatements = context.getFlysStatements(); sascha@4092: sascha@4092: // Create the ids. sascha@4092: SymbolicStatement.Instance nextId = flysStatements sascha@4092: .getStatement("next.discharge.table.values.id"); sascha@4092: sascha@4092: // Insert the values. sascha@4092: SymbolicStatement.Instance insertDTV = flysStatements sascha@4092: .getStatement("insert.discharge.table.value"); sascha@4092: sascha@4103: for (WQ wq: values) { sascha@4103: ResultSet rs = nextId.executeQuery(); sascha@4103: rs.next(); sascha@4103: int wqId = rs.getInt("discharge_table_values_id"); sascha@4103: rs.close(); sascha@4103: sascha@4092: insertDTV sascha@4092: .clearParameters() sascha@4103: .setInt("id", wqId) sascha@4092: .setInt("table_id", dischargeTableId) sascha@4092: .setDouble("w", wq.getW()) sascha@4092: .setDouble("q", wq.getQ()) sascha@4092: .execute(); sascha@4092: } sascha@4092: } sascha@4097: sascha@4097: public static List loadFlysDischargeTables( sascha@4097: SyncContext context, sascha@4097: int gaugeId sascha@4097: ) sascha@4097: throws SQLException sascha@4097: { sascha@4097: List dts = new ArrayList(); sascha@4097: sascha@4097: ResultSet rs = context sascha@4097: .getFlysStatements() sascha@4097: .getStatement("select.gauge.discharge.tables") sascha@4097: .clearParameters() sascha@4097: .setInt("gauge_id", gaugeId) sascha@4097: .executeQuery(); sascha@4097: sascha@4105: OUTER: while (rs.next()) { sascha@4097: int id = rs.getInt("id"); sascha@4097: String description = rs.getString("description"); sascha@4105: if (description == null) { sascha@4105: description = ""; sascha@4105: } sascha@4105: for (DischargeTable dt: dts) { sascha@4105: if (dt.getDescription().equals(description)) { sascha@4105: log.warn("FLYS: Found discharge table '" + sascha@4105: description + "' with same description. -> ignore"); sascha@4105: continue OUTER; sascha@4105: } sascha@4105: } sascha@4105: Date startTime = rs.getDate("start_time"); sascha@4105: Date stopTime = rs.getDate("stop_time"); sascha@4097: TimeInterval ti = startTime == null sascha@4097: ? null sascha@4097: : new TimeInterval(startTime, stopTime); sascha@4105: sascha@4097: DischargeTable dt = new DischargeTable( sascha@4097: id, gaugeId, ti, description); sascha@4097: dts.add(dt); sascha@4097: } sascha@4097: rs.close(); sascha@4097: sascha@4097: return dts; sascha@4097: } sascha@4097: sascha@4097: public static List loadAftDischargeTables( sascha@4097: SyncContext context, sascha@4097: Long officialNumber sascha@4097: ) sascha@4097: throws SQLException sascha@4097: { sascha@4097: return loadAftDischargeTables(context, officialNumber, 0); sascha@4097: } sascha@4097: sascha@4097: public static List loadAftDischargeTables( sascha@4097: SyncContext context, sascha@4097: Long officialNumber, sascha@4097: int flysGaugeId sascha@4097: ) sascha@4097: throws SQLException sascha@4097: { sascha@4097: List dts = new ArrayList(); sascha@4097: sascha@4097: ResultSet rs = context sascha@4097: .getAftStatements() sascha@4097: .getStatement("select.abflusstafel") sascha@4097: .clearParameters() sascha@4097: .setString("number", "%" + officialNumber) sascha@4097: .executeQuery(); sascha@4097: sascha@4105: OUTER: while (rs.next()) { sascha@4101: int dtId = rs.getInt("ABFLUSSTAFEL_NR"); sascha@4101: Date from = rs.getDate("GUELTIG_VON"); sascha@4101: Date to = rs.getDate("GUELTIG_BIS"); sascha@4101: teichmann@4134: if (from == null) { teichmann@4134: log.warn("AFT: ABFLUSSTAFEL_NR = " teichmann@4134: + dtId + ": GUELTIG_VON = NULL -> ignored."); teichmann@4134: } teichmann@4134: teichmann@4134: if (to == null) { teichmann@4134: log.warn("AFT: ABFLUSSTAFEL_NR = " teichmann@4134: + dtId + ": GUELTIG_BIS = NULL -> ignored."); teichmann@4134: } teichmann@4134: teichmann@4134: if (from == null || to == null) { teichmann@4134: continue; teichmann@4134: } teichmann@4134: teichmann@4134: if (from.compareTo(to) > 0) { teichmann@4736: log.warn("AFT: ABFLUSSTAFEL_NR = " sascha@4101: + dtId + ": " + from + " > " + to + ". -> swap"); sascha@4101: Date temp = from; sascha@4101: from = to; sascha@4101: to = temp; sascha@4101: } sascha@4101: sascha@4097: String description = rs.getString("ABFLUSSTAFEL_BEZ"); sascha@4097: if (description == null) { sascha@4097: description = String.valueOf(officialNumber); sascha@4097: } sascha@4097: sascha@4105: for (DischargeTable dt: dts) { sascha@4105: if (dt.getDescription().equals(description)) { sascha@4105: log.warn("AFT: Found discharge table '" + sascha@4105: description + "' with same description. -> ignore."); sascha@4105: continue OUTER; sascha@4105: } sascha@4105: } sascha@4105: teichmann@4134: TimeInterval timeInterval = new TimeInterval(from, to); sascha@4097: sascha@4097: DischargeTable dt = new DischargeTable( sascha@4097: dtId, sascha@4097: flysGaugeId, sascha@4097: timeInterval, sascha@4097: description); sascha@4097: dts.add(dt); sascha@4097: } sascha@4097: rs.close(); sascha@4097: sascha@4097: return dts; sascha@4097: } sascha@4102: sascha@4102: public void persistFlysTimeInterval( sascha@4102: SyncContext context sascha@4102: ) sascha@4102: throws SQLException sascha@4102: { sascha@4102: if (timeInterval != null) { sascha@4102: timeInterval = context.fetchOrCreateFLYSTimeInterval( sascha@4102: timeInterval); sascha@4102: } sascha@4102: } sascha@4102: sascha@4102: public int persistFlysDischargeTable( sascha@4102: SyncContext context, sascha@4102: int gaugeId sascha@4102: ) sascha@4102: throws SQLException sascha@4102: { sascha@4102: ConnectedStatements flysStatements = sascha@4102: context.getFlysStatements(); sascha@4102: sascha@4102: ResultSet rs = flysStatements sascha@4102: .getStatement("next.discharge.id") sascha@4102: .executeQuery(); sascha@4102: sascha@4102: rs.next(); sascha@4102: int flysId = rs.getInt("discharge_table_id"); sascha@4102: rs.close(); sascha@4102: sascha@4102: SymbolicStatement.Instance insertDT = flysStatements sascha@4102: .getStatement("insert.dischargetable") sascha@4102: .clearParameters() sascha@4102: .setInt("id", flysId) sascha@4102: .setInt("gauge_id", gaugeId) sascha@4102: .setString("description", description); sascha@4102: sascha@4102: if (timeInterval != null) { sascha@4102: insertDT.setInt("time_interval_id", timeInterval.getId()); sascha@4102: } sascha@4102: else { sascha@4102: insertDT.setNull("time_interval_id", Types.INTEGER); sascha@4102: } sascha@4102: sascha@4102: insertDT.execute(); sascha@4102: sascha@4102: if (log.isDebugEnabled()) { sascha@4102: log.debug("FLYS: Created discharge table id: " + id); sascha@4102: } sascha@4102: sascha@4102: return flysId; sascha@4102: } sascha@4090: } sascha@4090: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :