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: 
d@9706: import com.vividsolutions.jts.util.Assert;
d@9706: 
ingo@2809: @Entity
tom@8560: @Table(name = "bed_height")
tom@8560: public class BedHeight implements Serializable {
ingo@2809: 
d@9706: 	private Integer id;
d@9706: 	private Integer year;
mschaefer@8975: 
d@9706: 	private String evaluationBy;
d@9706: 	private String description;
mschaefer@8975: 
d@9706: 	private River river;
gernotbelger@9246: 
d@9706: 	private BedHeightType type;
gernotbelger@9246: 
d@9706: 	private LocationSystem locationSystem;
gernotbelger@9246: 
d@9706: 	private ElevationModel curElevationModel;
gernotbelger@9246: 
d@9706: 	private ElevationModel oldElevationModel;
gernotbelger@9246: 
d@9706: 	private Range range;
d@9706: 
d@9706: 	private String sounding_width_info;
d@9706: 	private String notes;
d@9706: 
d@9706: 	private List<BedHeightValue> values;
d@9706: 
d@9706: 	public static BedHeight copyPojoFrom(BedHeight b, double from, double to) {
d@9706: 		List<BedHeightValue> values2 = b.getValues();
d@9706: 		List<BedHeightValue> copyValueList = new ArrayList<>();
d@9706: 
d@9706: 		BedHeight copy = new BedHeight(b.getRiver(), b.getYear(), b.getType(), b.getLocationSystem(),
d@9706: 				b.getCurElevationModel(), b.getOldElevationModel(), new Range(from, to, b.getRange().getRiver()),
d@9706: 				b.getEvaluationBy(), b.getDescription(), b.getSoundingWidthInfo(), b.getNotes());
d@9706: 
d@9706: 		for (BedHeightValue val : values2) {
d@9706: 			Double station = val.getStation();
d@9706: 			if (station >= from && station <= to)
d@9706: 				copyValueList.add(val);
d@9706: 		}
d@9706: 
d@9706: 		copy.setValues(copyValueList);
d@9706: 		return copy;
d@9706: 	}
d@9706: 
d@9706: 	public BedHeight() {
d@9706: 	}
d@9706: 
d@9706: 	public BedHeight(final River river, final Integer year, final BedHeightType type,
d@9706: 			final LocationSystem locationSystem, final ElevationModel curElevationModel, final Range range) {
d@9706: 		this(river, year, type, locationSystem, curElevationModel, null, range, null, null, null, null);
d@9706: 	}
d@9706: 
d@9706: 	public BedHeight(final River river, final Integer year, final BedHeightType type,
d@9706: 			final LocationSystem locationSystem, final ElevationModel curElevationModel,
d@9706: 			final ElevationModel oldElevationModel, final Range range, final String evaluationBy,
d@9706: 			final String description, final String sounding_width_info, final String notes) {
d@9706: 		this.river = river;
d@9706: 		this.year = year;
d@9706: 		this.type = type;
d@9706: 		this.locationSystem = locationSystem;
d@9706: 		this.curElevationModel = curElevationModel;
d@9706: 		this.oldElevationModel = oldElevationModel;
d@9706: 		this.range = range;
d@9706: 		this.evaluationBy = evaluationBy;
d@9706: 		this.description = description;
d@9706: 		this.sounding_width_info = sounding_width_info;
d@9706: 		this.notes = notes;
d@9706: 	}
d@9706: 
d@9706: 	@Id
d@9706: 	@SequenceGenerator(name = "SEQUENCE_BED_HEIGHT_ID_SEQ", sequenceName = "BED_HEIGHT_ID_SEQ", allocationSize = 1)
d@9706: 	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE_BED_HEIGHT_ID_SEQ")
d@9706: 	@Column(name = "id")
d@9706: 	public Integer getId() {
d@9706: 		return this.id;
d@9706: 	}
d@9706: 
d@9706: 	public void setId(final Integer id) {
d@9706: 		this.id = id;
d@9706: 	}
d@9706: 
d@9706: 	@OneToOne
d@9706: 	@JoinColumn(name = "river_id")
d@9706: 	public River getRiver() {
d@9706: 		return this.river;
d@9706: 	}
d@9706: 
d@9706: 	public void setRiver(final River river) {
d@9706: 		this.river = river;
d@9706: 	}
d@9706: 
d@9706: 	@Column(name = "year")
d@9706: 	public Integer getYear() {
d@9706: 		return this.year;
d@9706: 	}
d@9706: 
d@9706: 	public void setYear(final Integer year) {
d@9706: 		this.year = year;
d@9706: 	}
d@9706: 
d@9706: 	@OneToOne
d@9706: 	@JoinColumn(name = "type_id")
d@9706: 	public BedHeightType getType() {
d@9706: 		return this.type;
d@9706: 	}
d@9706: 
d@9706: 	public void setType(final BedHeightType type) {
d@9706: 		this.type = type;
d@9706: 	}
d@9706: 
d@9706: 	@OneToOne
d@9706: 	@JoinColumn(name = "location_system_id")
d@9706: 	public LocationSystem getLocationSystem() {
d@9706: 		return this.locationSystem;
d@9706: 	}
d@9706: 
d@9706: 	public void setLocationSystem(final LocationSystem locationSystem) {
d@9706: 		this.locationSystem = locationSystem;
d@9706: 	}
d@9706: 
d@9706: 	@OneToOne
d@9706: 	@JoinColumn(name = "cur_elevation_model_id")
d@9706: 	public ElevationModel getCurElevationModel() {
d@9706: 		return this.curElevationModel;
d@9706: 	}
d@9706: 
d@9706: 	public void setCurElevationModel(final ElevationModel curElevationModel) {
d@9706: 		this.curElevationModel = curElevationModel;
d@9706: 	}
d@9706: 
d@9706: 	@OneToOne
d@9706: 	@JoinColumn(name = "old_elevation_model_id")
d@9706: 	public ElevationModel getOldElevationModel() {
d@9706: 		return this.oldElevationModel;
d@9706: 	}
d@9706: 
d@9706: 	public void setOldElevationModel(final ElevationModel oldElevationModel) {
d@9706: 		this.oldElevationModel = oldElevationModel;
d@9706: 	}
d@9706: 
d@9706: 	@OneToOne
d@9706: 	@JoinColumn(name = "range_id")
d@9706: 	public Range getRange() {
d@9706: 		return this.range;
d@9706: 	}
d@9706: 
d@9706: 	public void setRange(final Range range) {
d@9706: 		this.range = range;
d@9706: 	}
d@9706: 
d@9706: 	@Column(name = "evaluation_by")
d@9706: 	public String getEvaluationBy() {
d@9706: 		return this.evaluationBy;
d@9706: 	}
d@9706: 
d@9706: 	public void setEvaluationBy(final String evaluationBy) {
d@9706: 		this.evaluationBy = evaluationBy;
d@9706: 	}
d@9706: 
d@9706: 	@Column(name = "description")
d@9706: 	public String getDescription() {
d@9706: 		return this.description;
d@9706: 	}
d@9706: 
d@9706: 	public void setDescription(final String description) {
d@9706: 		this.description = description;
d@9706: 	}
d@9706: 
d@9706: 	@Column(name = "sounding_width_info")
d@9706: 	public String getSoundingWidthInfo() {
d@9706: 		return this.sounding_width_info;
d@9706: 	}
d@9706: 
d@9706: 	public void setSoundingWidthInfo(final String sounding_width_info) {
d@9706: 		this.sounding_width_info = sounding_width_info;
d@9706: 	}
d@9706: 
d@9706: 	@Column(name = "notes")
d@9706: 	public String getNotes() {
d@9706: 		return this.notes;
d@9706: 	}
d@9706: 
d@9706: 	public void setNotes(final String notes) {
d@9706: 		this.notes = notes;
d@9706: 	}
d@9706: 
d@9706: 	@OneToMany
d@9706: 	@JoinColumn(name = "bed_height_id")
d@9706: 	public List<BedHeightValue> getValues() {
d@9706: 		return this.values;
d@9706: 	}
d@9706: 
d@9706: 	public void setValues(final List<BedHeightValue> values) {
d@9706: 		this.values = values;
d@9706: 	}
d@9706: 
d@9706: 	public static List<BedHeight> getBedHeights(final River river, final double kmLo, final double kmHi) {
d@9706: 		return getBedHeights(river, kmLo, kmHi, false);
d@9706: 	}
d@9706: 
d@9706: 	/**
d@9706: 	 * Fetch the soundings with values in a river km range, optionally only those
d@9706: 	 * that also have field 01 etc. values
d@9706: 	 */
d@9706: 	public static List<BedHeight> getBedHeights(final River river, final double startKm, final double endKm,
d@9706: 			final boolean withHeightFieldsOnly) {
d@9706: 
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 
d@9706: 		final String fieldsClause = withHeightFieldsOnly ? " AND (height01 IS NOT NULL)" : "";
d@9706: 		final Query query = session.createQuery(
d@9706: 				"FROM BedHeight" + " WHERE (river=:river)" + " AND (id IN (SELECT bedHeight.id FROM BedHeightValue"
d@9706: 						+ " WHERE (station BETWEEN :kmfrom - 0.0001 AND :kmto + 0.0001)" + fieldsClause
d@9706: 						+ " GROUP BY bed_height_id))");
d@9706: 		query.setParameter("river", river);
d@9706: 		query.setParameter("kmfrom", startKm);
d@9706: 		query.setParameter("kmto", endKm);
d@9706: 
d@9706: 		final List<BedHeight> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles : null;
d@9706: 	}
d@9706: 
d@9706: 	public static BedHeight getBedHeightById(final int id) {
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 
d@9706: 		final Query query = session.createQuery("from BedHeight where id=:id");
d@9706: 
d@9706: 		query.setParameter("id", id);
d@9706: 
d@9706: 		final List<BedHeight> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
d@9706: 	}
d@9706: 
d@9706: 	public static BedHeight getBedHeightByDescription(final River river, final String description, final double startKm,
d@9706: 			final double endKm) {
d@9706: 
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 
d@9706: 		final Query query = session
d@9706: 				.createQuery("FROM BedHeight" + " WHERE (TRIM(description)=:description) AND river=:river"
d@9706: 						+ " AND id IN (SELECT bedHeight.id FROM BedHeightValue"
d@9706: 						+ " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
d@9706: 		query.setParameter("river", river);
d@9706: 		query.setParameter("description", description);
d@9706: 		query.setParameter("kmfrom", startKm);
d@9706: 		query.setParameter("kmto", endKm);
d@9706: 
d@9706: 		final List<BedHeight> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
d@9706: 	}
d@9706: 
d@9706: 	public static List<BedHeight> getBedHeightEpochs(final River river, final double startKm, final double endKm) {
d@9706: 
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 		final String description = "epoch";
d@9706: 		final Query query = session.createQuery("FROM BedHeight" + " WHERE lower(description) LIKE :description AND "
d@9706: 				+ "river=:river" + " AND id IN (SELECT bedHeight.id FROM BedHeightValue"
d@9706: 				+ " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
d@9706: 		query.setParameter("river", river);
d@9706: 		query.setParameter("description", "%" + description + "%");
d@9706: 		query.setParameter("kmfrom", startKm);
d@9706: 		query.setParameter("kmto", endKm);
d@9706: 
d@9706: 		final List<BedHeight> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles : null;
d@9706: 	}
d@9706: 
d@9706: 	public static List<BedHeight> getBedHeightYear(final River river, final double startKm, final double endKm) {
d@9706: 
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 		final String description = "epoch";
d@9706: 		final Query query = session
d@9706: 				.createQuery("FROM BedHeight" + " WHERE lower(description) NOT LIKE :description AND " + "river=:river"
d@9706: 						+ " AND id IN (SELECT bedHeight.id FROM BedHeightValue"
d@9706: 						+ " WHERE station BETWEEN :kmfrom AND :kmto" + " GROUP BY bedHeight.id)");
d@9706: 		query.setParameter("river", river);
d@9706: 		query.setParameter("description", "%" + description + "%");
d@9706: 		query.setParameter("kmfrom", startKm);
d@9706: 		query.setParameter("kmto", endKm);
d@9706: 
d@9706: 		final List<BedHeight> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles : null;
d@9706: 	}
d@9706: 
d@9706: 	public static Range getRangeFromBedHeights(final BedHeight bh) {
d@9706: 		final List<Range> ranges = new ArrayList<>();
d@9706: 
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 
d@9706: 		final Query query = session.createQuery("FROM Range" + " WHERE id=:range_id)");
d@9706: 		query.setParameter("range_id", bh.getRange().getId());
d@9706: 
d@9706: 		final List<Range> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
d@9706: 	}
d@9706: 
d@9706: 	public static List<BedHeight> getBedHeightYearEpoch(final boolean isEpoch, final Integer year, final River river,
d@9706: 			final double lowerKm, final double upperKm) {
d@9706: 
d@9706: 		final Session session = SessionHolder.HOLDER.get();
d@9706: 		final String description = "epoch";
d@9706: 
d@9706: 		final StringBuilder builder = new StringBuilder();
d@9706: 		builder.append("FROM BedHeight");
d@9706: 		if (isEpoch) {
d@9706: 			builder.append(" WHERE lower(description) LIKE :description ");
d@9706: 		} else {
d@9706: 			builder.append(" WHERE lower(description) NOT LIKE :description ");
d@9706: 		}
d@9706: 		builder.append(" AND year =:year");
d@9706: 
d@9706: 		builder.append(
d@9706: 				" AND river=:river  AND id IN (SELECT bedHeight.id FROM BedHeightValue   WHERE station BETWEEN :kmfrom AND :kmto  GROUP BY bedHeight.id )");
d@9706: 		final Query query = session.createQuery(builder.toString());
d@9706: 		query.setParameter("river", river);
d@9706: 		query.setParameter("year", year);
d@9706: 		query.setParameter("description", "%" + description + "%");
d@9706: 		query.setParameter("kmfrom", lowerKm);
d@9706: 		query.setParameter("kmto", upperKm);
d@9706: 
d@9706: 		final List<BedHeight> singles = query.list();
d@9706: 
d@9706: 		return ((singles != null) && !singles.isEmpty()) ? singles : null;
d@9706: 	}
gernotbelger@9211: }