Example usage for org.hibernate Query setResultTransformer

List of usage examples for org.hibernate Query setResultTransformer

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setResultTransformer

Introduction

In this page you can find the example usage for org.hibernate Query setResultTransformer.

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:com.scopix.periscope.evaluationmanagement.dao.ObservedSituationHibernateDAOImpl.java

License:Open Source License

@Override
public List<ObservedSituation> getObservedSituationListSQL(ObservedSituation observedSituation) {
    List<ObservedSituation> observedSituations = new ArrayList<ObservedSituation>();
    Session session = null;//w  ww . j a  va 2 s . co  m
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("select os.id, os.evaluation_state, os.observed_situation_date, os.situation_id ");
        sql.append("from observed_situation os ");
        if (observedSituation != null) {
            if (observedSituation.getEvaluationState() != null) {
                sql.append(" WHERE os.evaluation_state = '");
                sql.append(observedSituation.getEvaluationState().name()).append("'");
            }
        }
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        for (Map m : list) {
            ObservedSituation os = new ObservedSituation();
            os.setId((Integer) m.get("id"));
            os.setEvaluationState(EvaluationState.valueOf((String) m.get("evaluation_state")));
            os.setObservedSituationDate((Date) m.get("observed_situation_date"));
            Situation situation = new Situation();
            situation.setId((Integer) m.get("situation_id"));
            os.setSituation(situation);

            observedSituations.add(os);
        }
    } catch (Exception ex) {
        log.error("error = " + ex.getMessage());
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    return observedSituations;
}

From source file:com.scopix.periscope.evaluationmanagement.dao.ObservedSituationHibernateDAOImpl.java

License:Open Source License

@Override
public List<ObservedSituationEvaluationDTO> getObservedSituationEvaluationDTOs(Date startDate, Date endDate,
        List<Integer> situationTemplateIds, List<Integer> storeIds) {
    List<ObservedSituationEvaluationDTO> dtos = new ArrayList<ObservedSituationEvaluationDTO>();
    Session session = null;/*from  ww w  . j  a va2s  .  c  om*/
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    try {
        String idSTs = situationTemplateIds.toString().replaceAll("(\\[)|(\\])", "");
        String idStore = storeIds.toString().replaceAll("(\\[)|(\\])", "");
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT DISTINCT ");
        sql.append("os.id,   ");
        sql.append("s.description AS situation,  ");
        sql.append("os.observed_situation_date,  ");
        sql.append("ose.evaluation_result,  ");
        sql.append("ose.compliant,  ");
        sql.append("ose.rule_name,  ");
        sql.append("ose.metric1,  ");
        sql.append("ose.metric2,  ");
        sql.append("ose.metric3,  ");
        sql.append("ose.metric4,  ");
        sql.append("ose.metric5,  ");
        sql.append("ose.metric6,  ");
        sql.append("ose.metric7,  ");
        sql.append("ose.metric8,  ");
        sql.append("ose.metric9,  ");
        sql.append("ose.metric10,  ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id1 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName1,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id2 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName2,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id3 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName3,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id4 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName4,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id5 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName5,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id6 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName6,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id7 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName7,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id8 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName8,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id9 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName9,   ");
        sql.append("(select mt.description from metric_template mt, metric m, observed_metric om    ");
        sql.append("   where ose.metric_id10 = om.id   ");
        sql.append("   and m.id = om.metric_id   ");
        sql.append("   and mt.id = m.metric_template_id) AS metricName10,   ");
        sql.append("ose.sent_tomis,  ");
        sql.append("ose.sent_tomisdate,  ");
        sql.append("ose.target,  ");
        sql.append("ose.standard,  ");
        sql.append("ose.metric_count,  ");
        sql.append("ose.department,  ");
        sql.append("ose.product,  ");
        sql.append("store.description AS store_name,  ");
        sql.append("ose.state,  ");
        sql.append("ose.evaluation_date  ");
        sql.append("FROM   ");
        sql.append("observed_situation_evaluation ose,   ");
        sql.append("observed_situation os,  ");
        sql.append("situation s,  ");
        sql.append("place store ");
        sql.append("WHERE  ");
        sql.append("ose.store_id = store.id  ");
        sql.append("AND ose.observed_situation_id = os.id  ");
        sql.append("AND os.situation_id = s.id  ");
        sql.append("AND os.observed_situation_date >= to_date('").append(sdf.format(startDate))
                .append("', 'YYYY-MM-DD')  ");
        sql.append("AND os.observed_situation_date <= to_date('").append(sdf.format(endDate))
                .append("', 'YYYY-MM-DD')  ");
        sql.append("AND s.situation_template_id in (").append(idSTs).append(") ");
        sql.append("AND store.id in (").append(idStore).append(") ");
        sql.append("ORDER BY os.observed_situation_date, s.description ASC ");

        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        for (Map m : list) {
            ObservedSituationEvaluationDTO dto = new ObservedSituationEvaluationDTO();
            dto.setSituation((String) m.get("situation"));
            dto.setDate((Date) m.get("observed_situation_date"));
            dto.setEvaluationResult((Double) m.get("evaluation_result"));
            dto.setCompliant((Integer) m.get("compliant"));
            dto.setRuleName((String) m.get("rule_name"));
            dto.setMetric1((Double) m.get("metric1"));
            dto.setMetricName1((String) m.get("metricname1"));
            dto.setMetric2((Double) m.get("metric2"));
            dto.setMetricName2((String) m.get("metricname2"));
            dto.setMetric3((Double) m.get("metric3"));
            dto.setMetricName3((String) m.get("metricname3"));
            dto.setMetric4((Double) m.get("metric4"));
            dto.setMetricName4((String) m.get("metricname4"));
            dto.setMetric5((Double) m.get("metric5"));
            dto.setMetricName5((String) m.get("metricname5"));
            dto.setMetric6((Double) m.get("metric6"));
            dto.setMetricName6((String) m.get("metricname6"));
            dto.setMetric7((Double) m.get("metric7"));
            dto.setMetricName7((String) m.get("metricname7"));
            dto.setMetric8((Double) m.get("metric8"));
            dto.setMetricName8((String) m.get("metricname8"));
            dto.setMetric9((Double) m.get("metric9"));
            dto.setMetricName9((String) m.get("metricname9"));
            dto.setMetric10((Double) m.get("metric10"));
            dto.setMetricName10((String) m.get("metricname10"));
            dto.setSentToMIS((Boolean) m.get("sent_tomis"));
            dto.setSentToMISDate((Date) m.get("sent_tomisdate"));
            dto.setTarget((Double) m.get("target"));
            dto.setStandard((Double) m.get("standard"));
            dto.setMetricCount((Integer) m.get("metric_count"));
            dto.setDepartment((String) m.get("department"));
            dto.setProduct((String) m.get("product"));
            dto.setStoreName((String) m.get("store_name"));
            dto.setState((String) m.get("state"));
            dto.setEvaluationDate((Date) m.get("evaluation_date"));
            dtos.add(dto);
        }

    } catch (Exception ex) {
        log.error("error = " + ex.getMessage());
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    return dtos;
}

From source file:com.scopix.periscope.evaluationmanagement.dao.ObservedSituationHibernateDAOImpl.java

License:Open Source License

@Override
public List<IndicatorValuesDTO> getIndicatorValuesDTOs(Date startDate, Date endDate,
        List<Integer> situationTemplateIds, List<Integer> storeIds) {
    List<IndicatorValuesDTO> dtos = new ArrayList<IndicatorValuesDTO>();
    Session session = null;/*from  w  ww  .  jav  a 2  s  .c om*/
    Connection con = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    try {
        String idSTs = situationTemplateIds.toString().replaceAll("(\\[)|(\\])", "");
        String idStore = storeIds.toString().replaceAll("(\\[)|(\\])", "");
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT ");
        sql.append("s.description AS situation, ");
        sql.append("os.observed_situation_date, ");
        sql.append("i.name AS indicator_name, ");
        sql.append("iv.denominator, ");
        sql.append("iv.numerator, ");
        sql.append("iv.state, ");
        sql.append("iv.sent_tomis, ");
        sql.append("iv.sent_tomisdate, ");
        sql.append("store.description AS store_name, ");
        sql.append("iv.evaluation_date ");
        sql.append("FROM  ");
        sql.append("indicator_values iv, ");
        sql.append("indicator i, ");
        sql.append("place store, ");
        sql.append("observed_situation os, ");
        sql.append("situation s ");
        sql.append("WHERE ");
        sql.append("iv.observed_situation_id = os.id ");
        sql.append("AND s.id = os.situation_id ");
        sql.append("AND store.id = iv.store_id ");
        sql.append("AND i.id = iv.indicator_id ");
        sql.append("AND os.observed_situation_date >= to_date('").append(sdf.format(startDate))
                .append("', 'YYYY-MM-DD') ");
        sql.append("AND os.observed_situation_date <= to_date('").append(sdf.format(endDate))
                .append("', 'YYYY-MM-DD') ");
        sql.append("AND s.situation_template_id in (").append(idSTs).append(") ");
        sql.append("AND store.id in (").append(idStore).append(") ");
        sql.append("ORDER BY os.observed_situation_date, s.description ASC ");

        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        for (Map m : list) {
            IndicatorValuesDTO dto = new IndicatorValuesDTO();
            dto.setSituation((String) m.get("situation"));
            dto.setDate((Date) m.get("observed_situation_date"));
            dto.setDenominator((Double) m.get("denominator"));
            dto.setEvaluationDate((Date) m.get("evaluation_date"));
            dto.setIndicatorName((String) m.get("indicator_name"));
            dto.setNumerator((Double) m.get("numerator"));
            dto.setSentToMIS((Boolean) m.get("sent_tomis"));
            dto.setSentToMISDate((Date) m.get("sent_tomisdate"));
            dto.setState((String) m.get("state"));
            dto.setStoreName((String) m.get("store_name"));
            dtos.add(dto);
        }
    } catch (Exception ex) {
        log.error("error = " + ex.getMessage());
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    return dtos;

}

From source file:com.scopix.periscope.evaluationmanagement.dao.ObservedSituationHibernateDAOImpl.java

License:Open Source License

@Override
public List<Integer> getObservedSituationListSQL(int storeId, Date observedSituationDate,
        Integer[] situationTemplateIds) {
    //List<ObservedSituation> observedSituations = new ArrayList<ObservedSituation>();
    Session session = null;/*from w ww .j  a  v  a  2  s .  c o  m*/
    List<Integer> l = null;
    String situationTemplates = StringUtils.join(situationTemplateIds, ",");
    try {
        String patterns = "yyyy-MM-dd";
        StringBuilder sql = new StringBuilder();
        sql.append("select distinct os.id, os.evidence_date ");
        sql.append(" FROM place store, metric m, observed_metric om, situation s, ");
        sql.append(
                " observed_situation os LEFT JOIN pending_evaluation pe ON os.id = pe.observed_situation_id ");
        sql.append("WHERE ");
        sql.append(" pe.observed_situation_id IS NULL ");
        sql.append(" and os.observed_situation_date  = '");
        sql.append(DateFormatUtils.format(observedSituationDate, patterns)).append("' ");
        sql.append(" and om.observed_situation_id = os.id");
        sql.append(" and m.id = om.metric_id ");
        sql.append(" and store.id = m.store_id ");
        sql.append(" and s.id = os.situation_id");
        if (situationTemplates != null && situationTemplates.length() > 0) {
            sql.append(" and s.situation_template_id in(").append(situationTemplates).append(")");
        }
        sql.append(" and store.id = ").append(storeId).append("");
        sql.append(" order by os.evidence_date");
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());

        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        l = new ArrayList<Integer>();
        for (Map m : list) {
            Integer osId = (Integer) m.get("id");
            l.add(osId);
        }

        //l = (List<Integer>) query.list();
    } catch (Exception ex) {
        log.error("error = " + ex.getMessage());
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    //return observedSituations;
    return l;

}

From source file:com.scopix.periscope.evaluationmanagement.dao.PendingEvaluationHibernateDAOImpl.java

License:Open Source License

@Deprecated
@Override//from   w w  w  .  j a  v  a  2  s.c  o m
public List<PendingEvaluation> findPendingEvaluationListSQL(PendingEvaluation pendingEvaluation) {
    List<PendingEvaluation> pendingEvaluations = new ArrayList<PendingEvaluation>();
    Session session = null;
    try {
        StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT pe.id, pe.evaluation_queue, pe.evaluation_state, pe.observed_situation_id, pe.priority FROM ");
        sql.append("pending_evaluation pe ");
        StringBuilder where = new StringBuilder();
        if (pendingEvaluation != null) {
            if (pendingEvaluation.getEvaluationQueue() != null) {
                where.append(" WHERE pe.evaluation_queue = '")
                        .append(pendingEvaluation.getEvaluationQueue().name());
                where.append("'");
            }
            if (pendingEvaluation.getEvaluationState() != null) {
                if (where.length() == 0) {
                    where.append(" WHERE");
                } else {
                    where.append(" AND");
                }
                where.append(" pe.evaluation_state = '");
                where.append(pendingEvaluation.getEvaluationState().name()).append("'");
            }
            if (pendingEvaluation.getObservedSituation() != null
                    && pendingEvaluation.getObservedSituation().getId() != null) {
                if (where.length() == 0) {
                    where.append(" WHERE");
                } else {
                    where.append(" AND");
                }
                where.append(" pe.observed_situation_id = ");
                where.append(pendingEvaluation.getObservedSituation().getId());
            }
        }
        sql.append(where).append(" ORDER BY pe.priority");
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        int i = 1;
        StringBuilder updateBatch = new StringBuilder();
        for (Map m : list) {
            PendingEvaluation pe = new PendingEvaluation();
            pe.setId((Integer) m.get("id"));
            pe.setEvaluationQueue(EvaluationQueue.valueOf((String) m.get("evaluation_queue")));
            pe.setEvaluationState(EvaluationState.valueOf((String) m.get("evaluation_state")));
            ObservedSituation os = new ObservedSituation();
            os.setId((Integer) m.get("observed_situation_id"));
            pe.setObservedSituation(os);
            pe.setPriority(i);
            pendingEvaluations.add(pe);

            updateBatch.append("UPDATE pending_evaluation set priority = ").append(i).append(" WHERE id = ")
                    .append(pe.getId()).append(";");
            i++;
        }

        if (updateBatch.length() > 0) {
            session.createSQLQuery(updateBatch.toString()).executeUpdate();
        }
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    log.debug("pendingEvaluations = " + (pendingEvaluations != null ? pendingEvaluations.size() : null));
    return pendingEvaluations;
}

From source file:com.scopix.periscope.qualitycontrol.dao.QualityControlHibernateDAOImpl.java

License:Open Source License

@Override
public List<ObservedMetricResultDTO> getEvidenceFinishedList(FilteringData filter) throws ScopixException {
    log.debug("start");
    List<ObservedMetricResultDTO> listResult = new ArrayList<ObservedMetricResultDTO>();
    Session session = null;/*from  w  w w.  j  a  v  a2 s. c o  m*/
    try {
        //Al usar la sentencia "as <nombreAlias>", hibernate devuelve en la query el nombre con todo en minusculas
        //por lo tanto al recuperar dicho valor se debe hacer con el nombre en minusculas (de lo contrario no extrae
        //valor alguno), por ejemplo:
        //dto.setMetricType(MetricType.valueOf((String) m.get("metrictype")));
        StringBuilder sql = new StringBuilder();
        sql.append("select distinct data.evidenceId, ");
        sql.append(" ee.id as evidenceEvaluationId, ");
        sql.append(" ee.evidence_result as evidenceEvaluationResult,  ");
        sql.append(" data.observedSituationId, ");
        sql.append("  data.situationId, ");
        sql.append("  data.metricId, ");
        sql.append("  data.metricName,  ");
        sql.append("  data.observedMetricId, ");
        sql.append("  data.metricTemplateId, ");
        sql.append("  data.metricType, ");
        sql.append("  data.evidenceType,  ");
        sql.append("  a.id AS areaId, ");
        sql.append("  a.description AS area, ");
        sql.append("  data.evidenceDate  AS evidenceDate,  ");
        sql.append("  ess.evidence_path || c.name || '/' || s.name || '/' AS evidencePrePath,  ");
        sql.append("  ess.proof_path || c.name || '/' || s.name || '/' AS proofPrePath,  ");
        sql.append("  data.evidencePath, ");
        sql.append("  data.flvPath, ");
        sql.append("  ee.cant_do_reason as cantDoReason, ");
        sql.append("  ee.evaluation_user as userName,  ");
        sql.append("  data.metricEvaluation AS metricResult, ");
        sql.append("  ep.description AS cameraName,  ");
        sql.append(
                "  (select distinct repl.default_evidence_provider from relation_evidence_provider_location repl  ");
        sql.append("   where repl.evidence_provider_from_id = ep.id) as default_evidence_provider,  ");
        sql.append("  (select distinct repl.view_order from relation_evidence_provider_location repl  ");
        sql.append("   where repl.evidence_provider_from_id = ep.id) as view_order ");
        sql.append(" from  ");
        sql.append(" ( select distinct e.id AS evidenceId,  ");
        sql.append("      e.evidence_services_server_id,  ");
        sql.append("         e.evidence_date AS evidenceDate,  ");
        sql.append("         e.evidence_path AS evidencePath,  ");
        sql.append("         e.flv_path AS flvPath,  ");
        sql.append("         m.id AS metricId,  ");
        sql.append("         mt.description as metricName,  ");
        sql.append("         os.id AS observedSituationId,  ");
        sql.append("         os.situation_id as situationId,  ");
        sql.append("         mt.id AS metricTemplateId,  ");
        sql.append("         mt.metric_type_element AS metricType,  ");
        sql.append("         mt.evidence_type_element AS evidenceType,  ");
        sql.append("         m.extraction_plan_metric_id,  ");
        sql.append("         om.id as observedMetricId,  ");
        sql.append("         a.id as areaId, ");
        sql.append("         epc.store_id as storeId, ");
        sql.append("         reee.evidence_evaluation_id as evidenceEvaluationId, ");
        sql.append("         (select me.metric_evaluation_result  ");
        sql.append("          from metric_evaluation me  ");
        sql.append("     where om.id= me.observed_metric_id) as metricEvaluation, ");
        sql.append("      (select distinct ep2.id ");
        sql.append("   from evidence e2, ");
        sql.append("    rel_evidence_request_evidence rere2, ");
        sql.append("    evidence_request er2, ");
        sql.append("    evidence_provider ep2 ");
        sql.append("   where e2.id = e.id ");
        sql.append("    and rere2.evidence_id = e2.id ");
        sql.append("    and rere2.evidence_request_id = er2.id ");
        sql.append("    and er2.evidence_provider_id = ep2.id) as evidenceProviderId ");
        sql.append("    from  ");
        sql.append("         pending_evaluation pe,  ");
        sql.append("         observed_situation os,  ");
        sql.append("         rel_observed_metric_evidence rome,  ");
        sql.append("         observed_metric om,  ");
        sql.append("         metric m,  ");
        sql.append("         metric_template mt, ");
        sql.append("         evidence e ");
        sql.append("          left outer join rel_evidence_evaluation_evidence reee  ");
        sql.append("    on (reee.evidence_id = e.id) , ");
        sql.append("         extraction_plan_metric epm, ");
        sql.append("         extraction_plan_customizing epc, ");
        sql.append("      place a ");
        sql.append("    where   ");
        sql.append("         pe.evaluation_queue = 'OPERATOR'  ");
        sql.append("         and pe.evaluation_state = 'FINISHED'  ");
        sql.append("         and pe.observed_situation_id = os.id  ");
        sql.append("         and om.observed_situation_id = os.id  ");
        sql.append("         and rome.observed_metric_id = om.id  ");
        sql.append("         and rome.evidence_id = e.id  ");
        sql.append("         and om.metric_id = m.id  ");
        sql.append("         and m.metric_template_id = mt.id  ");
        sql.append("         and m.extraction_plan_metric_id = epm.id ");
        sql.append("         and epm.extraction_plan_customizing_id = epc.id ");
        sql.append("         and epc.area_type_id = a.area_type_id ");
        if (filter != null) {
            if (filter.getDate() != null) {
                sql.append("         and os.observed_situation_date = '");
                sql.append(DateFormatUtils.format(filter.getDate(), "yyyy-MM-dd")).append("' ");
            }
            if (filter.getArea() != null) {
                sql.append("         and a.id =").append(filter.getArea());
            }
            if (filter.getStore() != null) {
                sql.append("         and epc.store_id = ").append(filter.getStore());
            }
        }
        sql.append("    )  as data  ");
        sql.append("    left outer join rel_extraction_plan_metric_evidence_provider repmep  ");
        sql.append("     on (repmep.extraction_plan_metric_id = data.extraction_plan_metric_id ");
        sql.append("     and repmep.evidence_provider_id = data.evidenceProviderId) ");
        sql.append(" left outer join evidence_evaluation ee  ");
        sql.append("  on (ee.observed_metric_id = data.observedMetricId  ");
        sql.append("  and ee.id = data.evidenceEvaluationId  ");
        sql.append("  and ee.rejected = false), ");
        sql.append("    evidence_provider ep,  ");
        sql.append("    place a,  ");
        sql.append("    place s,  ");
        sql.append("    place c,  ");
        sql.append("    evidence_services_server ess  ");
        sql.append("where  ");
        sql.append("  ep.area_id = data.areaId ");
        sql.append("     and ep.store_id = data.storeId ");
        sql.append("     and ep.area_id = a.id  ");
        sql.append("     and a.store_id = s.id  ");
        sql.append("     and s.corporate_id = c.id  ");
        sql.append("     and ess.id = data.evidence_services_server_id  ");
        sql.append("     and repmep.evidence_provider_id = ep.id  ");
        sql.append("ORDER BY observedMetricId, evidenceId, evidenceDate, situationId ASC");

        session = this.getSession();
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        Integer id = null;
        ObservedMetricResultDTO dto = null;
        List<EvidenceDTO> evidenceDTOs = null;
        Set<Integer> evidenceId = new HashSet<Integer>();
        for (Map m : list) {
            if (id == null || !id.equals((Integer) m.get("observedmetricid"))) {
                if (dto != null) {
                    dto.setEvidences(evidenceDTOs);
                    listResult.add(dto);
                }
                evidenceId.clear();
                id = (Integer) m.get("observedmetricid");
                dto = new ObservedMetricResultDTO();
                dto.setSituationId((Integer) m.get("situationid"));
                dto.setMetricId((Integer) m.get("metricid"));
                dto.setMetricName((String) m.get("metricname"));
                dto.setObservedMetricId((Integer) m.get("observedmetricid"));
                dto.setMetricTemplateId((Integer) m.get("metrictemplateid"));
                dto.setMetricType(MetricType.valueOf((String) m.get("metrictype")));
                dto.setAreaId((Integer) m.get("areaid"));
                dto.setArea((String) m.get("area"));
                dto.setEvidenceDate((Date) m.get("evidencedate"));
                dto.setEvidencePrePath((String) m.get("evidenceprepath"));
                dto.setProofPrePath((String) m.get("proofprepath"));
                dto.setUserName((String) m.get("username"));
                if (m.get("metricresult") != null) {
                    dto.setMetricResult((Integer) m.get("metricresult"));
                } else {
                    dto.setMetricResult(-1);
                }

                evidenceDTOs = new ArrayList<EvidenceDTO>();
                EvidenceDTO evidenceDTO = generateDataEvidenceDTO(m);
                evidenceDTOs.add(evidenceDTO);
                evidenceId.add(evidenceDTO.getEvidenceId());

            } else {
                if (!evidenceId.contains((Integer) m.get("evidenceid"))) {
                    EvidenceDTO evidenceDTO = generateDataEvidenceDTO(m);
                    if (evidenceDTOs == null) {
                        evidenceDTOs = new ArrayList<EvidenceDTO>();
                    }
                    evidenceDTOs.add(evidenceDTO);
                    evidenceId.add(evidenceDTO.getEvidenceId());
                }
                if (dto != null && dto.getUserName() == null) {
                    dto.setUserName((String) m.get("username"));
                }
            }
        }
        if (dto != null) {
            dto.setEvidences(evidenceDTOs);
            listResult.add(dto);
        }
        listResult = this.addProofToResult(filter, listResult);
    } catch (Exception e) {
        log.error("error = " + e, e);
        //"periscopeexception.list.error", new String[]{
        throw new ScopixException("label.evidence", e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.debug("end, result = " + listResult);
    return listResult;
}

From source file:com.scopix.periscope.qualitycontrol.dao.QualityControlHibernateDAOImpl.java

License:Open Source License

@Override
public List<ObservedMetricResultDTO> getEvidenceFinishedListLowes(FilteringData filter) throws ScopixException {
    log.info("start");
    List<ObservedMetricResultDTO> listResult = new ArrayList<ObservedMetricResultDTO>();
    Session session = null;/* ww  w .j  av a 2  s  .  c  om*/
    try {
        //Al usar la sentencia "as <nombreAlias>", hibernate devuelve en la query el nombre con todo en minusculas
        //por lo tanto al recuperar dicho valor se debe hacer con el nombre en minusculas (de lo contrario no extrae
        //valor alguno), por ejemplo:
        //dto.setMetricType(MetricType.valueOf((String) m.get("metrictype")));
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT ");
        sql.append("e.id AS evidenceId,   ");
        sql.append("ee.id AS evidenceEvaluationId,   ");
        sql.append("ee.evidence_result AS evidenceEvaluationResult,   ");
        sql.append("os.id AS observedSituationId,  ");
        sql.append("os.situation_id as situationId,  ");
        sql.append("m.id AS metricId,   ");
        sql.append("mt.description as metricName,  ");
        sql.append("om.id AS observedMetricId,   ");
        sql.append("mt.id AS metricTemplateId,   ");
        sql.append("mt.metric_type_element AS metricType,   ");
        sql.append("mt.evidence_type_element AS evidenceType,   ");
        sql.append("a.id AS areaId,   ");
        sql.append("a.description AS area,   ");
        sql.append("e.evidence_date AS evidenceDate,   ");
        sql.append("ess.evidence_path || c.name || '/' || s.name || '/' AS evidencePrePath,   ");
        sql.append("ess.proof_path || c.name || '/' || s.name || '/' AS proofPrePath,   ");
        sql.append("e.evidence_path AS evidencePath,   ");
        sql.append("e.flv_path AS flvPath,   ");
        sql.append("ee.cant_do_reason AS cantDoReason,   ");
        sql.append("ee.evaluation_user AS userName ,   ");
        sql.append("(SELECT metric_evaluation_result FROM metric_evaluation where ");
        sql.append("observed_metric_id = om.id) AS metricResult,  ");
        sql.append("ep.description AS cameraName, ");
        sql.append(
                "(SELECT DISTINCT repl.default_evidence_provider FROM relation_evidence_provider_location repl WHERE ");
        sql.append("repl.evidence_provider_from_id = ep.id) AS default_evidence_provider, ");
        sql.append("(SELECT DISTINCT repl.view_order FROM relation_evidence_provider_location repl WHERE ");
        sql.append("repl.evidence_provider_from_id = ep.id) AS view_order ");
        sql.append("FROM   ");
        sql.append("evidence_evaluation ee,   ");
        sql.append("evidence e,   ");
        sql.append("observed_metric om,   ");
        sql.append("observed_situation os,  ");
        sql.append("metric m,   ");
        sql.append("metric_template mt,   ");
        sql.append("place a,   ");
        sql.append("evidence_services_server ess,   ");
        sql.append("place c,   ");
        sql.append("place s,  ");
        sql.append("rel_evidence_request_evidence rer,   ");
        sql.append("evidence_request er,   ");
        sql.append("evidence_provider ep, ");
        sql.append("rel_observed_metric_evidence rome, ");
        sql.append("pending_evaluation pe ");
        sql.append("WHERE  ");
        sql.append("ee.observed_metric_id = om.id ");
        sql.append("AND rome.observed_metric_id = om.id ");
        sql.append("AND e.id = rome.evidence_id ");
        sql.append("AND os.id = om.observed_situation_id ");
        sql.append("AND m.id = om.metric_id ");
        sql.append("AND mt.id = m.metric_template_id ");
        sql.append("AND m.area_id = a.id  ");
        sql.append("AND s.id = a.store_id   ");
        sql.append("AND c.id = s.corporate_id  ");
        sql.append("AND ess.id = e.evidence_services_server_id  ");
        sql.append("AND e.id = rer.evidence_id  ");
        sql.append("AND rer.evidence_request_id = er.id  ");
        sql.append("AND er.evidence_provider_id = ep.id ");
        sql.append("AND ee.rejected = false  ");
        sql.append("AND ee.observed_metric_id = om.id ");
        sql.append("AND pe.observed_situation_id = os.id ");
        sql.append("AND pe.evaluation_queue = 'OPERATOR' ");
        sql.append("AND pe.evaluation_state = 'FINISHED' ");

        if (filter != null) {
            if (filter.getDate() != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                sql.append(" AND om.observed_metric_date = '").append(sdf.format(filter.getDate())).append("'");
            }
            if (filter.getArea() != null) {
                sql.append(" AND a.id = ").append(filter.getArea());
            }
            if (filter.getStore() != null) {
                sql.append(" AND a.store_id = ").append(filter.getStore());
            }
        }
        sql.append(" ORDER BY om.id, e.id, e.evidence_date, os.situation_id ASC");
        session = this.getSession();
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        Integer id = null;
        ObservedMetricResultDTO dto = null;
        List<EvidenceDTO> evidenceDTOs = null;
        for (Map m : list) {
            if (id == null || !id.equals((Integer) m.get("observedmetricid"))) {
                if (dto != null) {
                    dto.setEvidences(evidenceDTOs);
                    listResult.add(dto);
                }
                id = (Integer) m.get("observedmetricid");
                dto = new ObservedMetricResultDTO();
                dto.setSituationId((Integer) m.get("situationid"));
                dto.setMetricId((Integer) m.get("metricid"));
                dto.setMetricName((String) m.get("metricname"));
                dto.setObservedMetricId((Integer) m.get("observedmetricid"));
                dto.setMetricTemplateId((Integer) m.get("metrictemplateid"));
                dto.setMetricType(MetricType.valueOf((String) m.get("metrictype")));
                dto.setAreaId((Integer) m.get("areaid"));
                dto.setArea((String) m.get("area"));
                dto.setEvidenceDate((Date) m.get("evidencedate"));
                dto.setEvidencePrePath((String) m.get("evidenceprepath"));
                dto.setProofPrePath((String) m.get("proofprepath"));
                dto.setUserName((String) m.get("username"));
                if (m.get("metricresult") != null) {
                    dto.setMetricResult((Integer) m.get("metricresult"));
                } else {
                    dto.setMetricResult(-1);
                }

                evidenceDTOs = new ArrayList<EvidenceDTO>();
                EvidenceDTO evidenceDTO = generateDataEvidenceDTO(m);
                evidenceDTOs.add(evidenceDTO);

            } else {
                EvidenceDTO evidenceDTO = generateDataEvidenceDTO(m);
                if (evidenceDTOs == null) {
                    evidenceDTOs = new ArrayList<EvidenceDTO>();
                }
                evidenceDTOs.add(evidenceDTO);
            }
        }
        if (dto != null) {
            dto.setEvidences(evidenceDTOs);
            listResult.add(dto);
        }
        listResult = this.addProofToResult(filter, listResult);
    } catch (Exception e) {
        log.error("error = " + e, e);
        //"periscopeexception.list.error", new String[]{
        throw new ScopixException("label.evidence", e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.info("end, result = " + listResult.size());
    return listResult;
}

From source file:com.scopix.periscope.qualitycontrol.dao.QualityControlHibernateDAOImpl.java

License:Open Source License

private List<ObservedMetricResultDTO> addProofToResult(FilteringData filter,
        List<ObservedMetricResultDTO> listParam) throws ScopixException {
    log.debug("start");
    Session session = null;/*from  ww  w.  j a v a  2  s. co  m*/
    try {
        //Buscar los proofs y agregarselos a la evidencia correspondiente
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT  ");
        sql.append("p.proof_date,  ");
        sql.append("p.id as proof_id,  ");
        sql.append("p.path_with_marks,  ");
        sql.append("p.path_without_marks,  ");
        sql.append("p.proof_order, ee.id as evidence_evaluation_id,  ");
        sql.append("om.id as observed_metric_id,  ");
        sql.append("ee.pending_evaluation_id,   ");
        sql.append("p.evidence_id, ");
        sql.append("p.proof_result ");
        sql.append("FROM  ");
        sql.append("proof p, ");
        sql.append("evidence_evaluation ee, ");
        sql.append("observed_metric om, ");
        sql.append("metric m, ");
        sql.append("place a ");
        sql.append("WHERE m.id = om.metric_id ");
        sql.append("AND ee.observed_metric_id = om.id ");
        sql.append("AND a.id = m.area_id ");
        sql.append("AND p.evidence_evaluation_id = ee.id ");
        sql.append("AND ee.rejected = false ");
        if (filter != null) {
            if (filter.getDate() != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                sql.append(" AND om.observed_metric_date = '");
                sql.append(sdf.format(filter.getDate()));
                sql.append("'");
            }
            if (filter.getArea() != null) {
                sql.append(" AND a.id = ");
                sql.append(filter.getArea());
            }
            if (filter.getStore() != null) {
                sql.append(" AND a.store_id = ");
                sql.append(filter.getStore());
            }
        }
        sql.append(" ORDER BY om.id, p.evidence_id");
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        Collections.sort(listParam);
        for (Map m : list) {
            Integer omId = (Integer) m.get("observed_metric_id");
            Integer evidenceId = (Integer) m.get("evidence_id");
            ProofDTO proofDTO = new ProofDTO();
            proofDTO.setProofId((Integer) m.get("proof_id"));
            proofDTO.setOrder((Integer) m.get("proof_order"));
            proofDTO.setPathWithMarks((String) m.get("path_with_marks"));
            proofDTO.setPathWithoutMarks((String) m.get("path_without_marks"));
            proofDTO.setProofResult((Integer) m.get("proof_result"));

            ObservedMetricResultDTO omrdto = new ObservedMetricResultDTO();
            omrdto.setObservedMetricId(omId);
            int index = Collections.binarySearch(listParam, omrdto);
            if (index >= 0) {
                omrdto = listParam.get(index);
                List<EvidenceDTO> evidences = omrdto.getEvidences();
                Collections.sort(listParam);
                EvidenceDTO evidenceDTO = new EvidenceDTO();
                evidenceDTO.setEvidenceId(evidenceId);
                index = Collections.binarySearch(evidences, evidenceDTO);
                if (index >= 0) {
                    evidenceDTO = evidences.get(index);
                    evidenceDTO.getProofs().add(proofDTO);
                }
            }
        }
    } catch (Exception e) {
        log.error("error = " + e, e);
        //"periscopeexception.list.error", new String[]{
        throw new ScopixException("label.evidence", e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e, e);
        }
    }
    log.debug("end, result = " + listParam);
    return listParam;
}

From source file:com.scopix.periscope.qualitycontrol.dao.QualityControlHibernateDAOImpl.java

License:Open Source License

@Override
public List<EvidenceFinishedDTO> getEvidenceFinishedList(Date start, Date end, boolean rejected)
        throws ScopixException {
    List<EvidenceFinishedDTO> listResult = new ArrayList<EvidenceFinishedDTO>();
    Session session = null;/* www .j  a  v a 2 s.c  om*/
    //        PreparedStatement st = null;
    //        ResultSet rs = null;
    //        Connection con = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("select distinct ");
        sql.append("p.description as area , ");
        sql.append("e.evidence_date, ");
        sql.append("ee.evidence_result, ");
        sql.append("ee.cant_do_reason, ");
        sql.append("om.id as metric_id, ");
        sql.append("e.evidence_path, ");
        sql.append("ep.description as camera, ");
        sql.append("mt.metric_type_element, ");
        sql.append("mt.evaluation_instruction, ");
        sql.append("ee.evaluation_user, ");
        sql.append("ee.rejected, ");
        sql.append("ee.evaluation_date, ");
        sql.append("ee.id AS evidence_evaluation_id, ");
        sql.append("ee.init_evaluation, ");
        sql.append("ee.end_evaluation, ");
        sql.append("ee.evaluation_time_in_seconds, ");
        sql.append("store.description AS store_name ");
        sql.append("from evidence_evaluation ee, ");
        sql.append("evidence e, ");
        sql.append("observed_metric om, ");
        sql.append("metric m, ");
        sql.append("metric_template mt, ");
        sql.append("place p, ");
        sql.append("place store, ");
        sql.append("rel_evidence_request_evidence rel, ");
        sql.append("evidence_request er, ");
        sql.append("evidence_provider ep, ");
        sql.append("rel_evidence_evaluation_evidence reee ");
        sql.append("where reee.evidence_id = e.id ");
        sql.append("and reee.evidence_evaluation_id = ee.id ");
        sql.append("and ee.observed_metric_id = om.id ");
        sql.append("and om.metric_id = m.id ");
        sql.append("and m.metric_template_id = mt.id ");
        sql.append("and m.area_id = p.id ");
        sql.append("and rel.evidence_id = e.id ");
        sql.append("and rel.evidence_request_id = er.id ");
        sql.append("and er.evidence_provider_id = ep.id ");
        sql.append("and e.evidence_date >= '");
        sql.append(sdf.format(start));
        sql.append(" 00:00:00' ");
        sql.append("and e.evidence_date <= '");
        sql.append(sdf.format(end));
        sql.append(" 23:59:59' ");
        sql.append("and m.store_id = store.id ");

        if (!rejected) {
            sql.append("and ee.rejected = false ");
        }
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        Set<Integer> eeId = new HashSet<Integer>();
        for (Map m : list) {
            if (!eeId.contains((Integer) m.get("evidence_evaluation_id"))) {
                EvidenceFinishedDTO dto = new EvidenceFinishedDTO();
                dto.setArea((String) m.get("area"));
                dto.setEvidenceDate((Date) m.get("evidence_date"));
                dto.setEvidenceEvaluationResult((Integer) m.get("evidence_result"));
                dto.setCantDoReason((String) m.get("cant_do_reason"));
                dto.setObservedMetricId((Integer) m.get("metric_id"));
                dto.setEvidencePath((String) m.get("evidence_path"));
                dto.setProvider((String) m.get("camera"));
                dto.setMetricType(MetricType.valueOf((String) m.get("metric_type_element")));
                dto.setEvaluationInstruction((String) m.get("evaluation_instruction"));
                dto.setUserName((String) m.get("evaluation_user"));
                dto.setRejected((Boolean) m.get("rejected"));
                dto.setEvaluationDate((Date) m.get("evaluation_date"));
                dto.setInitEvaluationDate((Date) m.get("init_evaluation"));
                dto.setEndEvaluationDate((Date) m.get("end_evaluation"));
                dto.setEvaluationTimeInSeconds(m.get("evaluation_time_in_seconds") == null ? null
                        : ((BigInteger) m.get("evaluation_time_in_seconds")).longValue());
                dto.setStoreName((String) m.get("store_name"));
                listResult.add(dto);

                eeId.add((Integer) m.get("evidence_evaluation_id"));
            }
        }
    } catch (Exception e) {
        log.error("Error " + e, e);
        //"periscopeexception.list.error", new String[]{
        throw new ScopixException("tab.qualitycontrol.summary", e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.debug("end, result = " + listResult);

    return listResult;
}

From source file:com.scopix.periscope.queuemanagement.dao.QueueManagementHibernateDAO.java

License:Open Source License

/**
 *
 * @param filter Filtros para retornar lista
 * @return List<PendingEvaluationDTO> generada para filtros
 * @throws ScopixException Excepcion en caso de error
 *//*from   ww w .j a v a 2  s  .  c o m*/
public List<PendingEvaluationDTO> getPendingEvaluationList(FilteringData filter) throws ScopixException {
    log.info("start");
    List<PendingEvaluationDTO> pendingEvaluationDTOs = new ArrayList<PendingEvaluationDTO>();
    Session session = null;
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT   ");
        sql.append(" pe.id AS pendingEvaluationId,    ");
        sql.append(" pe.user_name AS userName,    ");
        sql.append(" st.name AS situationTemplate,    ");
        sql.append(" st.id AS situationTemplateId,    ");
        sql.append(" a.description AS area,    ");
        sql.append(" a.id AS areaId,    ");
        sql.append(" mt.description AS description,    ");
        sql.append(" mt.metric_type_element AS type,    ");
        sql.append(" e.evidence_date AS date,    ");
        sql.append(" os.id AS observedSituationId,    ");
        sql.append(" pe.evaluation_state AS state,    ");
        sql.append(" pe.evaluation_queue AS queue,    ");
        sql.append(" pe.priority AS priority,    ");
        sql.append(" store.description AS store_description,     ");
        //sql.append(" e.id AS evidenceId,    ");
        sql.append(" e.evidence_date AS evidenceDate,    ");
        sql.append(" p.description  AS product,  ");
        sql.append(" m.metric_order   ");
        sql.append("FROM  ");
        sql.append("pending_evaluation pe, ");
        sql.append("observed_situation os,  ");
        sql.append("situation s, ");
        sql.append("situation_template st, ");
        sql.append("observed_metric om, ");
        sql.append("metric m, ");
        sql.append("metric_template mt, ");
        sql.append("product p, ");
        sql.append("place a, ");
        sql.append("place store, ");
        sql.append("evidence e, ");
        sql.append("rel_observed_metric_evidence rel ");
        sql.append("where  ");
        sql.append("os.id = pe.observed_situation_id ");
        sql.append("and s.id = os.situation_id ");
        sql.append("and st.id = s.situation_template_id ");
        sql.append("and om.observed_situation_id = os.id ");
        sql.append("and m.id = om.metric_id ");
        sql.append("and mt.id = m.metric_template_id ");
        sql.append("and a.id = m.area_id ");
        sql.append("and store.id = m.store_id ");
        sql.append("and p.id = st.product_id ");
        sql.append("and rel.observed_metric_id = om.id ");
        sql.append("and e.id = rel.evidence_id ");
        if (filter != null) {
            if (filter.getDate() != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                sql.append(" AND os.observed_situation_date = '").append(sdf.format(filter.getDate()))
                        .append("'");
            }
            if (filter.getArea() != null && filter.getArea() > 0) {
                sql.append(" AND a.id = ").append(filter.getArea());
            }
            if (filter.getQueue() != null && filter.getQueue().length() > 0) {
                sql.append(" AND pe.evaluation_queue = '").append(EvaluationQueue.valueOf(filter.getQueue()))
                        .append("'");
            }
            if (filter.getStore() != null && filter.getStore() > 0) {
                sql.append(" AND a.store_id = ").append(filter.getStore());
            }
            if (filter.getStatus() != null && filter.getStatus().length() > 0) {
                sql.append(" AND pe.evaluation_state = '").append(EvaluationState.valueOf(filter.getStatus()))
                        .append("'");
            }
            if (filter.getQueueNameId() != null
                    && (filter.getQueueNameId() > 0 || filter.getQueueNameId() == -1)) {
                sql.append(" AND pe.operator_queue_id = ").append(filter.getQueueNameId());
            } else {
                sql.append(" AND pe.operator_queue_id is null");
            }
        }
        sql.append(" ORDER BY pe.priority, pe.id, m.metric_order ASC");
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        int id = 0;
        PendingEvaluationDTO dto = null;
        for (Map m : list) {
            if (id != (Integer) m.get("pendingevaluationid")) {
                id = (Integer) m.get("pendingevaluationid");
                dto = new PendingEvaluationDTO();
                dto.setPendingEvaluationId(id);
                dto.setSituationTemplate((String) m.get("situationtemplate"));
                dto.setUserName((String) m.get("username"));
                dto.setSituationTemplateId((Integer) m.get("situationtemplateid"));
                dto.setArea((String) m.get("area"));
                dto.setAreaId((Integer) m.get("areaid"));
                dto.setDescription((String) m.get("description"));
                dto.setType((String) m.get("type"));
                dto.setDate((Date) m.get("date"));
                dto.setObservedSituationId((Integer) m.get("observedsituationid"));
                dto.setState(EvaluationState.valueOf((String) m.get("state")));
                dto.setQueue(EvaluationQueue.valueOf((String) m.get("queue")));
                dto.setPriority((Integer) m.get("priority"));
                //dto.setEvidenceId(rs.getInt("evidenceId"));
                dto.setEvidenceDate((Date) m.get("evidencedate"));
                dto.setProduct((String) m.get("product"));
                dto.setStore((String) m.get("store_description"));
                pendingEvaluationDTOs.add(dto);
            } else {
                dto.setDescription(dto.getDescription() + "<br>" + (String) m.get("description"));
                dto.setType(dto.getType() + "<br>" + (String) m.get("type"));
            }
        }

    } catch (Exception e) {
        log.error("Error " + e, e);
        //"periscopeexception.list.error", new String[]{
        throw new ScopixException("tab.queueManagement.pending");
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.info("end, result = " + pendingEvaluationDTOs);
    return pendingEvaluationDTOs;
}