Example usage for org.hibernate.sql JoinType LEFT_OUTER

Introduction

In this page you can find the example usage for org.hibernate.sql JoinType LEFT_OUTER_JOIN.

Prototype

JoinType LEFT_OUTER_JOIN

To view the source code for org.hibernate.sql JoinType LEFT_OUTER_JOIN.

Click Source Link

Usage

From source file:apm.modules.sys.service.RoleService.java

License:Open Source License

public List<Role> findAllRole() {
    //1.??5?/*  w  w  w  .  j  av a  2  s .  c  o m*/
    List<Role> rs = dao.findDefineRoles();

    //2.?
    User user = Users.currentUser();
    DetachedCriteria dc = dao.createDetachedCriteria();
    dc.createAlias("office", "office");
    dc.createAlias("userList", "userList", JoinType.LEFT_OUTER_JOIN);
    dc.add(dataScopeFilter(user, "office", "userList")); //??????
    dc.add(Restrictions.eq(Role.DEL_FLAG, Role.DEL_FLAG_NORMAL));
    dc.addOrder(Order.asc("office.code")).addOrder(Order.asc("name"));

    List<Role> rs2 = dao.find(dc);
    for (int i = 0; i < rs2.size(); i++) { //??
        Role r = rs2.get(i);
        if (!rs.contains(r)) {
            rs.add(r);
        }
    }
    return rs;
}

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  .  j  a v  a  2  s  . co  m*/
    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.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 v a  2 s .  com
            || (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.
 * //  w  ww.j av  a 2 s.c om
 * @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 w  w  w .  ja  v a2 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()));
    }

    // 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 w  w .j a va2 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());

    //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  w  w  w  . ja  va  2s. com
         * 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) {
        /*// w ww. ja v  a2 s.  c  om
         * 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();// ww w .  j av a2 s.  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   w  w  w.j av 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;
}

Example usage for org.hibernate.sql JoinType LEFT_OUTER_JOIN

Introduction

Prototype

Usage