List of usage examples for org.hibernate.criterion ProjectionList add
public ProjectionList add(Projection projection, String alias)
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<ConsentDetailsDataRow> getStudyCompConsentList(ConsentDetailsReportVO cdrVO) { // NB: There should only ever be one Consent record for a particular Subject for a particular StudyComp List<ConsentDetailsDataRow> results = new ArrayList<ConsentDetailsDataRow>(); Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss"); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("lss." + "subjectUID"), "subjectUID"); criteria.add(/*ww w .j a v a 2 s.c om*/ Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy())); if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) { criteria.add(Restrictions.ilike("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTUID, cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE)); } if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) { criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS, cdrVO.getLinkSubjectStudy().getSubjectStatus())); } if (cdrVO.getConsentDate() != null) { // NB: constraint on consentDate or consentStatus automatically removes "Not Consented" state // So LinkSubjectStudy inner join to Consent ok for populated consentDate criteria.createAlias("lss." + Constants.LINKSUBJECTSTUDY_CONSENT, "c"); criteria.createAlias("c." + Constants.CONSENT_CONSENTSTATUS, "cs"); // constrain on studyComp criteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp())); // constrain on consentDate criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTDATE, cdrVO.getConsentDate())); // ConsentStatus is optional for this query... if (cdrVO.getConsentStatus() != null) { criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus())); } projectionList.add(Projections.property("cs.name"), "consentStatus"); projectionList.add(Projections.property("c." + Constants.CONSENT_CONSENTDATE), "consentDate"); } else if (cdrVO.getConsentStatus() != null) { if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) { // Need to handle "Not Consented" status differently (since it doesn't have a Consent record) // Helpful website: http://www.cereslogic.com/pages/2008/09/22/hibernate-criteria-subqueries-exists/ // Build subquery to find all Consent records for a Study Comp DetachedCriteria consentCriteria = DetachedCriteria.forClass(Consent.class, "c"); // Constrain on StudyComponent consentCriteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp())); // Just in case "Not Consented" is erroneously entered into a row in the Consent table // consentCriteria.add(Restrictions.ne("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus())); // Join LinkSubjectStudy and Consent on ID FK consentCriteria.add(Property.forName("c.linkSubjectStudy.id").eqProperty("lss." + "id")); criteria.add(Subqueries.notExists(consentCriteria.setProjection(Projections.property("c.id")))); // If Consent records follows design for "Not Consented", then: // - consentStatus and consentDate are not populated } else { // NB: constraint on consentDate or consentStatus automatically removes "Not Consented" state // So LinkSubjectStudy inner join to Consent ok for all recordable consentStatus criteria.createAlias("lss." + Constants.LINKSUBJECTSTUDY_CONSENT, "c"); criteria.createAlias("c." + Constants.CONSENT_CONSENTSTATUS, "cs"); // Constrain on StudyComponent criteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp())); // ConsentStatus is NOT optional for this query! criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus())); if (cdrVO.getConsentDate() != null) { criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTDATE, cdrVO.getConsentDate())); } projectionList.add(Projections.property("cs.name"), "consentStatus"); projectionList.add(Projections.property("c." + Constants.CONSENT_CONSENTDATE), "consentDate"); } } else { // Should not attempt to run this query with no consentDate nor consentStatus criteria provided log.error( "reportDao.getStudyCompConsentList(..) is missing consentDate or consentStatus parameters in the VO"); return null; } criteria.addOrder(Order.asc("lss." + "subjectUID")); criteria.setProjection(projectionList); criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class)); // This gives a list of subjects matching the specific studyComp and consentStatus results = criteria.list(); return results; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public Address getBestAddress(LinkSubjectStudy subject) { Address result = null;/* w w w. j ava 2 s . c o m*/ // Attempt to get the preferred address first Criteria criteria = getSession().createCriteria(Address.class); criteria.add(Restrictions.eq("person", subject.getPerson())); criteria.add(Restrictions.eq("preferredMailingAddress", true)); criteria.setMaxResults(1); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("streetAddress"), "streetAddress"); projectionList.add(Projections.property("city"), "city"); projectionList.add(Projections.property("country"), "country"); projectionList.add(Projections.property("state"), "state"); projectionList.add(Projections.property("otherState"), "otherState"); projectionList.add(Projections.property("postCode"), "postCode"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(Address.class)); if (criteria.uniqueResult() != null) { result = (Address) criteria.uniqueResult(); } else { // Get any address criteria = getSession().createCriteria(Address.class); criteria.add(Restrictions.eq("person", subject.getPerson())); criteria.setMaxResults(1); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(Address.class)); result = (Address) criteria.uniqueResult(); } return result; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public Phone getWorkPhone(LinkSubjectStudy subject) { Phone result = null;//from w ww. j a va2s .c om Criteria criteria = getSession().createCriteria(Phone.class); criteria.add(Restrictions.eq("person", subject.getPerson())); criteria.createAlias("phoneType", "pt"); criteria.add(Restrictions.eq("pt.name", "Work")); criteria.setMaxResults(1); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("areaCode"), "areaCode"); projectionList.add(Projections.property("phoneNumber"), "phoneNumber"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(Phone.class)); if (criteria.uniqueResult() != null) { result = (Phone) criteria.uniqueResult(); } return result; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public Phone getHomePhone(LinkSubjectStudy subject) { Phone result = null;/*from w ww. j a v a 2 s. c o m*/ Criteria criteria = getSession().createCriteria(Phone.class); criteria.add(Restrictions.eq("person", subject.getPerson())); criteria.createAlias("phoneType", "pt"); criteria.add(Restrictions.eq("pt.name", "Home")); criteria.setMaxResults(1); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("areaCode"), "areaCode"); projectionList.add(Projections.property("phoneNumber"), "phoneNumber"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(Phone.class)); if (criteria.uniqueResult() != null) { result = (Phone) criteria.uniqueResult(); } return result; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public Consent getStudyCompConsent(Consent consent) { // Note: Should never be possible to have more than one Consent record for a // given a particular subject and study component Criteria criteria = getSession().createCriteria(Consent.class); if (consent != null) { criteria.add(Restrictions.eq("study.id", consent.getStudy().getId())); // must only get consents for subject in context criteria.add(Restrictions.eq("linkSubjectStudy.id", consent.getLinkSubjectStudy().getId())); // must only get consents for specific studyComp criteria.add(Restrictions.eq("studyComp.id", consent.getStudyComp().getId())); // Do NOT constrain against consentStatus or consentDate here, because we want to be able to // tell if they are "Not Consented" vs "Consented" with different consentStatus or consentDate. // if (consent.getConsentStatus() != null) // {/* www. ja va 2 s . c om*/ // criteria.add(Restrictions.eq("consentStatus.id", consent.getConsentStatus().getId())); // } // // if (consent.getConsentDate() != null) // { // criteria.add(Restrictions.eq("consentDate", consent.getConsentDate())); // } } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("studyComp"), "studyComp"); projectionList.add(Projections.property("consentStatus"), "consentStatus"); projectionList.add(Projections.property("consentDate"), "consentDate"); criteria.setProjection(projectionList); criteria.setMaxResults(1); criteria.setResultTransformer(Transformers.aliasToBean(Consent.class)); Consent result = (Consent) criteria.uniqueResult(); return result; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<FieldDetailsDataRow> getPhenoFieldDetailsList(FieldDetailsReportVO fdrVO) { List<FieldDetailsDataRow> results = new ArrayList<FieldDetailsDataRow>(); Criteria criteria = getSession().createCriteria(PhenoDataSetCollection.class, "fpc"); criteria.createAlias("phenoCollection", "pc"); // Inner join to Field criteria.createAlias("field", "f"); // Inner join to Field criteria.createAlias("f.fieldType", "ft"); // Inner join to FieldType criteria.add(Restrictions.eq("study", fdrVO.getStudy())); if (fdrVO.getPhenoCollection() != null) { criteria.add(Restrictions.eq("phenoCollection", fdrVO.getPhenoCollection())); }/*from w w w . ja v a 2 s . c o m*/ if (fdrVO.getFieldDataAvailable()) { DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "fd"); // Join FieldPhenoCollection and FieldData on ID FK fieldDataCriteria.add(Property.forName("f.id").eqProperty("fd." + "field.id")); fieldDataCriteria.add(Property.forName("pc.id").eqProperty("fd." + "collection.id")); criteria.add(Subqueries.exists(fieldDataCriteria.setProjection(Projections.property("fd.id")))); } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("pc.name"), "collection"); projectionList.add(Projections.property("f.name"), "fieldName"); projectionList.add(Projections.property("f.description"), "description"); projectionList.add(Projections.property("f.minValue"), "minValue"); projectionList.add(Projections.property("f.maxValue"), "maxValue"); projectionList.add(Projections.property("f.encodedValues"), "encodedValues"); projectionList.add(Projections.property("f.missingValue"), "missingValue"); projectionList.add(Projections.property("f.units"), "units"); projectionList.add(Projections.property("ft.name"), "type"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(FieldDetailsDataRow.class)); criteria.addOrder(Order.asc("pc.id")); criteria.addOrder(Order.asc("f.name")); results = criteria.list(); return results; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<CustomFieldDetailsDataRow> getPhenoCustomFieldDetailsList(CustomFieldDetailsReportVO fdrVO) { List<CustomFieldDetailsDataRow> results = new ArrayList<CustomFieldDetailsDataRow>(); if (fdrVO.getCustomFieldDisplay() != null) { /*//from www . j a v a 2 s.c o m * Following query returns customFields whether or not they are * associated with a customFieldGroups (via customFieldDisplay) */ Criteria criteria = getSession().createCriteria(CustomField.class, "cf"); criteria.createAlias("customFieldDisplay", "cfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay criteria.createAlias("cfd.customFieldGroup", "cfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup criteria.createAlias("fieldType", "ft", JoinType.LEFT_OUTER_JOIN); // Left join to FieldType criteria.createAlias("unitType", "ut", JoinType.LEFT_OUTER_JOIN); // Left join to UnitType criteria.add(Restrictions.eq("cf.study", fdrVO.getStudy())); ArkFunction function = iArkCommonService .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION); criteria.add(Restrictions.eq("cf.arkFunction", function)); if (fdrVO.getCustomFieldDisplay().getCustomFieldGroup() != null) { criteria.add( Restrictions.eq("cfg.id", fdrVO.getCustomFieldDisplay().getCustomFieldGroup().getId())); } if (fdrVO.getFieldDataAvailable()) { DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd"); // Join CustomFieldDisplay and PhenoData on ID FK fieldDataCriteria.add(Property.forName("cfd.id").eqProperty("pd." + "customFieldDisplay.id")); criteria.add(Subqueries .exists(fieldDataCriteria.setProjection(Projections.property("pd.customFieldDisplay")))); } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("cfg.name"), "questionnaire"); projectionList.add(Projections.property("cf.name"), "fieldName"); projectionList.add(Projections.property("cf.description"), "description"); projectionList.add(Projections.property("cf.minValue"), "minValue"); projectionList.add(Projections.property("cf.maxValue"), "maxValue"); projectionList.add(Projections.property("cf.encodedValues"), "encodedValues"); projectionList.add(Projections.property("cf.missingValue"), "missingValue"); projectionList.add(Projections.property("ut.name"), "units"); projectionList.add(Projections.property("ft.name"), "type"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(CustomFieldDetailsDataRow.class)); criteria.addOrder(Order.asc("cfg.id")); criteria.addOrder(Order.asc("cfd.sequence")); results = criteria.list(); } return results; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<PhenoDataSetFieldDetailsDataRow> getPhenoDataSetFieldDetailsList( PhenoDataSetFieldDetailsReportVO reportVO) { List<PhenoDataSetFieldDetailsDataRow> results = new ArrayList<PhenoDataSetFieldDetailsDataRow>(); if (reportVO.getPhenoDataSetFieldDisplay() != null) { /*//ww w . j av a2 s . co m * Following query returns customFields whether or not they are * associated with a customFieldGroups (via customFieldDisplay) */ Criteria criteria = getSession().createCriteria(PhenoDataSetField.class, "pf"); criteria.createAlias("phenoDatasetFieldDisplay", "pdfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay criteria.createAlias("pdfd.phenoDatasetFieldGroup", "pdfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup criteria.createAlias("fieldType", "ft", JoinType.LEFT_OUTER_JOIN); // Left join to FieldType criteria.createAlias("unitType", "ut", JoinType.LEFT_OUTER_JOIN); // Left join to UnitType criteria.add(Restrictions.eq("pf.study", reportVO.getStudy())); ArkFunction function = iArkCommonService .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION); criteria.add(Restrictions.eq("pf.arkFunction", function)); if (reportVO.getPhenoDataSetFieldDisplay().getPhenoDataSetGroup() != null) { criteria.add(Restrictions.eq("pdfg.id", reportVO.getPhenoDataSetFieldDisplay().getPhenoDataSetGroup().getId())); } if (reportVO.getFieldDataAvailable()) { DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd"); // Join CustomFieldDisplay and PhenoData on ID FK fieldDataCriteria .add(Property.forName("pdfd.id").eqProperty("pd." + "phenoDatasetFieldDisplay.id")); criteria.add(Subqueries.exists( fieldDataCriteria.setProjection(Projections.property("pd.phenoDatasetFieldDisplay")))); } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("pdfg.name"), "questionnaire"); projectionList.add(Projections.property("pf.name"), "fieldName"); projectionList.add(Projections.property("pf.description"), "description"); projectionList.add(Projections.property("pf.minValue"), "minValue"); projectionList.add(Projections.property("pf.maxValue"), "maxValue"); projectionList.add(Projections.property("pf.encodedValues"), "encodedValues"); projectionList.add(Projections.property("pf.missingValue"), "missingValue"); projectionList.add(Projections.property("ut.name"), "units"); projectionList.add(Projections.property("ft.name"), "type"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(PhenoDataSetFieldDetailsDataRow.class)); criteria.addOrder(Order.asc("pdfg.id")); criteria.addOrder(Order.asc("pdfd.sequence")); results = criteria.list(); } return results; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<ResearcherDetailCostDataRow> getBillableItemDetailCostData( ResearcherCostResportVO researcherCostResportVO) { List<ResearcherDetailCostDataRow> results = new ArrayList<ResearcherDetailCostDataRow>(); Criteria criteria = getSession().createCriteria(BillableItem.class, "bi"); criteria.createAlias("workRequest", "wr", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("billableItemType", "bit", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("wr.researcher", "re", JoinType.LEFT_OUTER_JOIN); if (researcherCostResportVO.getResearcherId() != null) { criteria.add(Restrictions.eq("re.id", researcherCostResportVO.getResearcherId())); }/*from w ww . ja v a2 s .c o m*/ criteria.add(Restrictions.eq("bi.studyId", researcherCostResportVO.getStudyId())); criteria.add(Restrictions.eq("bi.invoice", researcherCostResportVO.getInvoice())); criteria.add(Restrictions.le("bi.commenceDate", researcherCostResportVO.getToDate())); criteria.add(Restrictions.ge("bi.commenceDate", researcherCostResportVO.getFromDate())); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("bi.description"), "description"); projectionList.add(Projections.property("bi.commenceDate"), "commencedDate"); projectionList.add(Projections.property("bi.invoice"), "invoice"); projectionList.add(Projections.property("bi.quantity"), "quantity"); projectionList.add(Projections.property("bi.totalCost"), "totalAmount"); projectionList.add(Projections.property("bi.totalGST"), "totalGST"); projectionList.add(Projections.property("bit.itemName"), "itemType"); projectionList.add(Projections.property("bit.id"), "typeId"); projectionList.add(Projections.property("bit.quantityType"), "quantityType"); projectionList.add(Projections.property("wr.gstAllow"), "gstAllowed"); projectionList.add(Projections.property("wr.name"), "requestName"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(ResearcherDetailCostDataRow.class)); criteria.addOrder(Order.asc("bit.id")); criteria.addOrder(Order.asc("bi.commenceDate")); results = criteria.list(); return results; }
From source file:au.org.theark.study.model.dao.StudyDao.java
License:Open Source License
public RelationshipVo getSubjectRelative(final String subjectUID, final Long studyId) { List<RelationshipVo> relatives = new ArrayList<RelationshipVo>(); Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "sub"); criteria.createAlias("sub.study", "substudy", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("sub.person", "subPerson", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("subPerson.genderType", "subGender", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("subPerson.vitalStatus", "subVitStatus", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("substudy.pedigreeConfiguration", "spc", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("spc.customField", "cf", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("cf.customFieldDisplay", "cfd", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("sub.subjectCustomFieldDataSet", "scfd", JoinType.LEFT_OUTER_JOIN, Restrictions.eqProperty("cfd.id", "scfd.customFieldDisplay.id")); criteria.add(Restrictions.eq("sub.subjectUID", subjectUID)); criteria.add(Restrictions.eq("substudy.id", studyId)); criteria.setFetchMode("subject", FetchMode.JOIN); criteria.setFetchMode("relative", FetchMode.JOIN); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("sub.subjectUID"), "individualId"); projectionList.add(Projections.property("subGender.name"), "gender"); projectionList.add(Projections.property("subPerson.dateOfBirth"), "dob"); projectionList.add(Projections.property("subPerson.dateOfDeath"), "dod"); projectionList.add(Projections.property("subVitStatus.name"), "deceased"); projectionList.add(Projections.property("scfd.textDataValue"), "affectedStatus"); criteria.setProjection(projectionList); criteria.setResultTransformer(Transformers.aliasToBean(RelationshipVo.class)); relatives = criteria.list();//from w w w .j a v a 2s . c o m return relatives.size() > 0 ? relatives.get(0) : null; }