org.hibernate.criterion.CriteriaSpecification Java Examples

/**
 * @inheritDoc
 */
@Override
protected Collection<DutyRosterTurn> handleFindByDepartmentCategoryCalendarInterval(Long staffDepartmentId, Long staffCategoryId, Boolean allocatable, String calendar,
		Timestamp from, Timestamp to) {
	Criteria dutyRosterCriteria = createDutyRosterTurnCriteria("dutyRosterTurn");
	CriteriaUtil.applyClosedIntervalCriterion(dutyRosterCriteria, from, to, null);
	Criteria staffCriteria = null;
	if (staffDepartmentId != null) {
		staffCriteria = dutyRosterCriteria.createCriteria("staff", CriteriaSpecification.LEFT_JOIN);
	} else if (staffCategoryId != null || allocatable != null) {
		staffCriteria = dutyRosterCriteria.createCriteria("staff", CriteriaSpecification.INNER_JOIN);
	}
	if (staffDepartmentId != null || staffCategoryId != null || allocatable != null) {
		if (staffDepartmentId != null) {
			staffCriteria.add(Restrictions.or(Restrictions.isNull("dutyRosterTurn.staff"), Restrictions.eq("department.id", staffDepartmentId.longValue())));
		}
		if (staffCategoryId != null) {
			staffCriteria.add(Restrictions.eq("category.id", staffCategoryId.longValue()));
		}
		if (allocatable != null) {
			staffCriteria.add(Restrictions.eq("allocatable", allocatable.booleanValue()));
		}
	}
	CategoryCriterion.apply(dutyRosterCriteria, new CategoryCriterion(calendar, "calendar", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	return dutyRosterCriteria.list();
}
 

private static Criteria applyStratificationTagValuesCriterions(org.hibernate.Criteria listEntryCriteria, Set<Long> selectionSetValueIds) {
	org.hibernate.Criteria tagValuesCriteria = listEntryCriteria.createCriteria("tagValues", CriteriaSpecification.INNER_JOIN);
	tagValuesCriteria.createCriteria("tag", CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("stratification", true));
	org.hibernate.Criteria selectionValuesCriteria = tagValuesCriteria.createCriteria("value", CriteriaSpecification.INNER_JOIN).createCriteria("selectionValues",
			CriteriaSpecification.INNER_JOIN);
	selectionValuesCriteria.add(Restrictions.in("id", selectionSetValueIds));
	ProjectionList proj = Projections.projectionList();
	proj.add(Projections.id());
	proj.add(Projections.sqlGroupProjection(
			"count(*) as selectionValuesCount",
			"{alias}.id having count(*) = " + selectionSetValueIds.size(),
			new String[] { "selectionValuesCount" },
			new org.hibernate.type.Type[] { Hibernate.LONG }));
	listEntryCriteria.setProjection(proj);
	return listEntryCriteria;
}
 

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 long handleGetCount(Long trialId, Boolean active, Boolean activeSignup, Long probandId) throws Exception {
	org.hibernate.Criteria inquiryValueCriteria = createInquiryValueCriteria();
	if (probandId != null) {
		inquiryValueCriteria.add(Restrictions.eq("proband.id", probandId.longValue()));
	}
	if (trialId != null || active != null) {
		org.hibernate.Criteria inquiryCriteria = inquiryValueCriteria.createCriteria("inquiry", CriteriaSpecification.INNER_JOIN);
		if (trialId != null) {
			inquiryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
		}
		if (active != null) {
			inquiryCriteria.add(Restrictions.eq("active", active.booleanValue()));
		}
		if (activeSignup != null) {
			inquiryCriteria.add(Restrictions.eq("activeSignup", activeSignup.booleanValue()));
		}
	}
	return (Long) inquiryValueCriteria.setProjection(Projections.rowCount()).uniqueResult();
}
 

@Override
protected Collection<Inquiry> handleFindByTrialActiveExcelProbandSorted(Long trialId, Boolean active, Boolean activeSignup, Boolean excel, Long probandId) throws Exception {
	org.hibernate.Criteria inquiryCriteria = createInquiryCriteria();
	if (trialId != null) {
		inquiryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (active != null) {
		inquiryCriteria.add(Restrictions.eq("active", active.booleanValue()));
	}
	if (activeSignup != null) {
		inquiryCriteria.add(Restrictions.eq("activeSignup", activeSignup.booleanValue()));
	}
	if (excel != null) {
		inquiryCriteria.add(Restrictions.or(Restrictions.eq("excelValue", excel.booleanValue()),
				Restrictions.eq("excelDate", excel.booleanValue())));
	}
	if (probandId != null) {
		inquiryCriteria.createCriteria("inquiryValues", CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("proband.id", probandId.longValue()));
	}
	applySortOrders(inquiryCriteria);
	return CriteriaUtil.listDistinctRoot(inquiryCriteria, this, "trial.id", "category", "position");
}
 

@Override
protected Collection<Inquiry> handleFindByParticipantsActiveSorted(Long trialId, Boolean active, Boolean activeSignup) throws Exception {
	org.hibernate.Criteria inquiryCriteria = createInquiryCriteria();
	if (active != null) {
		inquiryCriteria.add(Restrictions.eq("active", active.booleanValue()));
	}
	if (activeSignup != null) {
		inquiryCriteria.add(Restrictions.eq("activeSignup", activeSignup.booleanValue()));
	}
	if (trialId != null) {
		inquiryCriteria.createCriteria("inquiryValues", CriteriaSpecification.INNER_JOIN).createCriteria("proband", CriteriaSpecification.INNER_JOIN)
				.createCriteria("trialParticipations", CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	applySortOrders(inquiryCriteria);
	return CriteriaUtil.listDistinctRoot(inquiryCriteria, this, "trial.id", "category", "position");
}
 

@Override
protected Collection<StaffStatusEntry> handleFindByStaffInterval(
		Long staffId, Timestamp from, Timestamp to, Boolean staffActive, Boolean allocatable, Boolean hideAvailability)
		throws Exception {
	Criteria statusEntryCriteria = createStatusEntryCriteria();
	CriteriaUtil.applyStopOpenIntervalCriterion(statusEntryCriteria, from, to, null);
	if (staffActive != null || hideAvailability != null) {
		Criteria statusTypeCriteria = statusEntryCriteria.createCriteria("type", CriteriaSpecification.INNER_JOIN);
		if (staffActive != null) {
			statusTypeCriteria.add(Restrictions.eq("staffActive", staffActive.booleanValue()));
		}
		if (hideAvailability != null) {
			statusTypeCriteria.add(Restrictions.eq("hideAvailability", hideAvailability.booleanValue()));
		}
	}
	if (staffId != null) {
		statusEntryCriteria.add(Restrictions.eq("staff.id", staffId.longValue()));
	}
	if (allocatable != null) {
		statusEntryCriteria.createCriteria("staff", CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("allocatable", allocatable.booleanValue()));
	}
	return statusEntryCriteria.list();
}
 

@Override
protected Collection<InventoryBooking> handleFindByTrialDepartmentInterval(
		Long trialId, Long trialDepartmentId, Timestamp from, Timestamp to, Boolean isRelevantForTrialAppointments)
		throws Exception {
	Criteria bookingCriteria = createBookingCriteria();
	CriteriaUtil.applyClosedIntervalCriterion(bookingCriteria, from, to, null);
	if (trialId != null) {
		bookingCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	} else if (trialDepartmentId != null) {
		Criteria trialCriteria = bookingCriteria.createCriteria("trial", CriteriaSpecification.INNER_JOIN);
		trialCriteria.add(Restrictions.eq("department.id", trialDepartmentId.longValue()));
	} else {
		bookingCriteria.add(Restrictions.isNotNull("trial"));
	}
	if (isRelevantForTrialAppointments != null) {
		bookingCriteria.createCriteria("inventory", CriteriaSpecification.INNER_JOIN).createCriteria("category", CriteriaSpecification.INNER_JOIN)
				.add(Restrictions.eq("relevantForTrialAppointments", isRelevantForTrialAppointments.booleanValue()));
	}
	return bookingCriteria.list();
}
 

public static List listDistinctRoot(Criteria criteria, Object dao, String... fields) throws Exception {
	if (dao != null && criteria != null) {
		criteria.setProjection(null);
		criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		Method loadMethod = CoreUtil.getDaoLoadMethod(dao);
		ProjectionList projectionList = Projections.projectionList().add(Projections.id());
		boolean cast = false;
		if (fields != null && fields.length > 0) {
			for (int i = 0; i < fields.length; i++) {
				projectionList.add(Projections.property(fields[i]));
			}
			cast = true;
		}
		List items = criteria.setProjection(Projections.distinct(projectionList)).list();
		Iterator it = items.iterator();
		ArrayList result = new ArrayList(items.size());
		while (it.hasNext()) {
			result.add(loadMethod.invoke(dao, cast ? ((Object[]) it.next())[0] : it.next()));
		}
		return result;
	}
	return null;
}
 

@Override
protected Collection<InventoryBooking> handleFindByTrialCalendarInterval(
		Long trialId, String calendar, Timestamp from, Timestamp to,
		Boolean isRelevantForTrialAppointments) throws Exception {
	Criteria bookingCriteria = createBookingCriteria();
	CriteriaUtil.applyClosedIntervalCriterion(bookingCriteria, from, to, null);
	if (trialId != null) {
		bookingCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (isRelevantForTrialAppointments != null) {
		bookingCriteria.createCriteria("inventory", CriteriaSpecification.INNER_JOIN).createCriteria("category", CriteriaSpecification.INNER_JOIN)
				.add(Restrictions.eq("relevantForTrialAppointments", isRelevantForTrialAppointments.booleanValue()));
	}
	CategoryCriterion.apply(bookingCriteria, new CategoryCriterion(calendar, "calendar", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	return bookingCriteria.list();
}
 

@Override
protected long handleGetCount(Long trialId, String category, Boolean active, Boolean activeSignup, Long probandId) throws Exception {
	org.hibernate.Criteria inquiryValueCriteria = createInquiryValueCriteria();
	if (probandId != null) {
		inquiryValueCriteria.add(Restrictions.eq("proband.id", probandId.longValue()));
	}
	org.hibernate.Criteria inquiryCriteria = inquiryValueCriteria.createCriteria("inquiry", CriteriaSpecification.INNER_JOIN);
	if (trialId != null) {
		inquiryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (active != null) {
		inquiryCriteria.add(Restrictions.eq("active", active.booleanValue()));
	}
	if (activeSignup != null) {
		inquiryCriteria.add(Restrictions.eq("activeSignup", activeSignup.booleanValue()));
	}
	if (category != null && category.length() > 0) {
		inquiryCriteria.add(Restrictions.eq("category", category));
	} else {
		inquiryCriteria.add(Restrictions.or(Restrictions.eq("category", ""), Restrictions.isNull("category")));
	}
	return (Long) inquiryValueCriteria.setProjection(Projections.rowCount()).uniqueResult();
}
 

@Override
protected long handleGetTrialGroupProbandCount(
		Long trialId, Long probandGroupId, Long probandId, boolean total) throws Exception {
	org.hibernate.Criteria listEntryCriteria = createListEntryCriteria();
	if (trialId != null) {
		listEntryCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (probandGroupId != null) {
		listEntryCriteria.add(Restrictions.eq("group.id", probandGroupId.longValue()));
	}
	if (probandId != null) {
		listEntryCriteria.add(Restrictions.eq("proband.id", probandId.longValue()));
	}
	if (!total) {
		listEntryCriteria.createCriteria("lastStatus", CriteriaSpecification.INNER_JOIN).createCriteria("status", CriteriaSpecification.INNER_JOIN)
				.add(Restrictions.eq("count", true));
	}
	return (Long) listEntryCriteria.setProjection(Projections.rowCount()).uniqueResult();
}
 

@Override
protected Collection<Trial> handleFindByReimbursementProbandSorted(
		Long probandId, PaymentMethod method, String costType, Boolean paid) throws Exception {
	org.hibernate.Criteria trialCriteria = createTrialCriteria(null);
	Criteria payoffCriteria = trialCriteria.createCriteria("payoffs", CriteriaSpecification.INNER_JOIN);
	payoffCriteria.add(Restrictions.eq("proband.id", probandId.longValue()));
	if (method != null) {
		payoffCriteria.add(Restrictions.eq("method", method));
	}
	if (paid != null) {
		payoffCriteria.add(Restrictions.eq("paid", paid.booleanValue()));
	}
	CategoryCriterion.apply(payoffCriteria, new CategoryCriterion(costType, "costType", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	trialCriteria.addOrder(Order.asc("name"));
	return CriteriaUtil.listDistinctRoot(trialCriteria, this, "name");
}
 

@Override
protected Collection<Map> handleFindByListEntryJs(Long probandListEntryId, boolean sort, Boolean js, PSFVO psf) throws Exception {
	org.hibernate.Criteria listEntryTagCriteria = createProbandListEntryTagCriteria(probandListEntryId);
	if (js != null) {
		if (js) {
			listEntryTagCriteria.add(Restrictions.and(Restrictions.ne("jsVariableName", ""), Restrictions.isNotNull("jsVariableName")));
		} else {
			listEntryTagCriteria.add(Restrictions.or(Restrictions.eq("jsVariableName", ""), Restrictions.isNull("jsVariableName")));
		}
	}
	if (psf != null) {
		SubCriteriaMap criteriaMap = new SubCriteriaMap(ProbandListEntryTag.class, listEntryTagCriteria);
		// clear sort and filters?
		CriteriaUtil.applyPSFVO(criteriaMap, psf);
	}
	if (sort) {
		applySortOrders(listEntryTagCriteria);
	}
	listEntryTagCriteria.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	return listEntryTagCriteria.list();
}
 

private static void applyAspSubstanceNameCriterions(org.hibernate.Criteria aspSubstanceCriteria, String nameInfix) {
	String revision = Settings.getString(SettingCodes.ASP_REVISION, Bundle.SETTINGS, DefaultSettings.ASP_REVISION);
	ArrayList<CategoryCriterion> criterions = new ArrayList<CategoryCriterion>();
	criterions.add(new CategoryCriterion(nameInfix, "name", MatchMode.ANYWHERE));
	if (MATCH_ASP_NAME || MATCH_ASP_REGISTRATION_NUMBER || MATCH_ATC_CODE_CODE) {
		org.hibernate.Criteria aspsCriteria = aspSubstanceCriteria.createCriteria("asps", "asps0", CriteriaSpecification.LEFT_JOIN);
		if (MATCH_ASP_NAME) {
			criterions.add(new CategoryCriterion(nameInfix, "asps0.name", MatchMode.ANYWHERE));
		}
		if (MATCH_ASP_REGISTRATION_NUMBER) {
			criterions.add(new CategoryCriterion(nameInfix, "asps0.registrationNumber", MatchMode.EXACT));
		}
		aspsCriteria.add(Restrictions.eq("revision", revision));
		if (MATCH_ATC_CODE_CODE) {
			org.hibernate.Criteria atcCodesCriteria = aspsCriteria.createCriteria("atcCodes", "atcCodes0", CriteriaSpecification.LEFT_JOIN);
			atcCodesCriteria.add(Restrictions.eq("revision", revision));
			criterions.add(new CategoryCriterion(nameInfix, "atcCodes0.code", MatchMode.EXACT));
		}
	}
	CategoryCriterion.applyOr(aspSubstanceCriteria, criterions);
	aspSubstanceCriteria.add(Restrictions.eq("revision", revision));
}
 

@Override
protected long handleGetInquiryMaxSelectionSetValueCount(Long trialId) throws Exception {
	org.hibernate.Criteria inputFieldCriteria = createInputFieldCriteria();
	inputFieldCriteria.add(
			Restrictions.in("fieldType", SELECT_FIELD_TYPES)); // no AUTOCOMPLETE!
	org.hibernate.Criteria ecrfFieldCriteria = inputFieldCriteria.createCriteria("inquiries", "inquiries0", CriteriaSpecification.INNER_JOIN);
	ecrfFieldCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	org.hibernate.Criteria selectionSetValueCriteria = inputFieldCriteria.createCriteria("selectionSetValues", "inputFieldSelectionSetValues",
			CriteriaSpecification.INNER_JOIN);
	inputFieldCriteria.setProjection(Projections.projectionList()
			.add(Projections.groupProperty("inquiries0.id"))
			.add(Projections.alias(Projections.count("inputFieldSelectionSetValues.id"), "selectionSetValuesCount")));
	inputFieldCriteria.addOrder(Order.desc("selectionSetValuesCount"));
	inputFieldCriteria.setMaxResults(1);
	long maxSelectionSetValues = 0l;
	try {
		maxSelectionSetValues = (Long) ((Object[]) inputFieldCriteria.list().iterator().next())[1];
	} catch (Exception e) {
	}
	return maxSelectionSetValues;
}
 

@Override
protected long handleGetEcrfFieldMaxSelectionSetValueCount(Long trialId) throws Exception {
	org.hibernate.Criteria inputFieldCriteria = createInputFieldCriteria();
	inputFieldCriteria.add(
			Restrictions.in("fieldType", SELECT_FIELD_TYPES)); // no AUTOCOMPLETE!
	org.hibernate.Criteria ecrfFieldCriteria = inputFieldCriteria.createCriteria("ecrfFields", "ecrfFields0", CriteriaSpecification.INNER_JOIN);
	ecrfFieldCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	org.hibernate.Criteria selectionSetValueCriteria = inputFieldCriteria.createCriteria("selectionSetValues", "inputFieldSelectionSetValues",
			CriteriaSpecification.INNER_JOIN);
	inputFieldCriteria.setProjection(Projections.projectionList()
			.add(Projections.groupProperty("ecrfFields0.id"))
			.add(Projections.alias(Projections.count("inputFieldSelectionSetValues.id"), "selectionSetValuesCount")));
	inputFieldCriteria.addOrder(Order.desc("selectionSetValuesCount"));
	inputFieldCriteria.setMaxResults(1);
	long maxSelectionSetValues = 0l;
	try {
		maxSelectionSetValues = (Long) ((Object[]) inputFieldCriteria.list().iterator().next())[1];
	} catch (Exception e) {
	}
	return maxSelectionSetValues;
}
 

@Override
protected Collection<VisitScheduleItem> handleFindByTrialDepartmentIntervalId(Long trialId, Long departmentId, Timestamp from, Timestamp to, Long id) throws Exception {
	org.hibernate.Criteria visitScheduleItemCriteria = createVisitScheduleItemCriteria("visitScheduleItem");
	org.hibernate.criterion.Criterion idCriterion;
	if (id != null) {
		idCriterion = Restrictions.eq("visitScheduleItem.id", id.longValue());
	} else {
		idCriterion = null;
	}
	if (trialId != null || departmentId != null) {
		org.hibernate.Criteria trialCriteria = visitScheduleItemCriteria.createCriteria("trial", CriteriaSpecification.INNER_JOIN); // ? inner join because trial is never null
		if (trialId != null) {
			visitScheduleItemCriteria.createCriteria("trial", CriteriaSpecification.INNER_JOIN).add(CriteriaUtil.applyOr(Restrictions.idEq(trialId.longValue()), idCriterion));
		}
		if (departmentId != null) {
			trialCriteria.add(CriteriaUtil.applyOr(Restrictions.eq("department.id", departmentId.longValue()), idCriterion));
		}
	}
	return listExpandDateMode(visitScheduleItemCriteria, null, from, to, idCriterion, null, null, true);
}
 

@Override
protected Collection<Object[]> handleFindByTrialDepartmentStatusTypeInterval(Long trialId,
		Long departmentId, Long statusId, Long visitTypeId, Timestamp from, Timestamp to)
		throws Exception {
	org.hibernate.Criteria visitScheduleItemCriteria = createVisitScheduleItemCriteria(null);
	if (trialId != null || departmentId != null || statusId != null) {
		org.hibernate.Criteria trialCriteria = visitScheduleItemCriteria.createCriteria("trial", CriteriaSpecification.INNER_JOIN); // ? inner join because trial is never null
		if (trialId != null) {
			trialCriteria.add(Restrictions.idEq(trialId.longValue()));
		}
		if (departmentId != null) {
			trialCriteria.add(Restrictions.eq("department.id", departmentId.longValue()));
		}
		if (statusId != null) {
			trialCriteria.add(Restrictions.eq("status.id", statusId.longValue()));
		}
	}
	if (visitTypeId != null) {
		org.hibernate.Criteria visitCriteria = visitScheduleItemCriteria.createCriteria("visit", CriteriaSpecification.INNER_JOIN); // ? inner join because trial is never null
		visitCriteria.add(Restrictions.eq("type.id", visitTypeId.longValue()));
	}
	return listExpandDateModeProband(visitScheduleItemCriteria, null, from, to, null, null);
}
 

@Override
protected Collection<VisitScheduleItem> handleFindByTrialSorted(Long trialId, boolean sort, PSFVO psf)
		throws Exception {
	org.hibernate.Criteria visitScheduleItemCriteria = createVisitScheduleItemCriteria(null);
	SubCriteriaMap criteriaMap = new SubCriteriaMap(VisitScheduleItem.class, visitScheduleItemCriteria);
	if (trialId != null) {
		visitScheduleItemCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	CriteriaUtil.applyPSFVO(criteriaMap, psf); // proband has only one probandlistentry, and listentry has only one group, but left joins
	if (sort) {
		visitScheduleItemCriteria.addOrder(Order.asc("trial"));
		org.hibernate.Criteria visitCriteria = visitScheduleItemCriteria.createCriteria("visit", CriteriaSpecification.LEFT_JOIN);
		visitCriteria.addOrder(Order.asc("token"));
		org.hibernate.Criteria groupCriteria = visitScheduleItemCriteria.createCriteria("group", CriteriaSpecification.LEFT_JOIN);
		groupCriteria.addOrder(Order.asc("token"));
		visitScheduleItemCriteria.addOrder(Order.asc("token"));
	}
	return visitScheduleItemCriteria.list();
}
 

private static void applyAspNameCriterions(org.hibernate.Criteria aspCriteria, String nameInfix) {
	String revision = Settings.getString(SettingCodes.ASP_REVISION, Bundle.SETTINGS, DefaultSettings.ASP_REVISION);
	ArrayList<CategoryCriterion> criterions = new ArrayList<CategoryCriterion>();
	criterions.add(new CategoryCriterion(nameInfix, "name", MatchMode.ANYWHERE));
	if (MATCH_REGISTRATION_NUMBER) {
		criterions.add(new CategoryCriterion(nameInfix, "registrationNumber", MatchMode.EXACT));
	}
	if (MATCH_SUBSTANCE_NAME) {
		org.hibernate.Criteria substancesCriteria = aspCriteria.createCriteria("substances", "substances0", CriteriaSpecification.LEFT_JOIN);
		substancesCriteria.add(Restrictions.eq("revision", revision));
		criterions.add(new CategoryCriterion(nameInfix, "substances0.name", MatchMode.ANYWHERE));
	}
	if (MATCH_ATC_CODE_CODE) {
		org.hibernate.Criteria atcCodesCriteria = aspCriteria.createCriteria("atcCodes", "atcCodes0", CriteriaSpecification.LEFT_JOIN);
		atcCodesCriteria.add(Restrictions.eq("revision", revision));
		criterions.add(new CategoryCriterion(nameInfix, "atcCodes0.code", MatchMode.EXACT));
	}
	CategoryCriterion.applyOr(aspCriteria, criterions);
	aspCriteria.add(Restrictions.eq("revision", revision));
}
 

@Override
protected Collection<ECRF> handleFindByTrialActiveSorted(Long trialId, Boolean active, boolean sort, PSFVO psf) throws Exception {
	org.hibernate.Criteria ecrfCriteria = createEcrfCriteria(null);
	SubCriteriaMap criteriaMap = new SubCriteriaMap(ECRF.class, ecrfCriteria);
	if (trialId != null) {
		ecrfCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (active != null) {
		ecrfCriteria.add(Restrictions.eq("active", active.booleanValue()));
	}
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	if (sort) {
		ecrfCriteria.addOrder(Order.asc("trial"));
		Criteria visitCriteria = ecrfCriteria.createCriteria("visit", CriteriaSpecification.LEFT_JOIN);
		visitCriteria.addOrder(Order.asc("token"));
		Criteria groupCriteria = ecrfCriteria.createCriteria("group", CriteriaSpecification.LEFT_JOIN);
		groupCriteria.addOrder(Order.asc("token"));
		ecrfCriteria.addOrder(Order.asc("position"));
	}
	return ecrfCriteria.list();
}
 

@Override
protected Collection<JournalEntry> handleFindEcrfJournal(Long trailId, Boolean systemMessage) throws Exception {
	org.hibernate.Criteria journalCriteria = createJournalEntryCriteria(null);
	if (systemMessage != null) {
		journalCriteria.add(Restrictions.eq("systemMessage", systemMessage.booleanValue()));
	}
	journalCriteria.add(Restrictions.or(
			Restrictions.eq("systemMessage", false),
			Restrictions.or(Restrictions.and(Restrictions.eq("systemMessage", true), Restrictions.eq("systemMessageModule", JournalModule.TRIAL_JOURNAL)),
					Restrictions.and(Restrictions.eq("systemMessage", true), Restrictions.eq("systemMessageModule", JournalModule.INPUT_FIELD_JOURNAL)))));
	journalCriteria.createCriteria("category", CriteriaSpecification.LEFT_JOIN).add(Restrictions.or(
			Restrictions.or(Restrictions.eq("module", JournalModule.TRIAL_JOURNAL), Restrictions.eq("module", JournalModule.INPUT_FIELD_JOURNAL)),
			Restrictions.isNull("module")));
	journalCriteria.createCriteria("inputField", CriteriaSpecification.LEFT_JOIN).createCriteria("ecrfFields", "trialEcrfField", CriteriaSpecification.LEFT_JOIN);
	journalCriteria.add(Restrictions.or(Restrictions.eq("trial.id", trailId.longValue()),
			Restrictions.eq("trialEcrfField.trial.id", trailId.longValue())));
	journalCriteria.addOrder(Order.asc("id"));
	return CriteriaUtil.listDistinctRoot(journalCriteria, this);
}
 

@Override
protected Collection<CourseParticipationStatusEntry> handleFindByStaffCourseRelevantForCourseAppointments(Long staffId, Long courseId, Boolean isRelevantForCourseAppointments)
		throws Exception {
	org.hibernate.Criteria courseParticipationStatusEntryCriteria = createCourseParticipationStatusEntryCriteria();
	if (staffId != null) {
		courseParticipationStatusEntryCriteria.add(Restrictions.eq("staff.id", staffId.longValue()));
	}
	if (courseId != null) {
		courseParticipationStatusEntryCriteria.add(Restrictions.eq("course.id", courseId.longValue()));
	}
	if (isRelevantForCourseAppointments != null) {
		courseParticipationStatusEntryCriteria.createCriteria("status", CriteriaSpecification.INNER_JOIN).add(
				Restrictions.eq("relevantForCourseAppointments", isRelevantForCourseAppointments.booleanValue()));
	}
	return courseParticipationStatusEntryCriteria.list();
}
 

@Override
protected Collection<InventoryStatusEntry> handleFindByInventoryInterval(
		Long inventoryId, Timestamp from, Timestamp to, Boolean inventoryActive, Boolean hideAvailability) throws Exception {
	Criteria statusEntryCriteria = createStatusEntryCriteria();
	CriteriaUtil.applyStopOpenIntervalCriterion(statusEntryCriteria, from, to, null);
	if (inventoryActive != null || hideAvailability != null) {
		Criteria statusTypeCriteria = statusEntryCriteria.createCriteria("type", CriteriaSpecification.INNER_JOIN);
		if (inventoryActive != null) {
			statusTypeCriteria.add(Restrictions.eq("inventoryActive", inventoryActive.booleanValue()));
		}
		if (hideAvailability != null) {
			statusTypeCriteria.add(Restrictions.eq("hideAvailability", hideAvailability.booleanValue()));
		}
	}
	if (inventoryId != null) {
		statusEntryCriteria.add(Restrictions.eq("inventory.id", inventoryId.longValue()));
	}
	return statusEntryCriteria.list();
}
 

@Override
protected Collection<MaintenanceScheduleItem> handleFindMaintenanceInterval(
		Long inventoryId, Long departmentId,
		Long inventoryCategoryId, Long responsiblePersonId, Long responsiblePersonProxyId,
		Boolean reminder, Timestamp from, Timestamp to) throws Exception {
	Criteria maintenanceItemCriteria = createMaintenanceItemCriteria();
	if (inventoryId != null || departmentId != null || inventoryCategoryId != null) {
		Criteria inventoryCriteria = maintenanceItemCriteria.createCriteria("inventory", CriteriaSpecification.INNER_JOIN);
		if (inventoryId != null) {
			inventoryCriteria.add(Restrictions.idEq(inventoryId.longValue()));
		}
		if (departmentId != null) {
			inventoryCriteria.add(Restrictions.eq("department.id", departmentId.longValue()));
		}
		if (inventoryCategoryId != null) {
			inventoryCriteria.add(Restrictions.eq("category.id", inventoryCategoryId.longValue()));
		}
	}
	applyResponsiblePersonProxyCriterions(maintenanceItemCriteria, responsiblePersonId, responsiblePersonProxyId);
	maintenanceItemCriteria.add(Restrictions.eq("active", true)); // performance only...
	return CriteriaUtil.listEvents(maintenanceItemCriteria, from, to, reminder);
}
 

@Override
protected Collection<Map> handleFindByListEntryEcrfJsField(Long probandListEntryId, Long ecrfId, boolean sort, Boolean js, String fieldQuery, PSFVO psf) throws Exception {
	org.hibernate.Criteria[] criterias = createEcrfFieldCriteria(probandListEntryId, ecrfId);
	org.hibernate.Criteria ecrfFieldCriteria = criterias[0];
	org.hibernate.Criteria ecrfFieldValueCriteria = criterias[1];
	if (js != null) {
		if (js) {
			ecrfFieldCriteria.add(Restrictions.and(Restrictions.ne("jsVariableName", ""), Restrictions.isNotNull("jsVariableName")));
		} else {
			ecrfFieldCriteria.add(Restrictions.or(Restrictions.eq("jsVariableName", ""), Restrictions.isNull("jsVariableName")));
		}
	}
	applyEcrfFieldSearchCriterions(ecrfFieldCriteria, fieldQuery);
	if (psf != null) {
		SubCriteriaMap criteriaMap = new SubCriteriaMap(ECRFField.class, ecrfFieldCriteria);
		// clear sort and filters?
		CriteriaUtil.applyPSFVO(criteriaMap, psf);
	}
	if (sort) {
		applySortOrders(null, ecrfFieldCriteria, ecrfFieldValueCriteria);
	}
	ecrfFieldCriteria.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	return ecrfFieldCriteria.list();
}
 

/**
 * @inheritDoc
 */
@Override
protected Collection<InventoryBooking> handleFindByProbandCalendarInterval(Long probandId, String calendar, Timestamp from, Timestamp to,
		Boolean isRelevantForProbandAppointments) throws Exception {
	Criteria bookingCriteria = createBookingCriteria();
	CriteriaUtil.applyClosedIntervalCriterion(bookingCriteria, from, to, null);
	if (probandId != null) {
		bookingCriteria.add(Restrictions.eq("proband.id", probandId.longValue()));
	}
	if (isRelevantForProbandAppointments != null) {
		bookingCriteria.createCriteria("inventory", CriteriaSpecification.INNER_JOIN).createCriteria("category", CriteriaSpecification.INNER_JOIN)
				.add(Restrictions.eq("relevantForProbandAppointments", isRelevantForProbandAppointments.booleanValue()));
	}
	CategoryCriterion.apply(bookingCriteria, new CategoryCriterion(calendar, "calendar", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	return bookingCriteria.list();
}
 

@Override
protected Collection<DutyRosterTurn> handleFindByDepartmentStatusStaffTrialCalendarInterval(
		Long trialDepartmentId, Long statusId, Long staffId, boolean unassigned, Long trialId, String calendar, Timestamp from, Timestamp to)
		throws Exception {
	Criteria dutyRosterCriteria = createDutyRosterTurnCriteria("dutyRosterTurn");
	CriteriaUtil.applyClosedIntervalCriterion(dutyRosterCriteria, from, to, null);
	Criteria trialCriteria = null;
	if (trialDepartmentId != null) {
		trialCriteria = dutyRosterCriteria.createCriteria("trial", CriteriaSpecification.LEFT_JOIN);
	} else if (statusId != null || trialId != null) {
		trialCriteria = dutyRosterCriteria.createCriteria("trial", CriteriaSpecification.INNER_JOIN);
	}
	if (trialDepartmentId != null || statusId != null || trialId != null) {
		if (trialDepartmentId != null) {
			trialCriteria.add(Restrictions.or(Restrictions.isNull("dutyRosterTurn.trial"), Restrictions.eq("department.id", trialDepartmentId.longValue())));
		}
		if (statusId != null) {
			trialCriteria.add(Restrictions.eq("status.id", statusId.longValue()));
		}
		if (trialId != null) {
			trialCriteria.add(Restrictions.idEq(trialId.longValue()));
		}
	}
	if (staffId != null) {
		if (unassigned) {
			dutyRosterCriteria.add(Restrictions.or(Restrictions.eq("staff.id", staffId.longValue()), Restrictions.isNull("staff")));
		} else {
			dutyRosterCriteria.add(Restrictions.eq("staff.id", staffId.longValue()));
		}
	}
	CategoryCriterion.apply(dutyRosterCriteria, new CategoryCriterion(calendar, "calendar", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	return dutyRosterCriteria.list();
}
 

@Override
protected Collection<InventoryBooking> handleFindByAppointmentDepartmentsCalendarInterval(Long probandDepartmentId, Long courseDepartmentId, Long trialDepartmentId,
		String calendar, Timestamp from, Timestamp to,
		Boolean isProbandAppointment, Boolean isRelevantForProbandAppointments,
		Boolean isCourseAppointment, Boolean isRelevantForCourseAppointments,
		Boolean isTrialAppointment, Boolean isRelevantForTrialAppointments) throws Exception {
	Criteria bookingCriteria = createBookingCriteria();
	CriteriaUtil.applyClosedIntervalCriterion(bookingCriteria, from, to, null);
	if (probandDepartmentId != null) {
		bookingCriteria.createCriteria("proband", CriteriaSpecification.LEFT_JOIN).add(
				Restrictions.or(Restrictions.isNull("department.id"), Restrictions.eq("department.id", probandDepartmentId.longValue())));
	}
	if (courseDepartmentId != null) {
		bookingCriteria.createCriteria("course", CriteriaSpecification.LEFT_JOIN).add(
				Restrictions.or(Restrictions.isNull("department.id"), Restrictions.eq("department.id", courseDepartmentId.longValue())));
	}
	if (trialDepartmentId != null) {
		bookingCriteria.createCriteria("trial", CriteriaSpecification.LEFT_JOIN).add(
				Restrictions.or(Restrictions.isNull("department.id"), Restrictions.eq("department.id", trialDepartmentId.longValue())));
	}
	applyIncludeAppointmentsCriterion(bookingCriteria, isProbandAppointment, "proband.id", isRelevantForProbandAppointments, "relevantForProbandAppointments");
	applyIncludeAppointmentsCriterion(bookingCriteria, isCourseAppointment, "course.id", isRelevantForCourseAppointments, "relevantForCourseAppointments");
	applyIncludeAppointmentsCriterion(bookingCriteria, isTrialAppointment, "trial.id", isRelevantForTrialAppointments, "relevantForTrialAppointments");
	CategoryCriterion.apply(bookingCriteria, new CategoryCriterion(calendar, "calendar", MatchMode.EXACT, EmptyPrefixModes.ALL_ROWS));
	bookingCriteria.addOrder(Order.asc("start"));
	return bookingCriteria.list();
}