sascha@4090: package de.intevation.aft; sascha@4090: teichmann@4772: import de.intevation.db.ConnectedStatements; teichmann@4772: import de.intevation.db.SymbolicStatement; sascha@4091: teichmann@4772: import java.sql.ResultSet; sascha@4091: import java.sql.SQLException; sascha@4102: import java.sql.Types; sascha@4091: teichmann@4772: import java.util.ArrayList; teichmann@4772: import java.util.Date; teichmann@4772: import java.util.List; teichmann@4772: import java.util.Set; teichmann@4772: import java.util.TreeSet; 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; teichmann@4775: protected String bfgId; 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, teichmann@4775: String description, teichmann@4775: String bfgId sascha@4090: ) { sascha@4090: this.gaugeId = gaugeId; sascha@4090: this.timeInterval = timeInterval; sascha@4090: this.description = description; teichmann@4775: this.bfgId = bfgId; 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, teichmann@4775: String description, teichmann@4775: String bfgId sascha@4090: ) { teichmann@4775: this(gaugeId, timeInterval, description, bfgId); 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: teichmann@4775: public String getBfgId() { teichmann@4775: return bfgId; teichmann@4775: } teichmann@4775: teichmann@4775: public void setBfgId(String bfgId) { teichmann@4775: this.bfgId = bfgId; teichmann@4775: } teichmann@4775: teichmann@4775: 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) { teichmann@4775: int wqId; sascha@4103: ResultSet rs = nextId.executeQuery(); teichmann@4775: try { teichmann@4775: rs.next(); teichmann@4775: wqId = rs.getInt("discharge_table_values_id"); teichmann@4775: } teichmann@4775: finally { teichmann@4775: rs.close(); teichmann@4775: } 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(); teichmann@4775: try { teichmann@4775: OUTER: while (rs.next()) { teichmann@4775: int id = rs.getInt("id"); teichmann@4775: String description = rs.getString("description"); teichmann@4775: String bfgId = rs.getString("bfg_id"); teichmann@4775: if (description == null) { teichmann@4775: description = ""; teichmann@4775: } teichmann@4775: if (bfgId == null) { teichmann@4775: bfgId = ""; teichmann@4775: } teichmann@4775: for (DischargeTable dt: dts) { teichmann@4775: if (dt.getBfgId().equals(bfgId)) { teichmann@4775: log.warn("FLYS: Found discharge table '" + teichmann@4775: bfgId + "' with same bfg_id. -> ignore"); teichmann@4775: continue OUTER; teichmann@4775: } teichmann@4775: } teichmann@4775: Date startTime = rs.getDate("start_time"); teichmann@4775: Date stopTime = rs.getDate("stop_time"); teichmann@4775: TimeInterval ti = startTime == null teichmann@4775: ? null teichmann@4775: : new TimeInterval(startTime, stopTime); sascha@4097: teichmann@4775: DischargeTable dt = new DischargeTable( teichmann@4775: id, gaugeId, ti, description, bfgId); teichmann@4775: dts.add(dt); sascha@4105: } sascha@4097: } teichmann@4775: finally { teichmann@4775: rs.close(); teichmann@4775: } 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(); teichmann@4775: try { teichmann@4775: OUTER: while (rs.next()) { teichmann@4775: int dtId = rs.getInt("ABFLUSSTAFEL_NR"); teichmann@4775: Date from = rs.getDate("GUELTIG_VON"); teichmann@4775: Date to = rs.getDate("GUELTIG_BIS"); sascha@4101: teichmann@4775: if (from == null) { teichmann@4736: log.warn("AFT: ABFLUSSTAFEL_NR = " teichmann@4775: + dtId + ": GUELTIG_VON = NULL -> ignored."); teichmann@4775: } sascha@4097: teichmann@4775: if (to == null) { teichmann@4775: log.warn("AFT: ABFLUSSTAFEL_NR = " teichmann@4775: + dtId + ": GUELTIG_BIS = NULL -> ignored."); sascha@4105: } sascha@4105: teichmann@4775: if (from == null || to == null) { teichmann@4775: continue; teichmann@4775: } sascha@4097: teichmann@4775: if (from.compareTo(to) > 0) { teichmann@4775: log.warn("AFT: ABFLUSSTAFEL_NR = " teichmann@4775: + dtId + ": " + from + " > " + to + ". -> swap"); teichmann@4775: Date temp = from; teichmann@4775: from = to; teichmann@4775: to = temp; teichmann@4775: } teichmann@4775: teichmann@4775: String description = rs.getString("ABFLUSSTAFEL_BEZ"); teichmann@4775: if (description == null) { teichmann@4775: description = String.valueOf(officialNumber); teichmann@4775: } teichmann@4775: teichmann@4775: String bfgId = rs.getString("BFG_ID"); teichmann@4775: if (bfgId == null) { teichmann@4775: bfgId = ""; teichmann@4775: } teichmann@4775: teichmann@4775: for (DischargeTable dt: dts) { teichmann@4775: if (dt.getBfgId().equals(bfgId)) { teichmann@4775: log.warn("AFT: Found discharge table '" + teichmann@4775: bfgId + "' with same bfg_id. -> ignore."); teichmann@4775: continue OUTER; teichmann@4775: } teichmann@4775: } teichmann@4775: teichmann@4775: TimeInterval timeInterval = new TimeInterval(from, to); teichmann@4775: teichmann@4775: DischargeTable dt = new DischargeTable( teichmann@4775: dtId, teichmann@4775: flysGaugeId, teichmann@4775: timeInterval, teichmann@4775: description, teichmann@4775: bfgId); teichmann@4775: dts.add(dt); teichmann@4775: } sascha@4097: } teichmann@4775: finally { teichmann@4775: rs.close(); teichmann@4775: } 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: teichmann@4775: int flysId; teichmann@4775: sascha@4102: ResultSet rs = flysStatements sascha@4102: .getStatement("next.discharge.id") sascha@4102: .executeQuery(); teichmann@4775: try { teichmann@4775: rs.next(); teichmann@4775: flysId = rs.getInt("discharge_table_id"); teichmann@4775: } teichmann@4775: finally { teichmann@4775: rs.close(); teichmann@4775: } 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) teichmann@4775: .setString("description", description) teichmann@4775: .setString("bfg_id", bfgId); 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 :