teichmann@5844: /* Copyright (C) 2011, 2012, 2013 by Bundesanstalt für Gewässerkunde
teichmann@5844:  * Software engineering by Intevation GmbH
teichmann@5844:  *
teichmann@5992:  * This file is Free Software under the GNU AGPL (>=v3)
teichmann@5844:  * and comes with ABSOLUTELY NO WARRANTY! Check out the
teichmann@5992:  * documentation coming with Dive4Elements River for details.
teichmann@5844:  */
teichmann@5844: 
teichmann@5829: package org.dive4elements.river.model;
ingo@2809: 
ingo@2809: import java.io.Serializable;
gernotbelger@9246: import java.util.ArrayList;
ingo@2809: import java.util.List;
ingo@2809: 
mschaefer@8975: import javax.persistence.Column;
ingo@2809: import javax.persistence.Entity;
mschaefer@8975: import javax.persistence.GeneratedValue;
mschaefer@8975: import javax.persistence.GenerationType;
ingo@2809: import javax.persistence.Id;
mschaefer@8975: import javax.persistence.JoinColumn;
mschaefer@8975: import javax.persistence.OneToMany;
mschaefer@8975: import javax.persistence.OneToOne;
ingo@2809: import javax.persistence.SequenceGenerator;
mschaefer@8975: import javax.persistence.Table;
ingo@2872: 
teichmann@5829: import org.dive4elements.river.backend.SessionHolder;
mschaefer@8975: import org.hibernate.Query;
mschaefer@8975: import org.hibernate.Session;
ingo@2872: 
ingo@2809: @Entity
tom@8560: @Table(name = "bed_height")
tom@8560: public class BedHeight implements Serializable {
ingo@2809: 
ingo@2809:     private Integer id;
ingo@2809:     private Integer year;
ingo@2809: 
ingo@2809:     private String evaluationBy;
ingo@2809:     private String description;
ingo@2809: 
ingo@2809:     private River river;
ingo@2809: 
gernotbelger@9074:     private BedHeightType type;
ingo@2809: 
ingo@2809:     private LocationSystem locationSystem;
ingo@2809: 
ingo@2809:     private ElevationModel curElevationModel;
ingo@2809: 
ingo@2809:     private ElevationModel oldElevationModel;
ingo@2809: 
ingo@2809:     private Range range;
ingo@2809: 
mschaefer@8975:     private String sounding_width_info;
mschaefer@9038:     private String notes;
mschaefer@8975: 
tom@8560:     private List<BedHeightValue> values;
ingo@2809: 
tom@8560:     public BedHeight() {
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public BedHeight(final River river, final Integer year, final BedHeightType type, final LocationSystem locationSystem,
gernotbelger@9074:             final ElevationModel curElevationModel, final Range range) {
mschaefer@8975:         this(river, year, type, locationSystem, curElevationModel, null, range, null, null, null, null);
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public BedHeight(final River river, final Integer year, final BedHeightType type, final LocationSystem locationSystem,
mschaefer@9038:             final ElevationModel curElevationModel, final ElevationModel oldElevationModel, final Range range, final String evaluationBy,
mschaefer@9038:             final String description, final String sounding_width_info, final String notes) {
gernotbelger@9074:         this.river = river;
gernotbelger@9074:         this.year = year;
gernotbelger@9074:         this.type = type;
gernotbelger@9074:         this.locationSystem = locationSystem;
ingo@2809:         this.curElevationModel = curElevationModel;
ingo@2809:         this.oldElevationModel = oldElevationModel;
gernotbelger@9074:         this.range = range;
gernotbelger@9074:         this.evaluationBy = evaluationBy;
gernotbelger@9074:         this.description = description;
mschaefer@8975:         this.sounding_width_info = sounding_width_info;
mschaefer@9038:         this.notes = notes;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @Id
mschaefer@8975:     @SequenceGenerator(name = "SEQUENCE_BED_HEIGHT_ID_SEQ", sequenceName = "BED_HEIGHT_ID_SEQ", allocationSize = 1)
mschaefer@8975:     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE_BED_HEIGHT_ID_SEQ")
ingo@2809:     @Column(name = "id")
ingo@2809:     public Integer getId() {
mschaefer@8975:         return this.id;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setId(final Integer id) {
ingo@2809:         this.id = id;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @OneToOne
tom@8842:     @JoinColumn(name = "river_id")
ingo@2809:     public River getRiver() {
mschaefer@8975:         return this.river;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setRiver(final River river) {
ingo@2809:         this.river = river;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @Column(name = "year")
ingo@2809:     public Integer getYear() {
mschaefer@8975:         return this.year;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setYear(final Integer year) {
ingo@2809:         this.year = year;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @OneToOne
ingo@2809:     @JoinColumn(name = "type_id")
ingo@2809:     public BedHeightType getType() {
mschaefer@8975:         return this.type;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setType(final BedHeightType type) {
ingo@2809:         this.type = type;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @OneToOne
ingo@2809:     @JoinColumn(name = "location_system_id")
ingo@2809:     public LocationSystem getLocationSystem() {
mschaefer@8975:         return this.locationSystem;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setLocationSystem(final LocationSystem locationSystem) {
ingo@2809:         this.locationSystem = locationSystem;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @OneToOne
ingo@2809:     @JoinColumn(name = "cur_elevation_model_id")
ingo@2809:     public ElevationModel getCurElevationModel() {
mschaefer@8975:         return this.curElevationModel;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setCurElevationModel(final ElevationModel curElevationModel) {
ingo@2809:         this.curElevationModel = curElevationModel;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @OneToOne
ingo@2809:     @JoinColumn(name = "old_elevation_model_id")
ingo@2809:     public ElevationModel getOldElevationModel() {
mschaefer@8975:         return this.oldElevationModel;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setOldElevationModel(final ElevationModel oldElevationModel) {
ingo@2809:         this.oldElevationModel = oldElevationModel;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @OneToOne
ingo@2809:     @JoinColumn(name = "range_id")
ingo@2809:     public Range getRange() {
mschaefer@8975:         return this.range;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setRange(final Range range) {
ingo@2809:         this.range = range;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @Column(name = "evaluation_by")
ingo@2809:     public String getEvaluationBy() {
mschaefer@8975:         return this.evaluationBy;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setEvaluationBy(final String evaluationBy) {
ingo@2809:         this.evaluationBy = evaluationBy;
ingo@2809:     }
ingo@2809: 
ingo@2809:     @Column(name = "description")
ingo@2809:     public String getDescription() {
mschaefer@8975:         return this.description;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setDescription(final String description) {
ingo@2809:         this.description = description;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     @Column(name = "sounding_width_info")
mschaefer@8975:     public String getSoundingWidthInfo() {
mschaefer@8975:         return this.sounding_width_info;
mschaefer@8975:     }
mschaefer@8975: 
mschaefer@8975:     public void setSoundingWidthInfo(final String sounding_width_info) {
mschaefer@8975:         this.sounding_width_info = sounding_width_info;
mschaefer@8975:     }
mschaefer@8975: 
mschaefer@9038:     @Column(name = "notes")
mschaefer@9038:     public String getNotes() {
mschaefer@9038:         return this.notes;
mschaefer@8975:     }
mschaefer@8975: 
mschaefer@9038:     public void setNotes(final String notes) {
mschaefer@9038:         this.notes = notes;
mschaefer@8975:     }
mschaefer@8975: 
ingo@2809:     @OneToMany
tom@8560:     @JoinColumn(name = "bed_height_id")
tom@8560:     public List<BedHeightValue> getValues() {
mschaefer@8975:         return this.values;
ingo@2809:     }
ingo@2809: 
mschaefer@8975:     public void setValues(final List<BedHeightValue> values) {
ingo@2809:         this.values = values;
ingo@2809:     }
ingo@2872: 
gernotbelger@9074:     public static List<BedHeight> getBedHeights(final River river, final double kmLo, final double kmHi) {
mschaefer@8975:         final Session session = SessionHolder.HOLDER.get();
ingo@2872: 
gernotbelger@9245:         final Query query = session.createQuery("from BedHeight" + " where river=:river" + " and id in (select bedHeight.id from BedHeightValue"
gernotbelger@9245:                 + " where station between :kmfrom and :kmto" + " group by bedHeight.id)");
ingo@2872: 
ingo@2872:         query.setParameter("river", river);
mschaefer@9218:         query.setParameter("kmfrom", kmLo);
mschaefer@9218:         query.setParameter("kmto", kmHi);
ingo@2875: 
mschaefer@8975:         final List<BedHeight> singles = query.list();
ingo@2875: 
mschaefer@9219:         return singles;
ingo@2872:     }
ingo@2872: 
mschaefer@8975:     public static BedHeight getBedHeightById(final int id) {
mschaefer@8975:         final Session session = SessionHolder.HOLDER.get();
ingo@2872: 
gernotbelger@9074:         final Query query = session.createQuery("from BedHeight where id=:id");
ingo@2872: 
ingo@2872:         query.setParameter("id", id);
ingo@2872: 
mschaefer@8975:         final List<BedHeight> singles = query.list();
ingo@2872: 
mschaefer@9098:         return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
ingo@2872:     }
gernotbelger@8942: 
gernotbelger@9211:     public static BedHeight getBedHeightByDescription(final River river, final String description, final double startKm, final double endKm) {
mschaefer@8975: 
gernotbelger@8942:         final Session session = SessionHolder.HOLDER.get();
mschaefer@8975: 
gernotbelger@9245:         final Query query = session.createQuery("FROM BedHeight" + " WHERE (TRIM(description)=:description) AND river=:river"
gernotbelger@9245:                 + " AND id IN (SELECT bedHeight.id FROM BedHeightValue" + " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
gernotbelger@9089:         query.setParameter("river", river);
gernotbelger@8942:         query.setParameter("description", description);
mschaefer@9218:         query.setParameter("kmfrom", startKm);
mschaefer@9218:         query.setParameter("kmto", endKm);
mschaefer@8975: 
gernotbelger@8942:         final List<BedHeight> singles = query.list();
mschaefer@8975: 
mschaefer@9098:         return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
gernotbelger@8942:     }
gernotbelger@9245: 
gernotbelger@9245:     public static List<BedHeight> getBedHeightEpochs(final River river, final double startKm, final double endKm) {
gernotbelger@9245: 
gernotbelger@9245:         final Session session = SessionHolder.HOLDER.get();
gernotbelger@9245:         final String description = "epoch";
gernotbelger@9245:         final Query query = session.createQuery("FROM BedHeight" + " WHERE lower(description) LIKE :description AND " + "river=:river"
gernotbelger@9245:                 + " AND id IN (SELECT bedHeight.id FROM BedHeightValue" + " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
gernotbelger@9245:         query.setParameter("river", river);
gernotbelger@9245:         query.setParameter("description", "%" + description + "%");
gernotbelger@9245:         query.setParameter("kmfrom", startKm);
gernotbelger@9245:         query.setParameter("kmto", endKm);
gernotbelger@9245: 
gernotbelger@9245:         final List<BedHeight> singles = query.list();
gernotbelger@9245: 
gernotbelger@9245:         return ((singles != null) && !singles.isEmpty()) ? singles : null;
gernotbelger@9245:     }
gernotbelger@9245: 
gernotbelger@9245:     public static List<BedHeight> getBedHeightYear(final River river, final double startKm, final double endKm) {
gernotbelger@9245: 
gernotbelger@9245:         final Session session = SessionHolder.HOLDER.get();
gernotbelger@9245:         final String description = "epoch";
gernotbelger@9245:         final Query query = session.createQuery("FROM BedHeight" + " WHERE lower(description) NOT LIKE :description AND " + "river=:river"
gernotbelger@9245:                 + " AND id IN (SELECT bedHeight.id FROM BedHeightValue" + " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
gernotbelger@9245:         query.setParameter("river", river);
gernotbelger@9245:         query.setParameter("description", "%" + description + "%");
gernotbelger@9245:         query.setParameter("kmfrom", startKm);
gernotbelger@9245:         query.setParameter("kmto", endKm);
gernotbelger@9245: 
gernotbelger@9245:         final List<BedHeight> singles = query.list();
gernotbelger@9245: 
gernotbelger@9245:         return ((singles != null) && !singles.isEmpty()) ? singles : null;
gernotbelger@9245:     }
gernotbelger@9246: 
gernotbelger@9246:     public static Range getRangeFromBedHeights(final BedHeight bh) {
gernotbelger@9246:         final List<Range> ranges = new ArrayList<>();
gernotbelger@9246: 
gernotbelger@9246:         final Session session = SessionHolder.HOLDER.get();
gernotbelger@9246: 
gernotbelger@9246:         final Query query = session.createQuery("FROM Range" + " WHERE id=:range_id)");
gernotbelger@9246:         query.setParameter("range_id", bh.getRange().getId());
gernotbelger@9246: 
gernotbelger@9246:         final List<Range> singles = query.list();
gernotbelger@9246: 
gernotbelger@9246:         return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
gernotbelger@9246:     }
gernotbelger@9246: 
gernotbelger@9246:     public static List<BedHeight> getBedHeightYearEpoch(final boolean isEpoch, final Integer year, final River river, final double lowerKm,
gernotbelger@9246:             final double upperKm) {
gernotbelger@9246: 
gernotbelger@9246:         final Session session = SessionHolder.HOLDER.get();
gernotbelger@9246:         final String description = "epoch";
gernotbelger@9246: 
gernotbelger@9246:         final StringBuilder builder = new StringBuilder();
gernotbelger@9246:         builder.append("FROM BedHeight");
gernotbelger@9246:         if (isEpoch) {
gernotbelger@9246:             builder.append(" WHERE lower(description) LIKE :description ");
gernotbelger@9246:         } else {
gernotbelger@9246:             builder.append(" WHERE lower(description) NOT LIKE :description ");
gernotbelger@9246:         }
gernotbelger@9246:         builder.append(" AND year =:year");
gernotbelger@9246: 
gernotbelger@9246:         builder.append(
gernotbelger@9246:                 " AND river=:river  AND id IN (SELECT bedHeight.id FROM BedHeightValue   WHERE station BETWEEN :kmfrom AND :kmto  GROUP BY bedHeight.id )");
gernotbelger@9246:         final Query query = session.createQuery(builder.toString());
gernotbelger@9246:         query.setParameter("river", river);
gernotbelger@9246:         query.setParameter("year", year);
gernotbelger@9246:         query.setParameter("description", "%" + description + "%");
gernotbelger@9246:         query.setParameter("kmfrom", lowerKm);
gernotbelger@9246:         query.setParameter("kmto", upperKm);
gernotbelger@9246: 
gernotbelger@9246:         final List<BedHeight> singles = query.list();
gernotbelger@9246: 
gernotbelger@9246:         return ((singles != null) && !singles.isEmpty()) ? singles : null;
gernotbelger@9246:     }
gernotbelger@9211: }