/**
 * Returns the latest run from the specified bunch
 * @param bunchName the bunch name
 * @return the latest run or null if none exists
 */
public static TaskoRun getLatestRun(String bunchName) {
    DetachedCriteria bunchIds = DetachedCriteria.forClass(TaskoBunch.class)
            .add(Restrictions.eq("name", bunchName))
            .setProjection(Projections.id());

    DetachedCriteria templateIds = DetachedCriteria.forClass(TaskoTemplate.class)
            .add(Subqueries.propertyIn("bunch", bunchIds))
            .setProjection(Projections.id());

    return (TaskoRun) getSession()
        .createCriteria(TaskoRun.class)
        .add(Subqueries.propertyIn("template.id", templateIds))
        .add(Restrictions.in("status",
                new Object[] {
                        TaskoRun.STATUS_RUNNING,
                        TaskoRun.STATUS_FINISHED,
                        TaskoRun.STATUS_INTERRUPTED
        }))
        .addOrder(Order.desc("startTime"))
        .addOrder(Order.desc("id"))
        .setFirstResult(0)
        .setMaxResults(1)
        .uniqueResult();
}
 

@Override
protected long handleGetCount(Long trialId, Long probandListStatusTypeId, Boolean locked, Long resendProbandId) throws Exception {
	org.hibernate.Criteria massMailCriteria = createMassMailCriteria("massMail0");
	if (trialId != null) {
		massMailCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (probandListStatusTypeId != null) {
		massMailCriteria.add(Restrictions.eq("probandListStatus.id", probandListStatusTypeId.longValue()));
	}
	if (locked != null) {
		massMailCriteria.createCriteria("status").add(Restrictions.eq("locked", locked.booleanValue()));
	}
	if (resendProbandId != null) {
		DetachedCriteria recipientsSubQuery = DetachedCriteria.forClass(MassMailRecipientImpl.class, "massMailRecipient1"); // IMPL!!!!
		recipientsSubQuery.setProjection(Projections.rowCount());
		recipientsSubQuery.add(Restrictions.eq("proband.id", resendProbandId.longValue()));
		recipientsSubQuery.add(Restrictions.eqProperty("massMail.id", "massMail0.id"));
		massMailCriteria.add(Restrictions.or(Restrictions.eq("probandListStatusResend", true),
				Subqueries.eq(0l, recipientsSubQuery)));
	}
	return (Long) massMailCriteria.setProjection(Projections.rowCount()).uniqueResult();
}
 

private org.hibernate.Criteria[] createEcrfFieldCriteria(Long probandListEntryId, Long ecrfId) {
	org.hibernate.Criteria ecrfFieldCriteria = this.getSession().createCriteria(ECRFField.class, ServiceUtil.ECRF_FIELD_VALUE_DAO_ECRF_FIELD_ALIAS);
	ecrfFieldCriteria.add(Restrictions.eq("ecrf.id", ecrfId.longValue()));
	org.hibernate.Criteria ecrfFieldValueCriteria = ecrfFieldCriteria.createCriteria("fieldValues", ServiceUtil.ECRF_FIELD_VALUE_DAO_ECRF_FIELD_VALUE_ALIAS,
			CriteriaSpecification.LEFT_JOIN,
			Restrictions.eq(ServiceUtil.ECRF_FIELD_VALUE_DAO_ECRF_FIELD_VALUE_ALIAS + ".listEntry.id", probandListEntryId.longValue()));
	// correlated - slow:
	DetachedCriteria subQuery = createEcrfFieldValueDetachedCriteriaMaxId(ecrfFieldValueCriteria, ecrfFieldCriteria, null, probandListEntryId, null);
	subQuery.add(Restrictions.or(Restrictions.isNull("index"),
			Restrictions.eqProperty("index", ServiceUtil.ECRF_FIELD_VALUE_DAO_ECRF_FIELD_VALUE_ALIAS + ".index")));
	ecrfFieldValueCriteria.add(Restrictions.or(
			Restrictions.isNull("listEntry"),
			Restrictions.and(
					Restrictions.eq("listEntry.id", probandListEntryId.longValue()),
					Subqueries.propertyIn("id", subQuery))));
	return new org.hibernate.Criteria[] { ecrfFieldCriteria, ecrfFieldValueCriteria };
}
 

@Override
protected Collection<Trial> handleFindByEcrfs(Long departmentId, boolean withChargeOnly, PSFVO psf) throws Exception {
	org.hibernate.Criteria trialCriteria = createTrialCriteria("trial0");
	SubCriteriaMap criteriaMap = new SubCriteriaMap(Trial.class, trialCriteria);
	if (departmentId != null) {
		trialCriteria.add(Restrictions.eq("department.id", departmentId.longValue()));
	}
	DetachedCriteria subQuery = DetachedCriteria.forClass(ECRFImpl.class, "ecrf"); // IMPL!!!!
	subQuery.setProjection(Projections.rowCount());
	subQuery.add(Restrictions.eqProperty("trial.id", "trial0.id"));
	if (withChargeOnly) {
		subQuery.add(Restrictions.gt("charge", 0.0f));
	}
	trialCriteria.add(Subqueries.lt(0l, subQuery));
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	return trialCriteria.list();
}
 

@Override
protected Collection<Trial> handleFindBySignup(Long departmentId, boolean ignoreSignupInquiries, PSFVO psf) throws Exception {
	org.hibernate.Criteria trialCriteria = createTrialCriteria("trial0");
	SubCriteriaMap criteriaMap = new SubCriteriaMap(Trial.class, trialCriteria);
	if (departmentId != null) {
		trialCriteria.add(Restrictions.eq("department.id", departmentId.longValue()));
	}
	org.hibernate.Criteria statusCriteria = trialCriteria.createCriteria("status", "trialStatus", CriteriaSpecification.INNER_JOIN);
	statusCriteria.add(Restrictions.eq("lockdown", false));
	DetachedCriteria subQuery = DetachedCriteria.forClass(InquiryImpl.class, "inquiry"); // IMPL!!!!
	subQuery.setProjection(Projections.rowCount());
	subQuery.add(Restrictions.eqProperty("trial.id", "trial0.id"));
	subQuery.add(Restrictions.eq("activeSignup", true));
	trialCriteria.add(
			Restrictions.or(
					Restrictions.eq("signupProbandList", true),
					Restrictions.and(
							ignoreSignupInquiries ? Subqueries.lt(0l, subQuery)
									: Restrictions.and(
											Restrictions.eq("signupInquiries", true),
											Subqueries.lt(0l, subQuery)),
							Restrictions.eq("trialStatus.inquiryValueInputEnabled", true))));
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	return trialCriteria.list();
}
 

@Override
public List<Alias> listAliasNotInMeasure(final Integer ruleId, final Integer ruleVersion) {
    return executeOnTransaction(new IExecuteOnTransaction<List<Alias>>() {
        @Override
        public List<Alias> execute(Session session) throws Exception {
            DetachedCriteria dc = DetachedCriteria.forClass(SbiKpiRuleOutput.class).createAlias("type", "_type").createAlias("sbiKpiRule", "_sbiKpiRule")
                    .createAlias("sbiKpiAlias", "_sbiKpiAlias").add(Restrictions.eq("_type.valueCd", MEASURE))
                    .add(Restrictions.eq("_sbiKpiRule.active", 'T')).setProjection(Property.forName("_sbiKpiAlias.id"));

            // Retriving all aliases not used as measure
            List<SbiKpiAlias> alias = session.createCriteria(SbiKpiAlias.class).add(Subqueries.propertyNotIn("id", dc)).list();
            Set<SbiKpiAlias> sbiAlias = new HashSet<>(alias);
            if (ruleId != null && ruleVersion != null) {
                List<SbiKpiAlias> aliasesUsedByCurrentRule = session.createCriteria(SbiKpiAlias.class)
                        .createAlias("sbiKpiRuleOutputs", "_sbiKpiRuleOutputs").createAlias("_sbiKpiRuleOutputs.sbiKpiRule", "_sbiKpiRule")
                        .add(Restrictions.eq("_sbiKpiRule.sbiKpiRuleId.id", ruleId)).add(Restrictions.eq("_sbiKpiRule.sbiKpiRuleId.version", ruleVersion))
                        .list();
                sbiAlias.addAll(aliasesUsedByCurrentRule);
            }

            List<Alias> ret = new ArrayList<>();
            for (SbiKpiAlias sbiKpiAlias : sbiAlias) {
                ret.add(new Alias(sbiKpiAlias.getId(), sbiKpiAlias.getName()));
            }
            return ret;
        }
    });
}
 

@Override
public List<Alias> listAliasInMeasure(final Integer ruleId, final Integer ruleVersion) {
    return executeOnTransaction(new IExecuteOnTransaction<List<Alias>>() {
        @Override
        public List<Alias> execute(Session session) throws Exception {
            DetachedCriteria dc = DetachedCriteria.forClass(SbiKpiRuleOutput.class).createAlias("type", "_type").createAlias("sbiKpiRule", "_sbiKpiRule")
                    .createAlias("sbiKpiAlias", "_sbiKpiAlias").add(Restrictions.eq("_type.valueCd", MEASURE))
                    .add(Restrictions.eq("_sbiKpiRule.active", 'T')).setProjection(Property.forName("_sbiKpiAlias.id"));

            // Retriving all aliases not used as measure
            List<SbiKpiAlias> alias = session.createCriteria(SbiKpiAlias.class).add(Subqueries.propertyIn("id", dc)).list();
            Set<SbiKpiAlias> sbiAlias = new HashSet<>(alias);
            if (ruleId != null && ruleVersion != null) {
                List<SbiKpiAlias> aliasesUsedByCurrentRule = session.createCriteria(SbiKpiAlias.class)
                        .createAlias("sbiKpiRuleOutputs", "_sbiKpiRuleOutputs").createAlias("_sbiKpiRuleOutputs.sbiKpiRule", "_sbiKpiRule")
                        .add(Restrictions.eq("_sbiKpiRule.sbiKpiRuleId.id", ruleId)).add(Restrictions.eq("_sbiKpiRule.sbiKpiRuleId.version", ruleVersion))
                        .list();
                sbiAlias.removeAll(aliasesUsedByCurrentRule);
            }

            List<Alias> ret = new ArrayList<>();
            for (SbiKpiAlias sbiKpiAlias : sbiAlias) {
                ret.add(new Alias(sbiKpiAlias.getId(), sbiKpiAlias.getName()));
            }
            return ret;
        }
    });
}
 

@Override
protected Collection<Password> handleFindExpiring(Date today,
		Long departmentId, AuthenticationType authMethod, VariablePeriod reminderPeriod,
		Long reminderPeriodDays, Boolean notify, boolean includeAlreadyPassed, PSFVO psf) throws Exception {
	org.hibernate.Criteria passwordCriteria = createPasswordCriteria("password0");
	SubCriteriaMap criteriaMap = new SubCriteriaMap(Password.class, passwordCriteria);
	if (departmentId != null) {
		criteriaMap.createCriteria("user").add(Restrictions.eq("department.id", departmentId.longValue()));
	}
	if (authMethod != null) {
		criteriaMap.createCriteria("user").add(Restrictions.eq("authMethod", authMethod));
	}
	DetachedCriteria subQuery = DetachedCriteria.forClass(PasswordImpl.class, "password1"); // IMPL!!!!
	subQuery.add(Restrictions.eqProperty("password1.user", "password0.user"));
	subQuery.setProjection(Projections.max("id"));
	passwordCriteria.add(Subqueries.propertyEq("id", subQuery));
	passwordCriteria.add(Restrictions.eq("expires", true)); // performance only...
	if (notify != null) {
		passwordCriteria.add(Restrictions.eq("prolongable", notify.booleanValue())); // performance only...
	}
	if (psf != null) {
		PSFVO sorterFilter = new PSFVO();
		sorterFilter.setFilters(psf.getFilters());
		sorterFilter.setSortField(psf.getSortField());
		sorterFilter.setSortOrder(psf.getSortOrder());
		CriteriaUtil.applyPSFVO(criteriaMap, sorterFilter);
	}
	ArrayList<Password> resultSet = CriteriaUtil.listExpirations(passwordCriteria, today, notify, includeAlreadyPassed, null, null, reminderPeriod, reminderPeriodDays);
	return CriteriaUtil.applyPVO(resultSet, psf, false); // no dupes by default
}
 

@Override
protected Collection<ProbandListEntry> handleGetProbandList(
		Long trialId, org.phoenixctms.ctsms.enumeration.ProbandListStatusLogLevel logLevel, boolean last)
		throws Exception {
	// http://stackoverflow.com/questions/1648426/hibernate-detached-queries-as-a-part-of-the-criteria-query
	// https://forum.hibernate.org/viewtopic.php?p=2317841#2317841
	org.hibernate.Criteria listEntryCriteria = createListEntryCriteria();
	boolean distinctRoot = false;
	if (trialId != null) {
		listEntryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (logLevel != null) {
		org.hibernate.Criteria statusEntryCriteria;
		if (last) {
			statusEntryCriteria = listEntryCriteria.createCriteria("statusEntries", "probandListStatusEntry0", CriteriaSpecification.INNER_JOIN);
		} else {
			statusEntryCriteria = listEntryCriteria.createCriteria("statusEntries", CriteriaSpecification.INNER_JOIN);
		}
		org.hibernate.Criteria statusTypeCriteria = statusEntryCriteria.createCriteria("status", CriteriaSpecification.INNER_JOIN);
		org.hibernate.Criteria logLevelCriteria = statusTypeCriteria.createCriteria("logLevels", CriteriaSpecification.INNER_JOIN);
		logLevelCriteria.add(Restrictions.eq("logLevel", logLevel));
		if (last) {
			DetachedCriteria subQuery = DetachedCriteria.forClass(ProbandListStatusEntryImpl.class, "probandListStatusEntry1"); // IMPL!!!!
			subQuery.add(Restrictions.eqProperty("probandListStatusEntry1.listEntry", "probandListStatusEntry0.listEntry"));
			subQuery.setProjection(Projections.max("id"));
			statusEntryCriteria.add(Subqueries.propertyEq("id", subQuery));
		} else {
			distinctRoot = true;
		}
	}
	listEntryCriteria.addOrder(Order.asc("trial"));
	listEntryCriteria.addOrder(Order.asc("position"));
	if (distinctRoot) {
		return CriteriaUtil.listDistinctRoot(listEntryCriteria, this, "trial.id", "position");
	} else {
		return listEntryCriteria.list();
	}
}
 

private static void applyEcrfFieldStatusEntryMaxIdSubCriteria(org.hibernate.Criteria ecrfFieldStatusEntryCriteria, org.hibernate.Criteria ecrfFieldCriteria,
		org.hibernate.Criteria probandListEntryCriteria, ECRFFieldStatusQueue queue, Long probandListEntryId, Long ecrfFieldId) {
	DetachedCriteria subQuery = createEcrfFieldStatusEntryDetachedCriteriaMaxId(ecrfFieldStatusEntryCriteria, ecrfFieldCriteria, probandListEntryCriteria, queue,
			probandListEntryId,
			ecrfFieldId);
	subQuery.add(Restrictions.or(Restrictions.isNull("index"),
			Restrictions.eqProperty("index", ecrfFieldStatusEntryCriteria.getAlias() + ".index")));
	ecrfFieldStatusEntryCriteria.add(Subqueries.propertyIn("id", subQuery));
}
 

private static void applyEcrfFieldStatusEntryMaxIdSubCriteria(org.hibernate.Criteria ecrfFieldStatusEntryCriteria, org.hibernate.Criteria ecrfFieldCriteria,
		org.hibernate.Criteria probandListEntryCriteria, ECRFFieldStatusQueue queue, Long probandListEntryId, Long ecrfFieldId, Long index) {
	DetachedCriteria subQuery = createEcrfFieldStatusEntryDetachedCriteriaMaxId(ecrfFieldStatusEntryCriteria, ecrfFieldCriteria, probandListEntryCriteria, queue,
			probandListEntryId,
			ecrfFieldId);
	if (index != null) {
		subQuery.add(Restrictions.eq("index", index.longValue()));
	} else {
		subQuery.add(Restrictions.isNull("index"));
	}
	ecrfFieldStatusEntryCriteria.add(Subqueries.propertyIn("id", subQuery));
}
 

@Override
protected Collection<Permission> handleFindByServiceMethodUser(
		String serviceMethod, Long userId, Boolean profilePermissionActive,
		Boolean userPermissionProfileActive) throws Exception {
	org.hibernate.Criteria permissionCritria = createPermissionCriteria();
	if (serviceMethod != null) {
		permissionCritria.add(Restrictions.eq("serviceMethod", serviceMethod));
	}
	if (userId != null || profilePermissionActive != null || userPermissionProfileActive != null) {
		org.hibernate.Criteria profilePermissionCritria = permissionCritria.createCriteria("profilePermissions", CriteriaSpecification.LEFT_JOIN);
		if (profilePermissionActive != null) {
			profilePermissionCritria.add(Restrictions.eq("active", profilePermissionActive.booleanValue()));
		}
		if (userId != null || userPermissionProfileActive != null) {
			DetachedCriteria subQuery = DetachedCriteria.forClass(UserPermissionProfileImpl.class, "userPermissionProfile"); // IMPL!!!!
			subQuery.setProjection(Projections.projectionList().add(Projections.property("profile")));
			if (userId != null) {
				subQuery.add(Restrictions.eq("user.id", userId.longValue()));
			}
			if (userPermissionProfileActive != null) {
				subQuery.add(Restrictions.eq("active", userPermissionProfileActive.booleanValue()));
			}
			profilePermissionCritria.add(Subqueries.propertyIn("profile", subQuery));
		}
	}
	return permissionCritria.list();
}
 

@Override
protected Collection<Proband> handleFindByMoneyTransferNoParticipation(Long trialId,
		PaymentMethod method, String costType, Boolean paid, boolean total,
		Boolean person, PSFVO psf)
		throws Exception {
	org.hibernate.Criteria probandCriteria = createProbandCriteria("proband0");
	if (person != null) {
		probandCriteria.add(Restrictions.eq("person", person.booleanValue()));
	}
	SubCriteriaMap criteriaMap = new SubCriteriaMap(Proband.class, probandCriteria);
	Criteria moneyTransferCriteria = criteriaMap.createCriteria("moneyTransfers");
	moneyTransferCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	if (method != null) {
		moneyTransferCriteria.add(Restrictions.eq("method", method));
	}
	if (paid != null) {
		moneyTransferCriteria.add(Restrictions.eq("paid", paid.booleanValue()));
	}
	CategoryCriterion.apply(moneyTransferCriteria, new CategoryCriterion(costType, "costType", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	DetachedCriteria subQuery = DetachedCriteria.forClass(ProbandListEntryImpl.class, "probandListEntry"); // IMPL!!!!
	subQuery.setProjection(Projections.rowCount());
	subQuery.add(Restrictions.eqProperty("proband.id", "proband0.id"));
	subQuery.add(Restrictions.eq("trial.id", trialId.longValue()));
	if (!total) {
		subQuery.createCriteria("lastStatus", CriteriaSpecification.INNER_JOIN).createCriteria("status", CriteriaSpecification.INNER_JOIN)
				.add(Restrictions.eq("count", true));
	}
	probandCriteria.add(Subqueries.eq(0l, subQuery));
	return CriteriaUtil.listDistinctRootPSFVO(criteriaMap, psf, this);
}
 

private static void applyEcrfFieldValueMaxIdSubCriteria(org.hibernate.Criteria ecrfFieldValueCriteria, org.hibernate.Criteria ecrfFieldCriteria,
		org.hibernate.Criteria probandListEntryCriteria, Long probandListEntryId, Long ecrfFieldId) {
	DetachedCriteria subQuery = createEcrfFieldValueDetachedCriteriaMaxId(ecrfFieldValueCriteria, ecrfFieldCriteria, probandListEntryCriteria, probandListEntryId, ecrfFieldId);
	subQuery.add(Restrictions.or(Restrictions.isNull("index"),
			Restrictions.eqProperty("index", ecrfFieldValueCriteria.getAlias() + ".index")));
	ecrfFieldValueCriteria.add(Subqueries.propertyEq("id", subQuery));
}
 

private static void applyEcrfFieldValueMaxIdSubCriteria(org.hibernate.Criteria ecrfFieldValueCriteria, org.hibernate.Criteria ecrfFieldCriteria,
		org.hibernate.Criteria probandListEntryCriteria, Long probandListEntryId, Long ecrfFieldId, Long index) {
	DetachedCriteria subQuery = createEcrfFieldValueDetachedCriteriaMaxId(ecrfFieldValueCriteria, ecrfFieldCriteria, probandListEntryCriteria, probandListEntryId, ecrfFieldId);
	if (index != null) {
		subQuery.add(Restrictions.eq("index", index.longValue()));
	} else {
		subQuery.add(Restrictions.isNull("index"));
	}
	ecrfFieldValueCriteria.add(Subqueries.propertyEq("id", subQuery));
}
 

@Override
protected Collection<ECRFFieldValue> handleGetLog(Long trialId, Long probandListEntryId, Long ecrfId, boolean sort, PSFVO psf) throws Exception {
	org.hibernate.Criteria ecrfFieldValueCriteria = createEcrfFieldValueCriteria("ecrfFieldValue0");
	org.hibernate.Criteria listEntryCriteria = ecrfFieldValueCriteria.createCriteria("listEntry", "probandListEntry0");
	if (trialId != null || probandListEntryId != null) {
		if (trialId != null) {
			listEntryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
		}
		if (probandListEntryId != null) {
			listEntryCriteria.add(Restrictions.idEq(probandListEntryId.longValue()));
		}
	}
	org.hibernate.Criteria ecrfFieldCriteria = ecrfFieldValueCriteria.createCriteria("ecrfField", "ecrfField0");
	if (ecrfId != null) {
		ecrfFieldCriteria.add(Restrictions.eq("ecrf.id", ecrfId.longValue()));
	}
	DetachedCriteria subQuery = createEcrfFieldValueDetachedCriteria(ecrfFieldValueCriteria, ecrfFieldCriteria, listEntryCriteria, probandListEntryId, null);
	subQuery.setProjection(Projections.rowCount());
	subQuery.add(Restrictions.or(Restrictions.isNull("index"),
			Restrictions.eqProperty("index", "ecrfFieldValue0" + ".index")));
	ecrfFieldValueCriteria.add(Subqueries.lt(1l, subQuery));
	SubCriteriaMap criteriaMap = new SubCriteriaMap(ECRFFieldValue.class, ecrfFieldValueCriteria);
	criteriaMap.registerCriteria("listEntry", listEntryCriteria);
	criteriaMap.registerCriteria("ecrfField", ecrfFieldCriteria);
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	if (sort) {
		applySortOrders(listEntryCriteria, ecrfFieldCriteria, ecrfFieldValueCriteria);
	}
	return ecrfFieldValueCriteria.list();
}
 

@Override
protected Collection<ECRFFieldValue> handleGetLog(Long trialId, Long probandListEntryId, Long ecrfId, String section, boolean sort, PSFVO psf) throws Exception {
	org.hibernate.Criteria ecrfFieldValueCriteria = createEcrfFieldValueCriteria("ecrfFieldValue0");
	org.hibernate.Criteria listEntryCriteria = ecrfFieldValueCriteria.createCriteria("listEntry", "probandListEntry0");
	if (trialId != null || probandListEntryId != null) {
		if (trialId != null) {
			listEntryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
		}
		if (probandListEntryId != null) {
			listEntryCriteria.add(Restrictions.idEq(probandListEntryId.longValue()));
		}
	}
	org.hibernate.Criteria ecrfFieldCriteria = ecrfFieldValueCriteria.createCriteria("ecrfField", "ecrfField0");
	if (ecrfId != null) {
		ecrfFieldCriteria.add(Restrictions.eq("ecrf.id", ecrfId.longValue()));
	}
	if (section != null && section.length() > 0) {
		ecrfFieldCriteria.add(Restrictions.eq("section", section));
	} else {
		ecrfFieldCriteria.add(Restrictions.or(Restrictions.eq("section", ""), Restrictions.isNull("section")));
	}
	DetachedCriteria subQuery = createEcrfFieldValueDetachedCriteria(ecrfFieldValueCriteria, ecrfFieldCriteria, listEntryCriteria, probandListEntryId, null);
	subQuery.setProjection(Projections.rowCount());
	subQuery.add(Restrictions.or(Restrictions.isNull("index"),
			Restrictions.eqProperty("index", "ecrfFieldValue0" + ".index")));
	ecrfFieldValueCriteria.add(Subqueries.lt(1l, subQuery));
	SubCriteriaMap criteriaMap = new SubCriteriaMap(ECRFFieldValue.class, ecrfFieldValueCriteria);
	criteriaMap.registerCriteria("listEntry", listEntryCriteria);
	criteriaMap.registerCriteria("ecrfField", ecrfFieldCriteria);
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	if (sort) {
		applySortOrders(listEntryCriteria, ecrfFieldCriteria, ecrfFieldValueCriteria);
	}
	return ecrfFieldValueCriteria.list();
}
 

@Override
protected Collection<Trial> handleFindByInquiryValuesProbandSorted(Long departmentId, Long probandId, Boolean active, Boolean activeSignup) throws Exception {
	org.hibernate.Criteria trialCriteria = createTrialCriteria("trial0");
	if (departmentId != null) {
		trialCriteria.add(Restrictions.eq("department.id", departmentId.longValue()));
	}
	org.hibernate.Criteria statusCriteria = trialCriteria.createCriteria("status", "trialStatus", CriteriaSpecification.INNER_JOIN);
	DetachedCriteria valuesSubQuery = DetachedCriteria.forClass(InquiryValueImpl.class, "inquiryValue"); // IMPL!!!!
	valuesSubQuery.setProjection(Projections.rowCount());
	valuesSubQuery.add(Restrictions.eq("proband.id", probandId.longValue()));
	DetachedCriteria inquiriesSubQuery = valuesSubQuery.createCriteria("inquiry", "inquiry0", CriteriaSpecification.INNER_JOIN);
	inquiriesSubQuery.add(Restrictions.eqProperty("trial.id", "trial0.id"));
	if (active != null) {
		inquiriesSubQuery.add(Restrictions.eq("active", active.booleanValue()));
	}
	if (activeSignup != null) {
		inquiriesSubQuery.add(Restrictions.eq("activeSignup", activeSignup.booleanValue()));
	}
	inquiriesSubQuery = DetachedCriteria.forClass(InquiryImpl.class, "inquiry1"); // IMPL!!!!
	inquiriesSubQuery.setProjection(Projections.rowCount());
	inquiriesSubQuery.add(Restrictions.eqProperty("trial.id", "trial0.id"));
	if (active != null) {
		inquiriesSubQuery.add(Restrictions.eq("active", active.booleanValue()));
	}
	if (activeSignup != null) {
		inquiriesSubQuery.add(Restrictions.eq("activeSignup", activeSignup.booleanValue()));
	}
	trialCriteria.add(
			Restrictions.or(
					Subqueries.lt(0l, valuesSubQuery),
					Restrictions.and(
							Subqueries.lt(0l, inquiriesSubQuery),
							Restrictions.and(
									Restrictions.eq("trialStatus.inquiryValueInputEnabled", true),
									Restrictions.eq("trialStatus.lockdown", false)))));
	trialCriteria.addOrder(Order.asc("name"));
	return trialCriteria.list();
}
 

@Override
@SuppressWarnings( "unchecked" )
public int count( Query query )
{
    Schema schema = query.getSchema();
    InternalHibernateGenericStore<?> store = getStore( (Class<? extends IdentifiableObject>) schema.getKlass() );

    if ( store == null )
    {
        return 0;
    }

    if ( query.getUser() == null )
    {
        query.setUser( currentUserService.getCurrentUser() );
    }

    if ( !query.isPlannedQuery() )
    {
        QueryPlan queryPlan = queryPlanner.planQuery( query, true );
        query = queryPlan.getPersistedQuery();
    }

    DetachedCriteria detachedCriteria = buildCriteria( store.getSharingDetachedCriteria( query.getUser() ), query );
    Criteria criteria = store.getCriteria();

    if ( criteria == null )
    {
        return 0;
    }

    return ((Number) criteria.add( Subqueries.propertyIn( "id", detachedCriteria ) )
        .setProjection( Projections.countDistinct( "id" ) )
        .uniqueResult()).intValue();
}
 

/**
 * Looks up a proxy server by hostname.
 * @param name the hostname
 * @return the server, if it is found
 */
@SuppressWarnings("unchecked")
public static Optional<Server> lookupProxyServer(String name) {
    boolean nameIsFullyQualified = name.contains(".");
    if (!nameIsFullyQualified) {
        log.warn("Specified master name \"" + name + "\" is not fully-qualified," +
                "proxy attachment might not be correct");
        log.warn("Please use a FQDN in /etc/salt/minion.d/susemanager.conf");
    }

    DetachedCriteria proxyIds = DetachedCriteria.forClass(ProxyInfo.class)
            .setProjection(Projections.property("server.id"));

    Optional<Server> result = findByFqdn(name);

    if (result.isPresent()) {
        return result;
    }

    result = HibernateFactory.getSession()
            .createCriteria(Server.class)
            .add(Subqueries.propertyIn("id", proxyIds))
            .add(Restrictions.eq("hostname", name))
            .list()
            .stream()
            .findFirst();

    if (result.isPresent()) {
        return result;
    }

    // precise search did not work, try imprecise
    if (nameIsFullyQualified) {
        String srippedHostname = name.split("\\.")[0];

        return HibernateFactory.getSession()
                .createCriteria(Server.class)
                .add(Subqueries.propertyIn("id", proxyIds))
                .add(Restrictions.eq("hostname", srippedHostname))
                .list()
                .stream()
                .findFirst();
    }
    else {
        return HibernateFactory.getSession()
                .createCriteria(Server.class)
                .add(Subqueries.propertyIn("id", proxyIds))
                .add(Restrictions.like("hostname", name + ".", MatchMode.START))
                .list()
                .stream()
                .findFirst();
    }
}
 

@Override
protected Collection<JournalEntry> handleFindRecent(JournalModule module, Long modifiedUserId, DBModule criteriaModule, Long entityDepartmentId, boolean limit, PSFVO psf)
		throws Exception {
	org.hibernate.Criteria journalCriteria = createJournalEntryCriteria("journalEntry0");
	SubCriteriaMap criteriaMap = new SubCriteriaMap(JournalEntry.class, journalCriteria);
	if (modifiedUserId != null) {
		journalCriteria.add(Restrictions.eq("modifiedUser.id", modifiedUserId.longValue()));
	}
	if (limit) {
		applyRecentJournalEntryTimestampCriterion(journalCriteria, null);
	}
	journalCriteria.add(Restrictions.or(
			Restrictions.eq("systemMessage", false),
			Restrictions.and(Restrictions.eq("systemMessage", true), Restrictions.eq("systemMessageModule", module))));
	criteriaMap.createCriteria("category", CriteriaSpecification.LEFT_JOIN).add(Restrictions.or(Restrictions.eq("module", module), Restrictions.isNull("module")));
	DetachedCriteria subQuery = DetachedCriteria.forClass(JournalEntryImpl.class, "journalEntry1"); // IMPL!!!!
	subQuery.setProjection(Projections.max("id"));
	journalCriteria.add(Subqueries.propertyEq("id", subQuery));
	switch (module) {
		case INVENTORY_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.inventory", "journalEntry0.inventory"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("inventory").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		case STAFF_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.staff", "journalEntry0.staff"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("staff").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		case COURSE_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.course", "journalEntry0.course"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("course").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		case USER_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.user", "journalEntry0.user"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("user").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		case TRIAL_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.trial", "journalEntry0.trial"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("trial").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		case PROBAND_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.proband", "journalEntry0.proband"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("proband").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		case CRITERIA_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.criteria", "journalEntry0.criteria"));
			if (criteriaModule != null) {
				subQuery.createCriteria("criteria", CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("module", criteriaModule));
			}
			break;
		case INPUT_FIELD_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.inputField", "journalEntry0.inputField"));
			break;
		case MASS_MAIL_JOURNAL:
			subQuery.add(Restrictions.eqProperty("journalEntry1.massMail", "journalEntry0.massMail"));
			if (entityDepartmentId != null) {
				criteriaMap.createCriteria("massMail").add(Restrictions.eq("department.id", entityDepartmentId.longValue()));
			}
			break;
		default:
	}
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	return journalCriteria.list();
}
 

/**
 * Creates a detached criteria with data sharing restrictions relative to the
 * given user and access string.
 *
 * @param user the user.
 * @param access the access string.
 * @return a DetachedCriteria.
 */
private DetachedCriteria getDataSharingDetachedCriteria( UserInfo user, String access )
{
    DetachedCriteria criteria = DetachedCriteria.forClass( getClazz(), "c" );

    if ( user == null || !dataSharingEnabled( user ) )
    {
        return criteria;
    }

    Assert.notNull( user, "User argument can't be null." );

    Disjunction disjunction = Restrictions.disjunction();

    disjunction.add( Restrictions.like( "c.publicAccess", access ) );
    disjunction.add( Restrictions.isNull( "c.publicAccess" ) );

    DetachedCriteria userGroupDetachedCriteria = DetachedCriteria.forClass( getClazz(), "ugdc" );
    userGroupDetachedCriteria.createCriteria( "ugdc.userGroupAccesses", "uga" );
    userGroupDetachedCriteria.createCriteria( "uga.userGroup", "ug" );
    userGroupDetachedCriteria.createCriteria( "ug.members", "ugm" );

    userGroupDetachedCriteria.add( Restrictions.eqProperty( "ugdc.id", "c.id" ) );
    userGroupDetachedCriteria.add( Restrictions.eq( "ugm.id", user.getId() ) );
    userGroupDetachedCriteria.add( Restrictions.like( "uga.access", access ) );

    userGroupDetachedCriteria.setProjection( Property.forName( "uga.id" ) );

    disjunction.add( Subqueries.exists( userGroupDetachedCriteria ) );

    DetachedCriteria userDetachedCriteria = DetachedCriteria.forClass( getClazz(), "udc" );
    userDetachedCriteria.createCriteria( "udc.userAccesses", "ua" );
    userDetachedCriteria.createCriteria( "ua.user", "u" );

    userDetachedCriteria.add( Restrictions.eqProperty( "udc.id", "c.id" ) );
    userDetachedCriteria.add( Restrictions.eq( "u.id", user.getId() ) );
    userDetachedCriteria.add( Restrictions.like( "ua.access", access ) );

    userDetachedCriteria.setProjection( Property.forName( "ua.id" ) );

    disjunction.add( Subqueries.exists( userDetachedCriteria ) );

    criteria.add( disjunction );

    return criteria;
}
 

/**
 * Creates a detached criteria with sharing restrictions relative to the given
 * user and access string.
 *
 * @param user   the user.
 * @param access the access string.
 * @return a DetachedCriteria.
 */
private DetachedCriteria getSharingDetachedCriteria( UserInfo user, String access )
{
    DetachedCriteria criteria = DetachedCriteria.forClass( getClazz(), "c" );

    preProcessDetachedCriteria( criteria );

    if ( !sharingEnabled( user ) || user == null )
    {
        return criteria;
    }

    Assert.notNull( user, "User argument can't be null." );

    Disjunction disjunction = Restrictions.disjunction();

    disjunction.add( Restrictions.like( "c.publicAccess", access ) );
    disjunction.add( Restrictions.isNull( "c.publicAccess" ) );
    disjunction.add( Restrictions.isNull( "c.user.id" ) );
    disjunction.add( Restrictions.eq( "c.user.id", user.getId() ) );

    DetachedCriteria userGroupDetachedCriteria = DetachedCriteria.forClass( getClazz(), "ugdc" );
    userGroupDetachedCriteria.createCriteria( "ugdc.userGroupAccesses", "uga" );
    userGroupDetachedCriteria.createCriteria( "uga.userGroup", "ug" );
    userGroupDetachedCriteria.createCriteria( "ug.members", "ugm" );

    userGroupDetachedCriteria.add( Restrictions.eqProperty( "ugdc.id", "c.id" ) );
    userGroupDetachedCriteria.add( Restrictions.eq( "ugm.id", user.getId() ) );
    userGroupDetachedCriteria.add( Restrictions.like( "uga.access", access ) );

    userGroupDetachedCriteria.setProjection( Property.forName( "uga.id" ) );

    disjunction.add( Subqueries.exists( userGroupDetachedCriteria ) );

    DetachedCriteria userDetachedCriteria = DetachedCriteria.forClass( getClazz(), "udc" );
    userDetachedCriteria.createCriteria( "udc.userAccesses", "ua" );
    userDetachedCriteria.createCriteria( "ua.user", "u" );

    userDetachedCriteria.add( Restrictions.eqProperty( "udc.id", "c.id" ) );
    userDetachedCriteria.add( Restrictions.eq( "u.id", user.getId() ) );
    userDetachedCriteria.add( Restrictions.like( "ua.access", access ) );

    userDetachedCriteria.setProjection( Property.forName( "ua.id" ) );

    disjunction.add( Subqueries.exists( userDetachedCriteria ) );

    criteria.add( disjunction );

    return criteria;
}
 

public void testSubselect() {

		Session session = openSession();
		Transaction t = session.beginTransaction();

		Course course = new Course();
		course.setCourseCode("HIB");
		course.setDescription("Hibernate Training");
		session.persist(course);
		
		Student gavin = new Student();
		gavin.setName("Gavin King");
		gavin.setStudentNumber(232);
		session.persist(gavin);

		Enrolment enrolment2 = new Enrolment();
		enrolment2.setCourse(course);
		enrolment2.setCourseCode(course.getCourseCode());
		enrolment2.setSemester((short) 3);
		enrolment2.setYear((short) 1998);
		enrolment2.setStudent(gavin);
		enrolment2.setStudentNumber(gavin.getStudentNumber());
		gavin.getEnrolments().add(enrolment2);
		session.persist(enrolment2);
		
		DetachedCriteria dc = DetachedCriteria.forClass(Student.class)
			.add( Property.forName("studentNumber").eq( new Long(232) ) )
			.setProjection( Property.forName("name") );

		session.createCriteria(Student.class)
			.add( Subqueries.propertyEqAll("name", dc) )
			.list();
		
		session.createCriteria(Student.class)
			.add( Subqueries.exists(dc) )
			.list();
	
		session.createCriteria(Student.class)
		.add( Property.forName("name").eqAll(dc) )
		.list();
	
		session.createCriteria(Student.class)
			.add( Subqueries.in("Gavin King", dc) )
			.list();
		
		DetachedCriteria dc2 = DetachedCriteria.forClass(Student.class, "st")
			.add( Property.forName("st.studentNumber").eqProperty("e.studentNumber") )
			.setProjection( Property.forName("name") );
		
		session.createCriteria(Enrolment.class, "e")
			.add( Subqueries.eq("Gavin King", dc2) )
			.list();

		//TODO: join in subselect: HHH-952
		/*DetachedCriteria dc3 = DetachedCriteria.forClass(Student.class, "st")
			.createCriteria("enrolments")
				.createCriteria("course")
					.add( Property.forName("description").eq("Hibernate Training") )
					.setProjection( Property.forName("st.name") );
	
		session.createCriteria(Enrolment.class, "e")
			.add( Subqueries.eq("Gavin King", dc3) )
			.list();*/

		session.delete(enrolment2);
		session.delete(gavin);
		session.delete(course);
		t.commit();
		session.close();
		
	}