List of usage examples for org.hibernate.criterion ProjectionList add
public ProjectionList add(Projection projection)
From source file:es.sm2.openppm.core.dao.TimesheetDAO.java
License:Open Source License
/** * Calculate Fte for inputed hours in project * /*from w w w .ja va2 s.c o m*/ * @param project * @param member * @param firstWeekDay * @param lastWeekDay * @return */ public double getHoursResource(Project project, Employee member, Date firstWeekDay, Date lastWeekDay) { ProjectionList proList = Projections.projectionList(); proList.add(Projections.sum(Timesheet.HOURSDAY1)); proList.add(Projections.sum(Timesheet.HOURSDAY2)); proList.add(Projections.sum(Timesheet.HOURSDAY3)); proList.add(Projections.sum(Timesheet.HOURSDAY4)); proList.add(Projections.sum(Timesheet.HOURSDAY5)); proList.add(Projections.sum(Timesheet.HOURSDAY6)); proList.add(Projections.sum(Timesheet.HOURSDAY7)); Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(proList) .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3)) .add(Restrictions.eq(Timesheet.EMPLOYEE, member)); if (firstWeekDay != null && lastWeekDay != null) { crit.add(Restrictions.eq(Timesheet.INITDATE, firstWeekDay)) .add(Restrictions.eq(Timesheet.ENDDATE, lastWeekDay)); } // Aliases // crit.createAlias(Timesheet.PROJECTACTIVITY, "pa", CriteriaSpecification.LEFT_JOIN); // Projects or operations crit.add(Restrictions.disjunction().add(Restrictions.isNotNull(Timesheet.OPERATION)) .add(Restrictions.eq("pa." + Projectactivity.PROJECT, project))); Object[] hoursList = (Object[]) crit.uniqueResult(); double hours = 0; if (hoursList != null) { hours += (hoursList[0] == null ? 0 : (Double) hoursList[0]); hours += (hoursList[1] == null ? 0 : (Double) hoursList[1]); hours += (hoursList[2] == null ? 0 : (Double) hoursList[2]); hours += (hoursList[3] == null ? 0 : (Double) hoursList[3]); hours += (hoursList[4] == null ? 0 : (Double) hoursList[4]); hours += (hoursList[5] == null ? 0 : (Double) hoursList[5]); hours += (hoursList[6] == null ? 0 : (Double) hoursList[6]); } return hours; }
From source file:es.sm2.openppm.core.dao.TimesheetDAO.java
License:Open Source License
/** * Calculate Fte for inputed hours in project * @param project/*from w w w . j ava2 s . co m*/ * @param member * @param firstWeekDay * @param lastWeekDay * @return */ public double getHoursResource(Project project, Teammember member, Date firstWeekDay, Date lastWeekDay) { ProjectionList proList = Projections.projectionList(); proList.add(Projections.sum(Timesheet.HOURSDAY1)); proList.add(Projections.sum(Timesheet.HOURSDAY2)); proList.add(Projections.sum(Timesheet.HOURSDAY3)); proList.add(Projections.sum(Timesheet.HOURSDAY4)); proList.add(Projections.sum(Timesheet.HOURSDAY5)); proList.add(Projections.sum(Timesheet.HOURSDAY6)); proList.add(Projections.sum(Timesheet.HOURSDAY7)); Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(proList) .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3)); crit.createCriteria(Timesheet.EMPLOYEE).createCriteria(Employee.TEAMMEMBERS) .add(Restrictions.idEq(member.getIdTeamMember())); if (firstWeekDay != null && lastWeekDay != null) { crit.add(Restrictions.eq(Timesheet.INITDATE, firstWeekDay)) .add(Restrictions.eq(Timesheet.ENDDATE, lastWeekDay)); } if (project == null) { crit.add(Restrictions.eq(Timesheet.PROJECTACTIVITY, member.getProjectactivity())); } else { crit.createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.PROJECT, project)); } Object[] hoursList = (Object[]) crit.uniqueResult(); double hours = 0; if (hoursList != null) { hours += (hoursList[0] == null ? 0 : (Double) hoursList[0]); hours += (hoursList[1] == null ? 0 : (Double) hoursList[1]); hours += (hoursList[2] == null ? 0 : (Double) hoursList[2]); hours += (hoursList[3] == null ? 0 : (Double) hoursList[3]); hours += (hoursList[4] == null ? 0 : (Double) hoursList[4]); hours += (hoursList[5] == null ? 0 : (Double) hoursList[5]); hours += (hoursList[6] == null ? 0 : (Double) hoursList[6]); } return hours; }
From source file:fr.insalyon.creatis.vip.application.server.dao.hibernate.SimulationStatsData.java
License:Open Source License
@Override public List<String> getBySimulationID(List<String> simulationID) throws DAOException { //System.out.println("Calling SimulationStatsData: getBySimuID"); List<String> result = new ArrayList<String>(); try {/*w ww .j av a 2 s . co m*/ Session session = sessionFactory.openSession(); session.beginTransaction(); Criteria criteria = session.createCriteria(Stats.class); // 'workflowID' is the variable in the class Stats //criteria.add(Restrictions.eq("workflowID", simulationID)); criteria.add(Restrictions.in("workflowID", simulationID)); //criteria.addOrder(Order.desc("workflowID")); ProjectionList p = Projections.projectionList(); //p.add(Projections.sum("completed")); p.add(Projections.alias(Projections.sum("completed"), "sumCompleted")); p.add(Projections.alias(Projections.sum("completedWaitingTime"), "sumCompletedWaitingTime")); p.add(Projections.alias(Projections.sum("completedExecutionTime"), "sumCompletedExecutionTime")); p.add(Projections.alias(Projections.sum("completedInputTime"), "sumCompletedInputTime")); p.add(Projections.alias(Projections.sum("completedOutputTime"), "sumCompletedOutputTime")); p.add(Projections.sum("cancelled")); p.add(Projections.sum("cancelledWaitingTime")); p.add(Projections.sum("cancelledExecutionTime")); p.add(Projections.sum("cancelledInputTime")); p.add(Projections.sum("cancelledOutputTime")); p.add(Projections.sum("failedApplication")); p.add(Projections.sum("failedApplicationWaitingTime")); p.add(Projections.sum("failedApplicationExecutionTime")); p.add(Projections.sum("failedApplicationInputTime")); p.add(Projections.sum("failedApplicationOutputTime")); p.add(Projections.sum("failedInput")); p.add(Projections.sum("failedInputWaitingTime")); p.add(Projections.sum("failedInputExecutionTime")); p.add(Projections.sum("failedInputInputTime")); p.add(Projections.sum("failedInputOutputTime")); p.add(Projections.sum("failedOutput")); p.add(Projections.sum("failedOutputWaitingTime")); p.add(Projections.sum("failedOutputExecutionTime")); p.add(Projections.sum("failedOutputInputTime")); p.add(Projections.sum("failedOutputOutputTime")); p.add(Projections.sum("failedStalled")); p.add(Projections.sum("failedStalledWaitingTime")); p.add(Projections.sum("failedStalledExecutionTime")); p.add(Projections.sum("failedStalledInputTime")); p.add(Projections.sum("failedStalledOutputTime")); criteria.setProjection(p); List l = criteria.list(); session.getTransaction().commit(); session.close(); Iterator it = l.iterator(); while (it.hasNext()) { Object ob[] = (Object[]) it.next(); /* for (int i = 0; i < ob.length; i++) { System.out.println("Object " + i + " is " + ob[i]); if (ob[i] != null) { result.add(String.valueOf(ob[i])); } } * */ if (ob.length >= 30) { result.add("Completed Jobs##" + String.valueOf(ob[0]) + ""); result.add("CompletedJobs WaitingTime##" + String.valueOf(ob[1]) + ""); result.add("CompletedJobs ExecutionTime##" + String.valueOf(ob[2]) + ""); result.add("CompletedJobs InputTime##" + String.valueOf(ob[3]) + ""); result.add("CompletedJobs OutputTime##" + String.valueOf(ob[4]) + ""); result.add("Cancelled Jobs##" + String.valueOf(ob[5]) + ""); result.add("CancelledJobs WaitingTime##" + String.valueOf(ob[6]) + ""); result.add("CancelledJobs ExecutionTime##" + String.valueOf(ob[7]) + ""); result.add("CancelledJobs InputTime##" + String.valueOf(ob[8]) + ""); result.add("CancelledJobs OutputTime##" + String.valueOf(ob[9]) + ""); result.add("failedApplication Jobs##" + String.valueOf(ob[10]) + ""); result.add("failedApplicationJobs WaitingTime##" + String.valueOf(ob[11]) + ""); result.add("failedApplicationJobs ExecutionTime##" + String.valueOf(ob[12]) + ""); result.add("failedApplicationJobs InputTime##" + String.valueOf(ob[13]) + ""); result.add("failedApplicationJobs OutputTime##" + String.valueOf(ob[14]) + ""); result.add("failedInput Jobs##" + String.valueOf(ob[15]) + ""); result.add("failedInputJobs WaitingTime##" + String.valueOf(ob[16]) + ""); result.add("failedInputJobs ExecutionTime##" + String.valueOf(ob[17]) + ""); result.add("failedInputJobs InputTime##" + String.valueOf(ob[18]) + ""); result.add("failedInputJobs OutputTime##" + String.valueOf(ob[19]) + ""); result.add("failedInput Jobs##" + String.valueOf(ob[15]) + ""); result.add("failedInputJobs WaitingTime##" + String.valueOf(ob[16]) + ""); result.add("failedInputJobs ExecutionTime##" + String.valueOf(ob[17]) + ""); result.add("failedInputJpbs InputTime##" + String.valueOf(ob[18]) + ""); result.add("failedInputJobs OutputTime##" + String.valueOf(ob[19]) + ""); result.add("failedOutput Jobs##" + String.valueOf(ob[20]) + ""); result.add("failedOutputJobs WaitingTime##" + String.valueOf(ob[21]) + ""); result.add("failedOutputJobs ExecutionTime##" + String.valueOf(ob[22]) + ""); result.add("failedOutputJobs InputTime##" + String.valueOf(ob[23]) + ""); result.add("failedOutputJobs OutputTime##" + String.valueOf(ob[24]) + ""); result.add("failedStalled Jobs##" + String.valueOf(ob[25]) + ""); result.add("failedStalledJobs WaitingTime##" + String.valueOf(ob[26]) + ""); result.add("failedStalledJobs ExecutionTime##" + String.valueOf(ob[27]) + ""); result.add("failedStalledJobs InputTime##" + String.valueOf(ob[28]) + ""); result.add("failedStalledJobs OutputTime##" + String.valueOf(ob[29]) + ""); } else { throw new DAOException("getBySimulationID: Not enough data"); } } return result; } catch (HibernateException ex) { throw new DAOException(ex); } }
From source file:fr.insalyon.creatis.vip.application.server.dao.hibernate.SimulationStatsData.java
License:Open Source License
@Override public List<String> getWorkflowsPerUser(List<String> workflowsId) throws WorkflowsDBDAOException { List<String> result = new ArrayList<String>(); try {/*from www . ja v a 2 s . c o m*/ Session session = sessionFactory.openSession(); session.beginTransaction(); Criteria criteria = session.createCriteria(Workflow.class); criteria.add(Restrictions.in("id", workflowsId)); ProjectionList p = Projections.projectionList(); p.add(Projections.groupProperty("username")); p.add(Projections.property("username")); p.add(Projections.alias(Projections.count("status"), "nbWfls")); //p.add(Projections.count("status")); criteria.setProjection(p); criteria.addOrder(Order.desc("nbWfls")); List l = criteria.list(); session.getTransaction().commit(); session.close(); Iterator it = l.iterator(); while (it.hasNext()) { Object ob[] = (Object[]) it.next(); if (ob[0] != null && ob[1] != null) { result.add(String.valueOf(ob[0]) + "##" + String.valueOf(ob[2])); } } return result; } catch (HibernateException ex) { throw new WorkflowsDBDAOException(ex); } }
From source file:fr.insalyon.creatis.vip.application.server.dao.hibernate.SimulationStatsData.java
License:Open Source License
@Override public List<String> getApplications(List<String> workflowsId) throws WorkflowsDBDAOException { List<String> result = new ArrayList<String>(); try {//w ww. ja va 2 s .com Session session = sessionFactory.openSession(); session.beginTransaction(); Criteria criteria = session.createCriteria(Workflow.class); criteria.add(Restrictions.in("id", workflowsId)); ProjectionList p = Projections.projectionList(); p.add(Projections.groupProperty("application")); p.add(Projections.property("application")); p.add(Projections.alias(Projections.count("status"), "nbWfls")); criteria.setProjection(p); criteria.setProjection(p); criteria.addOrder(Order.desc("nbWfls")); List l = criteria.list(); session.getTransaction().commit(); session.close(); Iterator it = l.iterator(); while (it.hasNext()) { Object ob[] = (Object[]) it.next(); if (ob[0] != null && ob[1] != null) { result.add(String.valueOf(ob[0]) + "##" + String.valueOf(ob[2])); } } return result; } catch (HibernateException ex) { throw new WorkflowsDBDAOException(ex); } //System.out.println("getApplications, first result is " + result.get(0).toString()); }
From source file:gov.nih.nci.cananolab.service.sample.helper.AdvancedSampleServiceHelper.java
License:BSD License
/** * Find sample names based on advanced search parameters * //from w w w. ja va 2 s. c o m * @param searchBean * @return * @throws Exception */ public Map<String, String> findSampleIdNamesByAdvancedSearch(AdvancedSampleSearchBean searchBean) throws Exception { List<String> sampleIds = new ArrayList<String>(); Map<String, String> sampleIdNameMap = new HashMap<String, String>(); CaNanoLabApplicationService appService = (CaNanoLabApplicationService) ApplicationServiceProvider .getApplicationService(); ProjectionList projList = Projections.projectionList(); projList.add(Property.forName("id")); projList.add(Property.forName("name")); // AND or all empty if (searchBean.getLogicalOperator().equals("and") || searchBean.getSampleQueries().isEmpty() && searchBean.getCharacterizationQueries().isEmpty() && searchBean.getCompositionQueries().isEmpty()) { DetachedCriteria crit = DetachedCriteria.forClass(Sample.class, "rootCrit") .setProjection(Projections.distinct(projList)); //Projections.distinct(Property.forName("id"))); setSampleCriteria(searchBean, crit); setCompositionCriteria(searchBean, crit); setCharacterizationCriteria(searchBean, crit); List results = appService.query(crit); for (int i = 0; i < results.size(); i++) { Object[] row = (Object[]) results.get(i); String id = row[0].toString(); String name = row[1].toString(); logger.debug("id is: " + id); logger.debug("name is: " + name); String sampleId = id; sampleIds.add(sampleId); sampleIdNameMap.put(id, name); } } // OR union the results else { Set<String> sampleIdSet = new HashSet<String>(); // sample if (!searchBean.getSampleQueries().isEmpty()) { // ProjectionList projList = Projections.projectionList(); // projList.add(Property.forName("id")); // projList.add(Property.forName("name")); DetachedCriteria crit = DetachedCriteria.forClass(Sample.class, "rootCrit") .setProjection(Projections.distinct(projList)); //Projections.distinct(Property.forName("id"))); setSampleCriteria(searchBean, crit); List results = appService.query(crit); for (int i = 0; i < results.size(); i++) { Object[] row = (Object[]) results.get(i); String id = row[0].toString(); String name = row[1].toString(); logger.debug("id is: " + id); logger.debug("name is: " + name); String sampleId = id; sampleIds.add(sampleId); sampleIdNameMap.put(id, name); } } // // composition if (!searchBean.getCompositionQueries().isEmpty()) { if (searchBean.getCompositionLogicalOperator().equals("and")) { for (CompositionQueryBean query : searchBean.getCompositionQueries()) { List<String> subSampleIds = new ArrayList<String>(); DetachedCriteria crit = DetachedCriteria.forClass(Sample.class, "rootCrit") .setProjection(Projections.distinct(projList)); //Projections.distinct(Property.forName("id"))); setSampleCriteria(searchBean, crit); setCharacterizationCriteria(searchBean, crit); setCompositionCriteriaBase(searchBean, crit); if (query.getCompositionType().equals("function")) { DetachedCriteria subCrit = getFunctionSubquery(query, "inherentFunction.", "function.", "id"); crit.add(Subqueries.exists(subCrit)); } else if (query.getCompositionType().equals("nanomaterial entity")) { DetachedCriteria subCrit = getNanomaterialEntitySubquery(query, "nanoEntity.", "id"); crit.add(Subqueries.exists(subCrit)); } else if (query.getCompositionType().equals("functionalizing entity")) { DetachedCriteria subCrit = getFunctionalizingEntitySubquery(query, "funcEntity.", "id"); crit.add(Subqueries.exists(subCrit)); } List results = appService.query(crit); for (int i = 0; i < results.size(); i++) { Object[] row = (Object[]) results.get(i); String id = row[0].toString(); String name = row[1].toString(); logger.debug("id is: " + id); logger.debug("name is: " + name); String sampleId = id; //String sampleId = obj.toString(); subSampleIds.add(sampleId); sampleIdNameMap.put(id, name); } if (sampleIds.size() > 0) sampleIds.retainAll(subSampleIds); else sampleIds.addAll(subSampleIds); } } else { DetachedCriteria crit = DetachedCriteria.forClass(Sample.class, "rootCrit") .setProjection(Projections.distinct(projList)); //Projections.distinct(Property.forName("id"))); setCompositionCriteria(searchBean, crit); List results = appService.query(crit); for (int i = 0; i < results.size(); i++) { Object[] row = (Object[]) results.get(i); String id = row[0].toString(); String name = row[1].toString(); logger.debug("id is: " + id); logger.debug("name is: " + name); String sampleId = id; //String sampleId = obj.toString(); sampleIds.add(sampleId); sampleIdNameMap.put(id, name); } } } if (!searchBean.getCharacterizationQueries().isEmpty()) { // characterization DetachedCriteria crit = DetachedCriteria.forClass(Sample.class, "rootCrit") .setProjection(Projections.distinct(projList)); //Projections.distinct(Property.forName("id"))); setCharacterizationCriteria(searchBean, crit); List results = appService.query(crit); for (int i = 0; i < results.size(); i++) { Object[] row = (Object[]) results.get(i); String id = row[0].toString(); String name = row[1].toString(); logger.debug("id is: " + id); logger.debug("name is: " + name); String sampleId = id; //String sampleId = obj.toString(); sampleIds.add(sampleId); sampleIdNameMap.put(id, name); } } } Iterator<String> ite = sampleIdNameMap.keySet().iterator(); while (ite.hasNext()) { Long sampleId = Long.valueOf(ite.next()); if (!springSecurityAclService.currentUserHasReadPermission(sampleId, SecureClassesEnum.SAMPLE.getClazz()) && !springSecurityAclService.currentUserHasWritePermission(sampleId, SecureClassesEnum.SAMPLE.getClazz())) { logger.debug("User doesn't have access to sample with id " + sampleId); ite.remove(); } } //filter out redundant ones and non-accessible ones // List<String>filteredSampleIds=new ArrayList<String>(); // for (String sampleId: sampleIds) { // if (!filteredSampleIds.contains(sampleId) // && StringUtils.containsIgnoreCase(getAccessibleData(), // sampleId)) { // filteredSampleIds.add(sampleId); // } else { // ignore no access exception // logger.debug("User doesn't have access to sample with id " // + sampleId); // } // } return sampleIdNameMap; }
From source file:gov.nih.nci.cananolab.service.sample.helper.SampleServiceHelper.java
License:BSD License
public List<String> findSampleIdsByOwner(String currentOwner) throws Exception { List<String> sampleIds = new ArrayList<String>(); // can't query for the entire Sample object due to // limitations in pagination in SDK // Sample sample = new Sample(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("id")); DetachedCriteria crit = DetachedCriteria.forClass(Sample.class); crit.setProjection(Projections.distinct(projectionList)); Criterion crit1 = Restrictions.eq("createdBy", currentOwner); // in case of copy createdBy is like lijowskim:COPY Criterion crit2 = Restrictions.like("createdBy", currentOwner + ":", MatchMode.START); crit.add(Expression.or(crit1, crit2)); CaNanoLabApplicationService appService = (CaNanoLabApplicationService) ApplicationServiceProvider .getApplicationService();//from w w w . j av a 2 s.com List results = appService.query(crit); for (int i = 0; i < results.size(); i++) { String id = results.get(i).toString(); if (springSecurityAclService.currentUserHasReadPermission(Long.valueOf(id), SecureClassesEnum.SAMPLE.getClazz()) || springSecurityAclService.currentUserHasWritePermission(Long.valueOf(id), SecureClassesEnum.SAMPLE.getClazz())) { sampleIds.add(id); } else { logger.debug("User doesn't have access to sample of ID: " + id); } } return sampleIds; }
From source file:grails.orm.HibernateCriteriaBuilder.java
License:Apache License
/** * A distinct projection that takes a list * * @param propertyNames The list of distince property names * @param alias The alias to use/*from w w w . j ava2 s. c o m*/ */ @SuppressWarnings("rawtypes") public org.grails.datastore.mapping.query.api.Projections distinct(Collection propertyNames, String alias) { ProjectionList list = Projections.projectionList(); for (Object o : propertyNames) { list.add(Projections.property(calculatePropertyName(o.toString()))); } final Projection proj = Projections.distinct(list); addProjectionToList(proj, alias); return this; }
From source file:id.co.sambaltomat.core.dao.hibernate.GenericDaoHibernate.java
public int getRowCount(final List<Criterion> params, final List<JoinPath> joinPaths) { return (Integer) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Integer count = Integer.valueOf("0"); ProjectionList projectionList = Projections.projectionList(); Criteria criteria = session.createCriteria(persistentClass); processJoinPath(criteria, joinPaths, projectionList); if (params != null) { for (Criterion criterion : params) { criteria.add(criterion); }//w w w.j ava 2 s . c om } //jika projectionList tidak kosong berarti ada GROUPING, lakukan konversi criteria if (projectionList.getLength() > 0) { criteria.setProjection(projectionList); String sql = criteriaToString(criteria); sql = "select count(*) from (" + sql + ") as countTemp"; //native SQL dijalankan SQLQuery sqlQuery = session.createSQLQuery(sql); List result = sqlQuery.list(); if (result != null && result.size() > 0) { count = (Integer) result.get(0); } } else { criteria.setProjection(projectionList.add(Projections.rowCount())); List result = criteria.list(); if (result != null && result.size() > 0) { count = (Integer) result.get(0); } } return count; } }); }
From source file:id.co.sambaltomat.core.dao.hibernate.GenericDaoHibernate.java
protected void processJoinPath(Criteria criteria, List<JoinPath> joinPaths, ProjectionList projectionList) { boolean isDistinctRoot = true; if (joinPaths != null && !joinPaths.isEmpty()) { for (JoinPath joinPath : joinPaths) { if (joinPath.joinType == JoinType.INNER_JOIN) criteria.createCriteria(joinPath.path, joinPath.alias, CriteriaSpecification.INNER_JOIN); else if (joinPath.joinType == JoinType.LEFT_JOIN) criteria.createCriteria(joinPath.path, joinPath.alias, CriteriaSpecification.LEFT_JOIN); else if (joinPath.joinType == JoinType.NON_DISTINCT_ROOT_ENTITY) isDistinctRoot = false;/* w ww . j a va 2s . co m*/ else if (joinPath.joinType == JoinType.GROUPING_FIELD) { if (projectionList == null) { projectionList = Projections.projectionList(); } projectionList.add(Projections.groupProperty(joinPath.alias)); } else criteria.setFetchMode(joinPath.path, FetchMode.SELECT); } } if (isDistinctRoot) criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); }