List of usage examples for org.hibernate Criteria createAlias
@Deprecated public Criteria createAlias(String associationPath, String alias, int joinType) throws HibernateException;
From source file:au.org.theark.core.dao.ArkAuthorisationDao.java
License:Open Source License
@SuppressWarnings("unchecked") public ArrayList<ArkRole> getArkRoleLinkedToModule(ArkModule arkModule) { Collection<ArkModuleRole> arkModuleList = new ArrayList<ArkModuleRole>(); Criteria criteria = getSession().createCriteria(ArkModuleRole.class); criteria.add(Restrictions.eq("arkModule", arkModule)); criteria.createAlias("arkRole", "role", JoinType.LEFT_OUTER_JOIN); criteria.addOrder(Order.asc("role.name")); arkModuleList = criteria.list();/*from w w w . jav a2s .com*/ ArrayList<ArkRole> moduleArkRolesList = new ArrayList<ArkRole>(); for (ArkModuleRole arkModuleRole : arkModuleList) { ArkRole arkRole = arkModuleRole.getArkRole(); moduleArkRolesList.add(arkRole); } return moduleArkRolesList; }
From source file:au.org.theark.core.dao.StudyDao.java
License:Open Source License
public GenderType getSubjectGenderType(final String subjectUID, final Long studyId) { GenderType genderType = null;/*from w w w . j a va 2 s. c om*/ Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss"); criteria.createAlias("study", "st", JoinType.INNER_JOIN); criteria.createAlias("person", "per", JoinType.INNER_JOIN); criteria.createAlias("per.genderType", "gen", JoinType.INNER_JOIN); criteria.setFetchMode("person", FetchMode.JOIN); criteria.add(Restrictions.eq("st.id", studyId)); criteria.add(Restrictions.eq("lss.subjectUID", subjectUID)); List list = criteria.list(); if (list.size() > 0) { LinkSubjectStudy subject = (LinkSubjectStudy) list.get(0); genderType = subject.getPerson().getGenderType(); } return genderType; }
From source file:au.org.theark.lims.model.dao.InventoryDao.java
License:Open Source License
public InvCell getInvCellByLocationNames(String siteName, String freezerName, String rackName, String boxName, String row, String column) throws ArkSystemException { InvCell invCell = new InvCell(); if ((siteName == null || freezerName == null || rackName == null || boxName == null || row == null || column == null)//w w w .j a va 2 s. co m || (siteName.isEmpty() || freezerName.isEmpty() || rackName.isEmpty() || boxName.isEmpty() || row.isEmpty() || column.isEmpty())) { return invCell; } Long rowno; Long colno; if (StringUtils.isNumeric(row)) { rowno = new Long(row); } else { row = row.toUpperCase(); char c = (char) row.charAt(0); rowno = (long) ((char) row.charAt(0) - 64); } if (StringUtils.isNumeric(column)) { colno = new Long(column); } else { column = column.toUpperCase(); char c = (char) column.charAt(0); colno = (long) ((char) column.charAt(0) - 64); } Criteria criteria = getSession().createCriteria(InvCell.class); criteria.createAlias("invBox", "box", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("box.invRack", "rack", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("rack.invFreezer", "freezer", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("freezer.invSite", "site", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("biospecimen", "b", JoinType.LEFT_OUTER_JOIN); criteria.add(Restrictions.eq("site.name", siteName)); criteria.add(Restrictions.eq("freezer.name", freezerName)); criteria.add(Restrictions.eq("rack.name", rackName)); criteria.add(Restrictions.eq("box.name", boxName)); criteria.add(Restrictions.eq("rowno", rowno)); criteria.add(Restrictions.eq("colno", colno)); invCell = (InvCell) criteria.uniqueResult(); return invCell; }
From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java
License:Open Source License
/** * The method checks if the given questionnaire's fields have data linked to it. * //from w w w . ja va 2s.c o m * @param customFieldGroup */ public void isDataAvailableForQuestionnaire(CustomFieldGroup customFieldGroup) { 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.add(Restrictions.eq("cf.study", customFieldGroup.getStudy())); ArkFunction function = iArkCommonService .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_DATA_DICTIONARY); criteria.add(Restrictions.eq("cf.arkFunction", function)); criteria.add(Restrictions.eq("cfg.id", customFieldGroup.getId())); 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"); }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<ConsentDetailsDataRow> getStudyLevelConsentDetailsDataRowList(ConsentDetailsReportVO cdrVO) { List<ConsentDetailsDataRow> resultList = new ArrayList<ConsentDetailsDataRow>(0); Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss"); // Add study in context to criteria first (linkSubjectStudy on the VO should never be null) criteria.add(//from ww w .jav a 2 s . c o m 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())); } // we are dealing with study-level consent if (cdrVO.getConsentStatus() != null) { if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) { // Special-case: Treat the null FK for consentStatus as "Not Consented" criteria.add(Restrictions.or( Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS, cdrVO.getConsentStatus()), Restrictions.isNull(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS))); } else { criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS, cdrVO.getConsentStatus())); } } if (cdrVO.getConsentDate() != null) { criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTDATE, cdrVO.getConsentDate())); } criteria.createAlias("lss.consentStatus", "cs", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("lss.subjectStatus", "ss"); criteria.createAlias("lss.person", "p"); criteria.createAlias("lss.person.genderType", "genderType"); // Restrict any addresses to the preferred mailing address //Criteria addressCriteria = criteria.createAlias("lss.person.addresses", "a", JoinType.LEFT_OUTER_JOIN); // addressCriteria.setMaxResults(1); // addressCriteria.add(Restrictions.or(Restrictions.or(Restrictions.eq("a.preferredMailingAddress", true), Restrictions.isNull("a.preferredMailingAddress"),Restrictions.eq("a.preferredMailingAddress", false)))); criteria.createAlias("lss.person.addresses.country", "c", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("lss.person.addresses.state", "state", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("lss.person.phones", "phone", JoinType.LEFT_OUTER_JOIN); //TODO: Get work phone returned as well //Criteria phoneCriteria = criteria.createAlias("lss.person.phones.phoneType", "phoneType", JoinType.LEFT_OUTER_JOIN);/*.add( Restrictions.or(Restrictions.eq("phoneType.name", "Home"), ( Restrictions.or(Restrictions.or(Restrictions.eq("phoneType.name", "Home"), Restrictions.isNull("phoneType.name"),Restrictions.eq("phoneType.name", "Mobile"))) ) ) ));*/ //phoneCriteria.setMaxResults(1); criteria.createAlias("lss.person.titleType", "title"); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("lss.subjectUID"), "subjectUID"); projectionList.add(Projections.property("cs.name"), "consentStatus"); projectionList.add(Projections.property("ss.name"), "subjectStatus"); projectionList.add(Projections.property("title.name"), "title"); projectionList.add(Projections.property("p.firstName"), "firstName"); projectionList.add(Projections.property("p.lastName"), "lastName"); projectionList.add(Projections.property("a.streetAddress"), "streetAddress"); projectionList.add(Projections.property("a.city"), "suburb"); projectionList.add(Projections.property("a.postCode"), "postcode"); projectionList.add(Projections.property("state.name"), "state"); projectionList.add(Projections.property("c.name"), "country"); projectionList.add(Projections.property("phone.phoneNumber"), "homePhone"); projectionList.add(Projections.property("p.preferredEmail"), "email"); projectionList.add(Projections.property("genderType.name"), "sex"); projectionList.add(Projections.property("lss.consentDate"), "consentDate"); criteria.setProjection(projectionList); // only return fields required for report criteria.addOrder(Order.asc("lss.consentStatus")); // although MySQL causes NULLs to come first criteria.addOrder(Order.asc("lss.subjectUID")); criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class)); resultList = (criteria.list()); return resultList; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<ConsentDetailsDataRow> getStudyLevelConsentOtherIDDetailsDataRowList(ConsentDetailsReportVO cdrVO) { List<ConsentDetailsDataRow> resultList = new ArrayList<ConsentDetailsDataRow>(0); Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss"); // Add study in context to criteria first (linkSubjectStudy on the VO should never be null) criteria.add(/*from w ww. ja v a2s .c o m*/ 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())); } // we are dealing with study-level consent if (cdrVO.getConsentStatus() != null) { if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) { // Special-case: Treat the null FK for consentStatus as "Not Consented" criteria.add(Restrictions.or( Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS, cdrVO.getConsentStatus()), Restrictions.isNull(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS))); } else { criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS, cdrVO.getConsentStatus())); } } if (cdrVO.getConsentDate() != null) { criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTDATE, cdrVO.getConsentDate())); } criteria.createAlias("lss.consentStatus", "cs", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("lss.subjectStatus", "ss"); criteria.createAlias("lss.person", "p"); criteria.createAlias("lss.person.genderType", "genderType"); // Restrict any addresses to the preferred mailing address //Criteria addressCriteria = criteria.createAlias("lss.person.addresses", "a", JoinType.LEFT_OUTER_JOIN); // addressCriteria.setMaxResults(1); // addressCriteria.add(Restrictions.or(Restrictions.or(Restrictions.eq("a.preferredMailingAddress", true), Restrictions.isNull("a.preferredMailingAddress"),Restrictions.eq("a.preferredMailingAddress", false)))); criteria.createAlias("lss.person.addresses.country", "c", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("lss.person.addresses.state", "state", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("lss.person.phones", "phone", JoinType.LEFT_OUTER_JOIN); //TODO: Get work phone returned as well //Criteria phoneCriteria = criteria.createAlias("lss.person.phones.phoneType", "phoneType", JoinType.LEFT_OUTER_JOIN);/*.add( Restrictions.or(Restrictions.eq("phoneType.name", "Home"), ( Restrictions.or(Restrictions.or(Restrictions.eq("phoneType.name", "Home"), Restrictions.isNull("phoneType.name"),Restrictions.eq("phoneType.name", "Mobile"))) ) ) ));*/ //phoneCriteria.setMaxResults(1); criteria.createAlias("lss.person.titleType", "title"); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("lss.subjectUID"), "subjectUID"); projectionList.add(Projections.property("cs.name"), "consentStatus"); projectionList.add(Projections.property("ss.name"), "subjectStatus"); projectionList.add(Projections.property("title.name"), "title"); projectionList.add(Projections.property("p.firstName"), "firstName"); projectionList.add(Projections.property("p.lastName"), "lastName"); projectionList.add(Projections.property("a.streetAddress"), "streetAddress"); projectionList.add(Projections.property("a.city"), "suburb"); projectionList.add(Projections.property("a.postCode"), "postcode"); projectionList.add(Projections.property("state.name"), "state"); projectionList.add(Projections.property("c.name"), "country"); projectionList.add(Projections.property("phone.phoneNumber"), "homePhone"); projectionList.add(Projections.property("p.preferredEmail"), "email"); projectionList.add(Projections.property("genderType.name"), "sex"); projectionList.add(Projections.property("lss.consentDate"), "consentDate"); criteria.setProjection(projectionList); // only return fields required for report criteria.addOrder(Order.asc("lss.consentStatus")); // although MySQL causes NULLs to come first criteria.addOrder(Order.asc("lss.subjectUID")); criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class)); resultList = (criteria.list()); //removing duplicate entries from resultList List<ConsentDetailsDataRow> uniqueResults = new ArrayList(); Iterator<ConsentDetailsDataRow> iterator = resultList.iterator(); while (iterator.hasNext()) { ConsentDetailsDataRow o = iterator.next(); if (!uniqueResults.contains(o)) uniqueResults.add(o); } resultList.clear(); resultList.addAll(uniqueResults); for (int j = 0; j < resultList.size(); j++) { ConsentDetailsDataRow c = resultList.get(j); if (c.getOtherID() == null && c.getOtherIDSource() == null) { List<OtherID> otherIDs = null; try { otherIDs = iArkCommonService.getOtherIDs(iArkCommonService .getSubjectByUID(c.getSubjectUID(), cdrVO.getLinkSubjectStudy().getStudy()) .getPerson()); if (otherIDs.size() >= 1) { c.setOtherID(otherIDs.get(0).getOtherID()); c.setOtherIDSource(otherIDs.get(0).getOtherID_Source()); } if (otherIDs.size() > 1) { for (int i = 1; i < otherIDs.size(); i++) { OtherID o = otherIDs.get(i); ConsentDetailsDataRow clone = new ConsentDetailsDataRow(c.getSubjectUID(), o.getOtherID_Source(), o.getOtherID(), null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); resultList.add(clone); } } } catch (EntityNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return resultList; }
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 . ja va 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 a 2s.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<ResearcherCostDataRow> getResearcherBillableItemTypeCostData( final ResearcherCostResportVO researcherCostResportVO) { List<ResearcherCostDataRow> results = new ArrayList<ResearcherCostDataRow>(); 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("bit.billableItemTypeStatus", "bitst", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("wr.researcher", "re", JoinType.LEFT_OUTER_JOIN); criteria.add(Restrictions.eq("re.id", researcherCostResportVO.getResearcherId())); 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())); criteria.add(Restrictions.eq("bitst.name", "ACTIVE")); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.groupProperty("bit.id")); projectionList.add(Projections.property("bit.itemName"), "costType"); projectionList.add(Projections.sum("bi.totalCost"), "totalCost"); projectionList.add(Projections.sum("bi.totalGST"), "totalGST"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(ResearcherCostDataRow.class)); criteria.addOrder(Order.asc("bit.itemName")); results = criteria.list();//from w w w . ja v a2s . c o m 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 ww w . ja v a2 s .co 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; }