@Override
public List<Target> listOverlappingTargets(final Integer targetId, final Date startDate, final Date endDate, final Set<Kpi> kpis) {
    return executeOnTransaction(new IExecuteOnTransaction<List<Target>>() {
        @Override
        public List<Target> execute(Session session) throws Exception {
            Junction kpiJunction = Restrictions.disjunction();
            for (Kpi kpi : kpis) {
                kpiJunction.add(Restrictions.conjunction().add(Restrictions.eq("_kpi.sbiKpiKpiId.id", kpi.getId()))
                        .add(Restrictions.eq("_kpi.sbiKpiKpiId.version", kpi.getVersion())));
            }
            // DetachedCriteria kpis = DetachedCriteria.forClass(SbiKpiKpi.class)
            Criteria c = session.createCriteria(SbiKpiTarget.class);
            Junction d = Restrictions.disjunction();
            d.add(Restrictions.conjunction()
                    .add(Restrictions.disjunction().add(Restrictions.le("startValidity", startDate)).add(Restrictions.isNull("startValidity")))
                    .add(Restrictions.disjunction().add(Restrictions.ge("endValidity", startDate)).add(Restrictions.isNull("endValidity"))));
            d.add(Restrictions.conjunction()
                    .add(Restrictions.disjunction().add(Restrictions.le("startValidity", endDate)).add(Restrictions.isNull("startValidity")))
                    .add(Restrictions.disjunction().add(Restrictions.ge("endValidity", endDate)).add(Restrictions.isNull("endValidity"))));
            if (targetId != null) {
                c.createAlias("sbiKpiTargetValues", "_targetValues").add(d).createAlias("_targetValues.sbiKpiKpi", "_kpi").add(kpiJunction)
                        .add(Restrictions.ne("targetId", targetId));
            } else {
                c.createAlias("sbiKpiTargetValues", "_targetValues").add(d).createAlias("_targetValues.sbiKpiKpi", "_kpi").add(kpiJunction);
            }
            List<SbiKpiTarget> lst = c.list();
            List<Target> targetList = new ArrayList<>();
            for (SbiKpiTarget sbiKpiTarget : lst) {
                targetList.add(from(sbiKpiTarget, false));
            }
            return targetList;
        }
    });
}
 

private static boolean applyCategoryCriterion(Object criteriaJunction, CategoryCriterion categoryCriterion) {
	Criterion restriction = getCategoryCriterionRestriction(categoryCriterion);
	if (restriction != null) {
		if (criteriaJunction instanceof Criteria) {
			((Criteria) criteriaJunction).add(restriction);
			return true;
		} else if (criteriaJunction instanceof Junction) {
			((Junction) criteriaJunction).add(restriction);
			return true;
		}
	}
	return false;
}
 

private static void applyCategoryCriterions(Criteria criteria, Junction junction, CategoryCriterion... categoryCriterions) {
	if (criteria != null && junction != null && categoryCriterions != null) {
		if (categoryCriterions.length > 0) {
			boolean applied = false;
			for (int i = 0; i < categoryCriterions.length; i++) {
				applied |= applyCategoryCriterion(junction, categoryCriterions[i]);
			}
			if (applied) {
				criteria.add(junction);
			}
		}
	}
}
 

private static LinkedHashMap<String, Projection> applyExpandDateModeCriterions(org.hibernate.Criteria visitScheduleItemCriteria, Long probandId, Timestamp from, Timestamp to,
		org.hibernate.criterion.Criterion or) {
	// cartesian product <visitscheduleitems> x <start tag values> x <stop tag values>
	org.hibernate.Criteria startTagValuesCriteria = visitScheduleItemCriteria.createCriteria("startTag", CriteriaSpecification.LEFT_JOIN)
			.createCriteria("tagValues", "startTagValues", CriteriaSpecification.LEFT_JOIN);
	org.hibernate.Criteria startTagValuesValueCriteria = startTagValuesCriteria.createCriteria("value",
			CriteriaSpecification.LEFT_JOIN);
	org.hibernate.Criteria stopTagValuesCriteria = visitScheduleItemCriteria.createCriteria("stopTag", CriteriaSpecification.LEFT_JOIN).createCriteria("tagValues",
			"stopTagValues", CriteriaSpecification.LEFT_JOIN);
	org.hibernate.Criteria stopTagValuesValueCriteria = stopTagValuesCriteria.createCriteria("value", CriteriaSpecification.LEFT_JOIN);
	// from the cross product, remove those with start+stop values of different probands. also include rows without existing stop tag values
	visitScheduleItemCriteria.add(CriteriaUtil.applyOr(
			Restrictions.or(Restrictions.eqProperty("startTagValues.listEntry.id", "stopTagValues.listEntry.id"), Restrictions.isNull("stopTagValues.listEntry.id")), or));
	// narrow to particular proband, if given
	org.hibernate.Criteria startTagValuesListEntryCriteria = startTagValuesCriteria.createCriteria("listEntry", "startTagValuesListEntry", CriteriaSpecification.LEFT_JOIN);
	if (probandId != null) {
		startTagValuesListEntryCriteria.add(CriteriaUtil.applyOr(Restrictions.or(Restrictions.isNull("proband.id"), Restrictions.eq("proband.id", probandId.longValue())), or));
	}
	// only rows with proband group matching the group of the visitschelute item (or those with no group)
	visitScheduleItemCriteria
			.add(CriteriaUtil.applyOr(Restrictions.or(Restrictions.isNull("startTagValuesListEntry.id"),
					Restrictions.or(Restrictions.eqProperty("startTagValuesListEntry.group.id", "group.id"), Restrictions.isNull("group.id"))), or));
	CriteriaQueryTranslator translator = CriteriaUtil.getCriteriaQueryTranslator(visitScheduleItemCriteria);
	// prepare sql fragments:
	String offsetSql = translator.getColumn(visitScheduleItemCriteria, "offsetSeconds");
	String durationSql = translator.getColumn(visitScheduleItemCriteria, "duration");
	String tagStartSql = translator.getColumn(startTagValuesValueCriteria, "timestampValue");
	String tagStopSql = translator.getColumn(stopTagValuesValueCriteria, "timestampValue");
	String tagStartOffsetSql = MessageFormat.format(Settings.getString(SettingCodes.SQL_ADD_SECONDS_TERM, Bundle.SETTINGS, null),
			tagStartSql, offsetSql);
	String tagStopOffsetSql = MessageFormat.format(Settings.getString(SettingCodes.SQL_ADD_SECONDS_TERM, Bundle.SETTINGS, null),
			tagStopSql, offsetSql);
	String durationStopOffsetSql = MessageFormat.format(Settings.getString(SettingCodes.SQL_ADD_SECONDS_TERM, Bundle.SETTINGS, null),
			tagStartOffsetSql, durationSql);
	String probandIdSql = translator.getColumn(startTagValuesListEntryCriteria, "proband.id");
	// date filtering
	Junction junction = Restrictions.disjunction();
	if (or != null) {
		junction.add(or);
	}
	if (from != null || to != null) {
		junction.add(Restrictions.and(Restrictions.eq("mode", VisitScheduleDateMode.STATIC), CriteriaUtil.getClosedIntervalCriterion(from, to, null)));
		junction.add(Restrictions.and(Restrictions.eq("mode", VisitScheduleDateMode.TAGS),
				Restrictions.and(Restrictions.sqlRestriction("(" + tagStartSql + ") < (" + tagStopSql + ")"),
						CriteriaUtil.getClosedIntervalCriterion(from, to, null, tagStartOffsetSql, tagStopOffsetSql))));
		junction.add(
				Restrictions.and(Restrictions.eq("mode", VisitScheduleDateMode.TAG_DURATION), Restrictions.and(Restrictions.sqlRestriction("(" + tagStartSql + ") is not null"),
						CriteriaUtil.getClosedIntervalCriterion(from, to, null, tagStartOffsetSql, durationStopOffsetSql))));
		visitScheduleItemCriteria.add(junction);
	} else {
		junction.add(Restrictions.eq("mode", VisitScheduleDateMode.STATIC));
		junction.add(Restrictions.and(Restrictions.eq("mode", VisitScheduleDateMode.TAGS), Restrictions.sqlRestriction("(" + tagStartSql + ") < (" + tagStopSql + ")")));
		junction.add(Restrictions.and(Restrictions.eq("mode", VisitScheduleDateMode.TAG_DURATION), Restrictions.sqlRestriction("(" + tagStartSql + ") is not null")));
	}
	visitScheduleItemCriteria.add(junction); //no stales any more
	LinkedHashMap<String, Projection> sqlColumns = new LinkedHashMap<String, Projection>();
	sqlColumns.put("tagStart", new SQLProjection(
			tagStartOffsetSql + " as tagStart",
			new String[] { "tagStart" },
			new org.hibernate.type.Type[] { Hibernate.TIMESTAMP },
			tagStartOffsetSql));
	sqlColumns.put("tagStop", new SQLProjection(
			tagStopOffsetSql + " as tagStop",
			new String[] { "tagStop" },
			new org.hibernate.type.Type[] { Hibernate.TIMESTAMP },
			tagStopOffsetSql));
	sqlColumns.put("durationStop", new SQLProjection(
			durationStopOffsetSql + " as durationStop",
			new String[] { "durationStop" },
			new org.hibernate.type.Type[] { Hibernate.TIMESTAMP },
			durationStopOffsetSql));
	sqlColumns.put("probandId", new SQLProjection(
			probandIdSql + " as probandId",
			new String[] { "probandId" },
			new org.hibernate.type.Type[] { Hibernate.LONG },
			probandIdSql));
	return sqlColumns;
}