List of usage examples for org.hibernate.criterion Projections groupProperty
public static PropertyProjection groupProperty(String propertyName)
From source file:org.jasig.ssp.dao.EarlyAlertResponseDao.java
License:Apache License
@SuppressWarnings(UNCHECKED) public List<EarlyAlertStudentReportTO> getPeopleByEarlyAlertReferralIds(final List<UUID> earlyAlertReferralIds, final String alertTermCode, final Date alertCreateDateFrom, final Date alertCreateDateTo, final Date responseCreateDateFrom, final Date responseCreateDateTo, final PersonSearchFormTO personSearchForm, final SortingAndPaging sAndP) throws ObjectNotFoundException { final Criteria criteria = createCriteria(); criteria.createAlias("earlyAlertReferralIds", "earlyAlertReferral"); criteria.createAlias("earlyAlert", "earlyAlert"); if (alertTermCode != null) { criteria.add(Restrictions.eq("earlyAlert.courseTermCode", alertTermCode)); }/*from w ww .ja va 2 s . com*/ if (alertCreateDateFrom != null) { criteria.add(Restrictions.ge("earlyAlert.createdDate", alertCreateDateFrom)); } if (alertCreateDateTo != null) { criteria.add(Restrictions.le("earlyAlert.createdDate", alertCreateDateTo)); } if (responseCreateDateFrom != null) { criteria.add(Restrictions.ge("createdDate", responseCreateDateFrom)); } if (responseCreateDateTo != null) { criteria.add(Restrictions.le("createdDate", responseCreateDateTo)); } if (earlyAlertReferralIds != null) { // EarlyAlertResponse->EarlyAlertReferral not modeled as an operational // join type, so no filtering on object status since for a direct // operational->reference association, the status of the reference type // does not matter criteria.add(Restrictions.in("earlyAlertReferral.id", earlyAlertReferralIds)); } Criteria personCriteria = criteria.createAlias("earlyAlert.person", "person"); setPersonCriteria(personCriteria, personSearchForm); List<UUID> ids = criteria.setProjection(Projections.distinct(Projections.property("id"))).list(); if (ids.size() == 0) { return new ArrayList<>(); } BatchProcessor<UUID, EarlyAlertStudentReportTO> processor = new BatchProcessor<UUID, EarlyAlertStudentReportTO>( ids); do { final Criteria collectionCriteria = createCriteria(); collectionCriteria.createAlias("earlyAlert", "earlyAlert"); collectionCriteria.createAlias("earlyAlert.person", "person"); collectionCriteria.createAlias("person.coach", "coach"); ProjectionList projections = Projections.projectionList().add(Projections .distinct(Projections.groupProperty("earlyAlert.id").as("early_alert_response_earlyAlertId"))); addBasicStudentProperties(projections, collectionCriteria); collectionCriteria.addOrder(Order.asc("person.lastName")); collectionCriteria.addOrder(Order.asc("person.firstName")); collectionCriteria.addOrder(Order.asc("person.middleName")); collectionCriteria.setProjection(projections).setResultTransformer( new NamespacedAliasToBeanResultTransformer(EarlyAlertStudentReportTO.class, "early_alert_response_")); processor.process(collectionCriteria, "id"); } while (processor.moreToProcess()); return processor.getSortedAndPagedResultsAsList(); }
From source file:org.jasig.ssp.dao.EarlyAlertResponseDao.java
License:Apache License
private ProjectionList addBasicStudentProperties(ProjectionList projections, Criteria criteria) { criteria.createAlias("person.specialServiceGroups", "personSpecialServiceGroups", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("person.programStatuses", "personProgramStatuses", JoinType.LEFT_OUTER_JOIN); projections.add(Projections.groupProperty("person.firstName").as("early_alert_response_firstName")); projections.add(Projections.groupProperty("person.middleName").as("early_alert_response_middleName")); projections.add(Projections.groupProperty("person.lastName").as("early_alert_response_lastName")); projections.add(Projections.groupProperty("person.schoolId").as("early_alert_response_schoolId")); projections.add(Projections.groupProperty("person.primaryEmailAddress") .as("early_alert_response_primaryEmailAddress")); projections.add(Projections.groupProperty("person.secondaryEmailAddress") .as("early_alert_response_secondaryEmailAddress")); projections.add(Projections.groupProperty("person.cellPhone").as("early_alert_response_cellPhone")); projections.add(Projections.groupProperty("person.homePhone").as("early_alert_response_homePhone")); projections.add(Projections.groupProperty("person.addressLine1").as("early_alert_response_addressLine1")); projections.add(Projections.groupProperty("person.addressLine2").as("early_alert_response_addressLine2")); projections.add(Projections.groupProperty("person.city").as("early_alert_response_city")); projections.add(Projections.groupProperty("person.state").as("early_alert_response_state")); projections.add(Projections.groupProperty("person.zipCode").as("early_alert_response_zipCode")); projections.add(Projections.groupProperty("person.id").as("early_alert_response_id")); criteria.createAlias("personSpecialServiceGroups.specialServiceGroup", "specialServiceGroup", JoinType.LEFT_OUTER_JOIN);/*from w w w . ja v a2 s . c o m*/ projections.add(Projections.groupProperty("personSpecialServiceGroups.objectStatus") .as("early_alert_response_specialServiceGroupAssocObjectStatus")); projections.add(Projections.groupProperty("specialServiceGroup.name") .as("early_alert_response_specialServiceGroupName")); projections.add(Projections.groupProperty("specialServiceGroup.id") .as("early_alert_response_specialServiceGroupId")); criteria.createAlias("personProgramStatuses.programStatus", "programStatus", JoinType.LEFT_OUTER_JOIN); projections .add(Projections.groupProperty("programStatus.name").as("early_alert_response_programStatusName")); projections.add( Projections.groupProperty("personProgramStatuses.id").as("early_alert_response_programStatusId")); projections.add(Projections.groupProperty("personProgramStatuses.expirationDate") .as("early_alert_response_programStatusExpirationDate")); // Join to Student Type criteria.createAlias("person.studentType", "studentType", JoinType.LEFT_OUTER_JOIN); // add StudentTypeName Column projections.add(Projections.groupProperty("studentType.name").as("early_alert_response_studentTypeName")); projections.add(Projections.groupProperty("studentType.code").as("early_alert_response_studentTypeCode")); Dialect dialect = ((SessionFactoryImplementor) sessionFactory).getDialect(); if (dialect instanceof SQLServerDialect) { // sql server requires all these to part of the grouping //projections.add(Projections.groupProperty("coach.id").as("coachId")); projections.add(Projections.groupProperty("coach.lastName").as("early_alert_response_coachLastName")) .add(Projections.groupProperty("coach.firstName").as("early_alert_response_coachFirstName")) .add(Projections.groupProperty("coach.middleName").as("early_alert_response_coachMiddleName")) .add(Projections.groupProperty("coach.schoolId").as("early_alert_response_coachSchoolId")) .add(Projections.groupProperty("coach.username").as("early_alert_response_coachUsername")); } else { // other dbs (postgres) don't need these in the grouping //projections.add(Projections.property("coach.id").as("coachId")); projections.add(Projections.groupProperty("coach.lastName").as("early_alert_response_coachLastName")) .add(Projections.groupProperty("coach.firstName").as("early_alert_response_coachFirstName")) .add(Projections.groupProperty("coach.middleName").as("early_alert_response_coachMiddleName")) .add(Projections.groupProperty("coach.schoolId").as("early_alert_response_coachSchoolId")) .add(Projections.groupProperty("coach.username").as("early_alert_response_coachUsername")); } return projections; }
From source file:org.jasig.ssp.dao.EarlyAlertResponseDao.java
License:Apache License
@SuppressWarnings("unchecked") public PagingWrapper<EntityStudentCountByCoachTO> getStudentEarlyAlertResponseCountByCoaches( EntityCountByCoachSearchForm form) { List<Person> coaches = form.getCoaches(); List<AuditPerson> auditCoaches = new ArrayList<AuditPerson>(); for (Person person : coaches) { auditCoaches.add(new AuditPerson(person.getId())); }/*from ww w .j a va 2 s . co m*/ BatchProcessor<AuditPerson, EntityStudentCountByCoachTO> processor = new BatchProcessor<AuditPerson, EntityStudentCountByCoachTO>( auditCoaches, form.getSAndP()); do { final Criteria query = createCriteria(); if (form.getCreateDateFrom() != null) { query.add(Restrictions.ge("createdDate", form.getCreateDateFrom())); } if (form.getCreateDateTo() != null) { query.add(Restrictions.le("createdDate", form.getCreateDateTo())); } query.createAlias("earlyAlert", "earlyAlert"); Criteria personCriteria = query.createAlias("earlyAlert.person", "person"); if (form.getStudentTypeIds() != null && !form.getStudentTypeIds().isEmpty()) { personCriteria.add(Restrictions.in("person.studentType.id", form.getStudentTypeIds())); } if (form.getServiceReasonIds() != null && !form.getServiceReasonIds().isEmpty()) { query.createAlias("person.serviceReasons", "serviceReasons"); query.createAlias("serviceReasons.serviceReason", "serviceReason"); query.add(Restrictions.in("serviceReason.id", form.getServiceReasonIds())); query.add(Restrictions.eq("serviceReasons.objectStatus", ObjectStatus.ACTIVE)); } if (form.getSpecialServiceGroupIds() != null && !form.getSpecialServiceGroupIds().isEmpty()) { query.createAlias("person.specialServiceGroups", "specialServiceGroups"); query.createAlias("specialServiceGroups.specialServiceGroup", "specialServiceGroup"); query.add(Restrictions.in("specialServiceGroup.id", form.getSpecialServiceGroupIds())); query.add(Restrictions.eq("specialServiceGroups.objectStatus", ObjectStatus.ACTIVE)); } query.setProjection(Projections.projectionList() .add(Projections.countDistinct("earlyAlert.person").as("earlyalertresponse_studentCount")) .add(Projections.countDistinct("id").as("earlyalertresponse_entityCount")) .add(Projections.groupProperty("earlyAlert.createdBy").as("earlyalertresponse_coach"))); query.setResultTransformer(new NamespacedAliasToBeanResultTransformer(EntityStudentCountByCoachTO.class, "earlyalertresponse_")); processor.process(query, "earlyAlert.createdBy"); } while (processor.moreToProcess()); return processor.getSortedAndPagedResults(); }
From source file:org.jasig.ssp.dao.JournalEntryDao.java
License:Apache License
@SuppressWarnings("unchecked") public PagingWrapper<EntityStudentCountByCoachTO> getStudentJournalCountForCoaches( EntityCountByCoachSearchForm form) { List<Person> coaches = form.getCoaches(); List<AuditPerson> auditCoaches = new ArrayList<AuditPerson>(); for (Person person : coaches) { auditCoaches.add(new AuditPerson(person.getId())); }//ww w . ja v a2s. c o m BatchProcessor<AuditPerson, EntityStudentCountByCoachTO> processor = new BatchProcessor<AuditPerson, EntityStudentCountByCoachTO>( auditCoaches, form.getSAndP()); do { final Criteria query = createCriteria(); setCriteria(query, form); query.setProjection( Projections.projectionList().add(Projections.countDistinct("person").as("journal_studentCount")) .add(Projections.countDistinct("id").as("journal_entityCount")) .add(Projections.groupProperty("createdBy").as("journal_coach"))) .setResultTransformer(new NamespacedAliasToBeanResultTransformer( EntityStudentCountByCoachTO.class, "journal_")); processor.process(query, "createdBy"); } while (processor.moreToProcess()); return processor.getSortedAndPagedResults(); }
From source file:org.jasig.ssp.dao.JournalEntryDao.java
License:Apache License
@SuppressWarnings("unchecked") public PagingWrapper<JournalStepStudentReportTO> getJournalStepStudentReportTOsFromCriteria( JournalStepSearchFormTO personSearchForm, SortingAndPaging sAndP) { final Criteria criteria = createCriteria(sAndP); setPersonCriteria(criteria, personSearchForm); if (personSearchForm.getCreateDateFrom() != null) { criteria.add(Restrictions.ge("createdDate", personSearchForm.getCreateDateFrom())); }//ww w .j av a 2 s. c o m if (personSearchForm.getCreateDateTo() != null) { criteria.add(Restrictions.le("createdDate", personSearchForm.getCreateDateTo())); } if (personSearchForm.getGetStepDetails()) { JoinType joinType = JoinType.INNER_JOIN; criteria.createAlias("journalEntryDetails", "journalEntryDetails", joinType); criteria.createAlias("journalEntryDetails.journalStepJournalStepDetail", "journalStepJournalStepDetail", joinType); criteria.createAlias("journalStepJournalStepDetail.journalStepDetail", "journalStepDetail", joinType); if (personSearchForm.getJournalStepDetailIds() != null && !personSearchForm.getJournalStepDetailIds().isEmpty()) { criteria.add(Restrictions.in("journalStepDetail.id", personSearchForm.getJournalStepDetailIds())); criteria.add(Restrictions.eq("journalEntryDetails.objectStatus", sAndP.getStatus())); criteria.add(Restrictions.eq("journalStepJournalStepDetail.objectStatus", sAndP.getStatus())); } } else { criteria.createAlias("journalEntryDetails", "journalEntryDetails", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("journalEntryDetails.journalStepJournalStepDetail", "journalStepJournalStepDetail", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("journalStepJournalStepDetail.journalStepDetail", "journalStepDetail", JoinType.LEFT_OUTER_JOIN); if (personSearchForm.getJournalStepDetailIds() != null && !personSearchForm.getJournalStepDetailIds().isEmpty()) { Criterion isNotIds = Restrictions .not(Restrictions.in("journalStepDetail.id", personSearchForm.getJournalStepDetailIds())); Criterion isNull = Restrictions.isNull("journalStepDetail.id"); criteria.add(Restrictions.or(isNotIds, isNull)); } else { criteria.add(Restrictions.isNull("journalStepDetail.id")); } } ProjectionList projections = Projections.projectionList(); projections.add(Projections.distinct( Projections.groupProperty("journalEntryDetails.id").as("journalentry_journalEntryDetailId"))); addBasicStudentProperties(projections, criteria); projections .add(Projections.groupProperty("journalStepDetail.name").as("journalentry_journalStepDetailName")); criteria.setProjection(projections); criteria.setResultTransformer( new NamespacedAliasToBeanResultTransformer(JournalStepStudentReportTO.class, "journalentry_")); if (criteria.list().size() > 1) { List<JournalStepStudentReportTO> reports = criteria.list(); Map<UUID, JournalStepStudentReportTO> cleanReports = new HashMap<UUID, JournalStepStudentReportTO>(); for (JournalStepStudentReportTO report : reports) { if (!cleanReports.containsKey(report.getJournalEntryDetailId())) { cleanReports.put(report.getJournalEntryDetailId(), report); } } List<JournalStepStudentReportTO> sortReports = Lists.newArrayList(cleanReports.values()); Collections.sort(sortReports, new Comparator<JournalStepStudentReportTO>() { public int compare(JournalStepStudentReportTO o1, JournalStepStudentReportTO o2) { JournalStepStudentReportTO p1 = (JournalStepStudentReportTO) o1; JournalStepStudentReportTO p2 = (JournalStepStudentReportTO) o2; int value = p1.getLastName().compareToIgnoreCase(p2.getLastName()); if (value != 0) return value; value = p1.getFirstName().compareToIgnoreCase(p2.getFirstName()); if (value != 0) return value; if (p1.getMiddleName() == null && p2.getMiddleName() == null) return 0; if (p1.getMiddleName() == null) return -1; if (p2.getMiddleName() == null) return 1; return p1.getMiddleName().compareToIgnoreCase(p2.getMiddleName()); } }); return new PagingWrapper<JournalStepStudentReportTO>(sortReports.size(), sortReports); } return new PagingWrapper<JournalStepStudentReportTO>(criteria.list().size(), (List<JournalStepStudentReportTO>) criteria.list()); }
From source file:org.jasig.ssp.dao.JournalEntryDao.java
License:Apache License
private ProjectionList addBasicStudentProperties(ProjectionList projections, Criteria criteria) { criteria.createAlias("person.staffDetails", "personStaffDetails", JoinType.LEFT_OUTER_JOIN); projections.add(Projections.groupProperty("person.firstName").as("journalentry_firstName")); projections.add(Projections.groupProperty("person.middleName").as("journalentry_middleName")); projections.add(Projections.groupProperty("person.lastName").as("journalentry_lastName")); projections.add(Projections.groupProperty("person.schoolId").as("journalentry_schoolId")); projections.add(// www .jav a2 s . c o m Projections.groupProperty("person.primaryEmailAddress").as("journalentry_primaryEmailAddress")); projections.add( Projections.groupProperty("person.secondaryEmailAddress").as("journalentry_secondaryEmailAddress")); projections.add(Projections.groupProperty("person.cellPhone").as("journalentry_cellPhone")); projections.add(Projections.groupProperty("person.homePhone").as("journalentry_homePhone")); projections.add(Projections.groupProperty("person.addressLine1").as("journalentry_addressLine1")); projections.add(Projections.groupProperty("person.addressLine2").as("journalentry_addressLine2")); projections.add(Projections.groupProperty("person.city").as("journalentry_city")); projections.add(Projections.groupProperty("person.state").as("journalentry_state")); projections.add(Projections.groupProperty("person.zipCode").as("journalentry_zipCode")); projections.add(Projections.groupProperty("person.id").as("journalentry_id")); criteria.createAlias("personSpecialServiceGroups.specialServiceGroup", "specialServiceGroup", JoinType.LEFT_OUTER_JOIN); projections.add(Projections.groupProperty("personSpecialServiceGroups.objectStatus") .as("journalentry_specialServiceGroupAssocObjectStatus")); criteria.createAlias("personProgramStatuses.programStatus", "programStatus", JoinType.LEFT_OUTER_JOIN); projections .add(Projections.groupProperty("specialServiceGroup.id").as("journalentry_specialServiceGroupId")); projections.add( Projections.groupProperty("specialServiceGroup.name").as("journalentry_specialServiceGroupName")); projections.add(Projections.groupProperty("programStatus.name").as("journalentry_programStatusName")); projections.add(Projections.groupProperty("personProgramStatuses.id").as("journalentry_programStatusId")); projections.add(Projections.groupProperty("personProgramStatuses.expirationDate") .as("journalentry_programStatusExpirationDate")); // Join to Student Type criteria.createAlias("person.studentType", "studentType", JoinType.LEFT_OUTER_JOIN); // add StudentTypeName Column projections.add(Projections.groupProperty("studentType.name").as("journalentry_studentTypeName")); projections.add(Projections.groupProperty("studentType.code").as("journalentry_studentTypeCode")); Dialect dialect = ((SessionFactoryImplementor) sessionFactory).getDialect(); if (dialect instanceof SQLServerDialect) { // sql server requires all these to part of the grouping //projections.add(Projections.groupProperty("coach.id").as("coachId")); projections.add(Projections.groupProperty("coach.lastName").as("journalentry_coachLastName")) .add(Projections.groupProperty("coach.firstName").as("journalentry_coachFirstName")) .add(Projections.groupProperty("coach.middleName").as("journalentry_coachMiddleName")) .add(Projections.groupProperty("coach.schoolId").as("journalentry_coachSchoolId")) .add(Projections.groupProperty("coach.username").as("journalentry_coachUsername")); } else { // other dbs (postgres) don't need these in the grouping //projections.add(Projections.property("coach.id").as("coachId")); projections.add(Projections.groupProperty("coach.lastName").as("journalentry_coachLastName")) .add(Projections.groupProperty("coach.firstName").as("journalentry_coachFirstName")) .add(Projections.groupProperty("coach.middleName").as("journalentry_coachMiddleName")) .add(Projections.groupProperty("coach.schoolId").as("journalentry_coachSchoolId")) .add(Projections.groupProperty("coach.username").as("journalentry_coachUsername")); } return projections; }
From source file:org.jasig.ssp.dao.PersonDao.java
License:Apache License
@SuppressWarnings("unchecked") public PagingWrapper<DisabilityServicesReportTO> getDisabilityReport(PersonSearchFormTO form, final SortingAndPaging sAndP) throws ObjectNotFoundException { List<UUID> ids = getStudentUUIDs(form); if (ids.size() == 0) return null; BatchProcessor<UUID, DisabilityServicesReportTO> processor = new BatchProcessor<UUID, DisabilityServicesReportTO>( ids, sAndP);// w w w . j a v a2 s . co m do { final Criteria criteria = createCriteria(); // don't bring back any non-students, there will likely be a better way // to do this later final ProjectionList projections = Projections.projectionList(); criteria.setProjection(projections); addBasicStudentProperties(projections, criteria, sAndP.getStatus()); Criteria demographics = criteria.createAlias("demographics", "demographics", JoinType.LEFT_OUTER_JOIN); demographics.createAlias("demographics.ethnicity", "ethnicity", JoinType.LEFT_OUTER_JOIN); demographics.createAlias("demographics.race", "race", JoinType.LEFT_OUTER_JOIN); demographics.createAlias("demographics.veteranStatus", "veteranStatus", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("disabilityAgencies", "disabilityAgencies", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("disabilityAgencies.disabilityAgency", "disabilityAgency", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("disabilityTypes", "personDisabilityTypes", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("personDisabilityTypes.disabilityType", "disabilityType", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("disability", "personDisability"); criteria.createAlias("personDisability.disabilityStatus", "disabilityStatus", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("educationGoal", "educationGoal", JoinType.LEFT_OUTER_JOIN); Dialect dialect = ((SessionFactoryImplementor) sessionFactory).getDialect(); if (dialect instanceof SQLServerDialect) { projections.add(Projections.groupProperty("ethnicity.name").as("ethnicity")); projections.add(Projections.groupProperty("race.name").as("race")); projections.add(Projections.groupProperty("veteranStatus.name").as("veteranStatus")); projections.add(Projections.groupProperty("disabilityAgency.name").as("disabilityAgencyName")); projections.add(Projections.groupProperty("disabilityType.name").as("disabilityType")); projections.add(Projections.groupProperty("disabilityAgency.createdDate") .as("disabilityAgencyCreatedDate")); projections.add(Projections.groupProperty("educationGoal.plannedMajor").as("major")); projections.add(Projections.groupProperty("disabilityStatus.name").as("odsStatus")); projections .add(Projections.groupProperty("personDisability.createdDate").as("odsRegistrationDate")); projections .add(Projections.groupProperty("personDisability.noDocumentation").as("noDocumentation")); projections.add(Projections.groupProperty("personDisability.inadequateDocumentation") .as("inadequateDocumentation")); projections.add(Projections.groupProperty("personDisability.noDisability").as("noDisability")); projections.add(Projections.groupProperty("personDisability.noSpecialEd").as("noSpecialEd")); } else { projections.add(Projections.groupProperty("ethnicity.name").as("ethnicity")); projections.add(Projections.groupProperty("race.name").as("race")); projections.add(Projections.groupProperty("veteranStatus.name").as("veteranStatus")); projections.add(Projections.groupProperty("disabilityType.name").as("disabilityType")); projections.add(Projections.groupProperty("disabilityAgency.name").as("disabilityAgencyName")); projections.add(Projections.groupProperty("disabilityAgency.createdDate") .as("disabilityAgencyCreatedDate")); projections.add(Projections.groupProperty("educationGoal.plannedMajor").as("major")); projections.add(Projections.groupProperty("disabilityStatus.name").as("odsStatus")); projections .add(Projections.groupProperty("personDisability.createdDate").as("odsRegistrationDate")); projections .add(Projections.groupProperty("personDisability.noDocumentation").as("noDocumentation")); projections.add(Projections.groupProperty("personDisability.inadequateDocumentation") .as("inadequateDocumentation")); projections.add(Projections.groupProperty("personDisability.noDisability").as("noDisability")); projections.add(Projections.groupProperty("personDisability.noSpecialEd").as("noSpecialEd")); } criteria.setResultTransformer(new AliasToBeanResultTransformer(DisabilityServicesReportTO.class)); processor.process(criteria, "id"); } while (processor.moreToProcess()); return new PagingWrapper<DisabilityServicesReportTO>(ids.size(), processor.getSortedAndPagedResultsAsList()); }
From source file:org.jasig.ssp.dao.PersonDao.java
License:Apache License
private ProjectionList addBasicStudentProperties(ProjectionList projections, Criteria criteria, ObjectStatus status) {//www . j a v a 2 s. c o m projections.add(Projections.groupProperty("firstName").as("firstName")); projections.add(Projections.groupProperty("middleName").as("middleName")); projections.add(Projections.groupProperty("lastName").as("lastName")); projections.add(Projections.groupProperty("schoolId").as("schoolId")); projections.add(Projections.groupProperty("primaryEmailAddress").as("primaryEmailAddress")); projections.add(Projections.groupProperty("secondaryEmailAddress").as("secondaryEmailAddress")); projections.add(Projections.groupProperty("cellPhone").as("cellPhone")); projections.add(Projections.groupProperty("homePhone").as("homePhone")); projections.add(Projections.groupProperty("addressLine1").as("addressLine1")); projections.add(Projections.groupProperty("addressLine2").as("addressLine2")); projections.add(Projections.groupProperty("city").as("city")); projections.add(Projections.groupProperty("state").as("state")); projections.add(Projections.groupProperty("zipCode").as("zipCode")); projections.add(Projections.groupProperty("actualStartTerm").as("actualStartTerm")); projections.add(Projections.groupProperty("actualStartYear").as("actualStartYear")); projections.add(Projections.groupProperty("id").as("id")); criteria.createAlias("programStatuses", "personProgramStatuses", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("specialServiceGroups", "personSpecialServiceGroups", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("personSpecialServiceGroups.specialServiceGroup", "specialServiceGroup", JoinType.LEFT_OUTER_JOIN); projections.add(Projections.groupProperty("specialServiceGroup.id").as("specialServiceGroupId")); projections.add(Projections.groupProperty("specialServiceGroup.name").as("specialServiceGroupName")); projections.add(Projections.groupProperty("personSpecialServiceGroups.objectStatus") .as("specialServiceGroupAssocObjectStatus")); criteria.createAlias("personProgramStatuses.programStatus", "programStatus", JoinType.LEFT_OUTER_JOIN); projections.add(Projections.groupProperty("programStatus.name").as("programStatusName")); projections.add(Projections.groupProperty("programStatus.id").as("programStatusId")); projections.add(Projections.groupProperty("personProgramStatuses.expirationDate") .as("programStatusExpirationDate")); // Join to Student Type criteria.createAlias("studentType", "studentType", JoinType.LEFT_OUTER_JOIN); // add StudentTypeName Column projections.add(Projections.groupProperty("studentType.name").as("studentTypeName")); projections.add(Projections.groupProperty("studentType.code").as("studentTypeCode")); criteria.createAlias("coach", "c"); criteria.createAlias("watchers", "watcher", JoinType.LEFT_OUTER_JOIN); Dialect dialect = ((SessionFactoryImplementor) sessionFactory).getDialect(); if (dialect instanceof SQLServerDialect) { // sql server requires all these to part of the grouping //projections.add(Projections.groupProperty("c.id").as("coachId")); projections.add(Projections.groupProperty("c.lastName").as("coachLastName")) .add(Projections.groupProperty("c.firstName").as("coachFirstName")) .add(Projections.groupProperty("c.middleName").as("coachMiddleName")) .add(Projections.groupProperty("c.schoolId").as("coachSchoolId")) .add(Projections.groupProperty("c.username").as("coachUsername")); } else { // other dbs (postgres) don't need these in the grouping //projections.add(Projections.property("c.id").as("coachId")); projections.add(Projections.groupProperty("c.lastName").as("coachLastName")) .add(Projections.groupProperty("c.firstName").as("coachFirstName")) .add(Projections.groupProperty("c.middleName").as("coachMiddleName")) .add(Projections.groupProperty("c.schoolId").as("coachSchoolId")) .add(Projections.groupProperty("c.username").as("coachUsername")); } return projections; }
From source file:org.jasig.ssp.dao.PlanDao.java
License:Apache License
@SuppressWarnings("unchecked") public List<PlanAdvisorCountTO> getPlanCountByOwner(SearchPlanTO form) { Criteria criteria = createCriteria(); if (form.getDateFrom() != null) criteria.add(Restrictions.ge("modifiedDate", form.getDateFrom())); if (form.getDateTo() != null) criteria.add(Restrictions.lt("modifiedDate", form.getDateTo())); criteria.add(Restrictions.eq("objectStatus", ObjectStatus.ACTIVE)); criteria.createAlias("owner", "owner"); criteria.setProjection(//from w w w .j a v a 2 s . c om Projections.projectionList().add(Projections.countDistinct("id").as("plan_entityCount")). // cannot just group by owner, else you get a N+1 // http://stackoverflow.com/questions/4330480/prevent-hibernate-n1-selects-when-grouping-by-an-entity // (plus EntityStudentCountByCoachTO doesn't map Persons, just AuditPersons) add(Projections.groupProperty("owner.id").as("plan_coachId")) .add(Projections.groupProperty("owner.firstName").as("plan_coachFirstName")) .add(Projections.groupProperty("owner.lastName").as("plan_coachLastName"))); List<EntityStudentCountByCoachTO> activePlansByCoaches = criteria .setResultTransformer( new NamespacedAliasToBeanResultTransformer(EntityStudentCountByCoachTO.class, "plan_")) .list(); criteria = createCriteria(); if (form.getDateFrom() != null) criteria.add(Restrictions.ge("modifiedDate", form.getDateFrom())); if (form.getDateTo() != null) criteria.add(Restrictions.lt("modifiedDate", form.getDateTo())); criteria.add(Restrictions.eq("objectStatus", ObjectStatus.INACTIVE)); criteria.createAlias("owner", "owner"); criteria.setProjection( Projections.projectionList().add(Projections.countDistinct("id").as("plan_entityCount")). // cannot just group by owner, else you get a N+1 // http://stackoverflow.com/questions/4330480/prevent-hibernate-n1-selects-when-grouping-by-an-entity // (plus EntityStudentCountByCoachTO doesn't map Persons, just AuditPersons) add(Projections.groupProperty("owner.id").as("plan_coachId")) .add(Projections.groupProperty("owner.firstName").as("plan_coachFirstName")) .add(Projections.groupProperty("owner.lastName").as("plan_coachLastName"))); List<EntityStudentCountByCoachTO> inactivePlansByCoaches = criteria .setResultTransformer( new NamespacedAliasToBeanResultTransformer(EntityStudentCountByCoachTO.class, "plan_")) .list(); Map<UUID, PlanAdvisorCountTO> results = new HashMap<UUID, PlanAdvisorCountTO>(); for (EntityStudentCountByCoachTO inactivePlan : inactivePlansByCoaches) { if (results.containsKey(inactivePlan.getCoach().getId())) { PlanAdvisorCountTO result = results.get(inactivePlan.getCoach().getId()); result.setInactivePlanCount(inactivePlan.getEntityCount()); } else { PlanAdvisorCountTO result = new PlanAdvisorCountTO(); result.setCoachName( inactivePlan.getCoach().getFirstName() + " " + inactivePlan.getCoach().getLastName()); result.setInactivePlanCount(inactivePlan.getEntityCount()); results.put(inactivePlan.getCoach().getId(), result); } } for (EntityStudentCountByCoachTO activePlan : activePlansByCoaches) { if (results.containsKey(activePlan.getCoach().getId())) { PlanAdvisorCountTO result = results.get(activePlan.getCoach().getId()); result.setActivePlanCount(activePlan.getEntityCount()); } else { PlanAdvisorCountTO result = new PlanAdvisorCountTO(); result.setCoachName( activePlan.getCoach().getFirstName() + " " + activePlan.getCoach().getLastName()); result.setActivePlanCount(activePlan.getEntityCount()); results.put(activePlan.getCoach().getId(), result); } } List<PlanAdvisorCountTO> sortedResults = Lists.newArrayList(results.values()); Collections.sort(sortedResults, PlanAdvisorCountTO.COACH_NAME_COMPARATOR); return sortedResults; }
From source file:org.jasig.ssp.dao.report.DisabilityServiceReportDao.java
License:Apache License
private ProjectionList addBasicStudentProperties(ProjectionList projections, Criteria criteria) { projections.add(Projections.property("id").as("personId")); projections.add(Projections.property("firstName").as("firstName")); projections.add(Projections.property("middleName").as("middleName")); projections.add(Projections.property("lastName").as("lastName")); projections.add(Projections.property("schoolId").as("schoolId")); projections.add(Projections.property("primaryEmailAddress").as("primaryEmailAddress")); projections.add(Projections.property("secondaryEmailAddress").as("secondaryEmailAddress")); // Join to Student Type criteria.createAlias("studentType", "studentType", JoinType.LEFT_OUTER_JOIN); // add StudentTypeName Column projections.add(Projections.property("studentType.name").as("studentType")); Dialect dialect = ((SessionFactoryImplementor) sessionFactory).getDialect(); if (dialect instanceof SQLServerDialect) { // sql server requires all these to part of the grouping projections.add(Projections.groupProperty("c.lastName").as("coachLastName")) .add(Projections.groupProperty("c.firstName").as("coachFirstName")) .add(Projections.groupProperty("c.middleName").as("coachMiddleName")) .add(Projections.groupProperty("c.schoolId").as("coachSchoolId")) .add(Projections.groupProperty("c.username").as("coachUsername")); } else {/*from ww w . j a v a 2s . com*/ // other dbs (postgres) don't need these in the grouping projections.add(Projections.property("c.lastName").as("coachLastName")) .add(Projections.property("c.firstName").as("coachFirstName")) .add(Projections.property("c.middleName").as("coachMiddleName")) .add(Projections.property("c.schoolId").as("coachSchoolId")) .add(Projections.property("c.username").as("coachUsername")); } return projections; }