Example usage for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE

Introduction

In this page you can find the example usage for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE.

Prototype

AliasToEntityMapResultTransformer INSTANCE

To view the source code for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE.

Click Source Link

Usage

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

License:Open Source License

@Override
public List<Map<String, Object>> getFailedPendingEvidences(Date startDate, Date endDate) {
    Session session = this.getSession();
    try {/*  w w  w . ja  v a 2s.  c om*/
        StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT DISTINCT ON (ert.id) ert.id as evidence_region_transfer_id, ert.region_server_name as region_server_name, pe.evaluation_state as pending_evaluation_state ");
        sql.append("FROM ");
        sql.append("evidence ev join evidence_region_transfer ert on ev.id = ert.evidence_id, ");
        sql.append(
                "observed_situation os left join pending_evaluation pe on  os.id = pe.observed_situation_id, ");
        sql.append("observed_metric om, ");
        sql.append("rel_observed_metric_evidence rel ");
        sql.append("WHERE rel.evidence_id = ev.id ");
        sql.append("AND rel.observed_metric_id = om.id ");
        sql.append("AND om.observed_situation_id = os.id ");
        sql.append("AND ert.completed = false ");
        List<Map<String, Object>> resultSql;
        if (startDate != null && endDate != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            sql.append("and  ert.transmisition_date >= '").append(sdf.format(startDate)).append("'");
            sql.append("and  ert.transmisition_date <= '").append(sdf.format(endDate)).append("'");
        }
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        resultSql = query.list();
        return resultSql;
    } finally {
        this.releaseSession(session);
    }
}

---

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

License:Open Source License

public List<ObservedMetric> getObservedMetricListSQL(ObservedMetric observedMetric) {
    List<ObservedMetric> observedMetrics = new ArrayList<ObservedMetric>();
    log.info("start");
    Session session = null;//w w  w.j  a v a 2  s  .co m
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("select om.id, om.evaluation_state, om.metric_id, om.observed_metric_date, ");
        sql.append("om.observed_situation_id from observed_metric om ");
        if (observedMetric != null) {
            if (observedMetric.getEvaluationState() != null) {
                sql.append(" WHERE om.evaluation_state = '");
                sql.append(observedMetric.getEvaluationState().name());
                sql.append("'");
            }
        }
        session = this.getSession();
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        if (list.size() > 0) {
            for (Map m : list) {
                ObservedMetric om = new ObservedMetric();
                om.setId((Integer) m.get("id"));
                om.setEvaluationState(EvaluationState.valueOf((String) m.get("evaluation_state")));
                Metric metric = new Metric();
                metric.setId((Integer) m.get("metric_id"));
                om.setMetric(metric);
                om.setObservedMetricDate((Date) m.get("observed_metric_date"));
                ObservedSituation observedSituation = new ObservedSituation();
                observedSituation.setId((Integer) m.get("observed_situation_id"));
                om.setObservedSituation(observedSituation);

                observedMetrics.add(om);
            }
        }

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

---

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

License:Open Source License

public List<EvidencesAndProofsDTO> getEvidenceAndProof(Integer observedMetricId) throws ScopixException {
    List<EvidencesAndProofsDTO> listResult = new ArrayList<EvidencesAndProofsDTO>();
    Session session = null;//from w  w  w. j  a  va  2  s.  co  m
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT distinct e.id AS evidence_id, p.id AS proof_id, ep.description, ep.id AS camera");
        sql.append(" FROM");
        sql.append(" evidence_evaluation ee,");
        sql.append(" rel_evidence_evaluation_evidence reee,");
        sql.append(" evidence e,");
        sql.append(" rel_evidence_request_evidence rel,");
        sql.append(" evidence_request er,");
        sql.append(" evidence_provider ep,");
        sql.append(" proof p");
        sql.append(" WHERE");
        sql.append(" ee.observed_metric_id = ");
        sql.append(observedMetricId);
        sql.append(" and reee.evidence_evaluation_id=ee.id");
        sql.append(" and reee.evidence_id = e.id");
        sql.append(" and p.evidence_evaluation_id = ee.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 ee.rejected = false");
        sql.append(" ORDER BY ep.id");

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

        if (list.size() > 0) {
            Integer evidenceId = null;
            EvidencesAndProofsDTO dto = null;
            List<Integer> proofs = null;
            for (Map m : list) {
                if (evidenceId == null || !evidenceId.equals((Integer) m.get("evidence_id"))) {
                    if (dto != null) {
                        dto.setProofsId(proofs);
                        listResult.add(dto);
                    }
                    evidenceId = (Integer) m.get("evidence_id");
                    dto = new EvidencesAndProofsDTO();
                    dto.setEvidenceId(evidenceId);
                    proofs = new ArrayList<Integer>();
                }
                proofs.add((Integer) m.get("proof_id"));
            }
            if (dto != null) {
                dto.setProofsId(proofs);
                listResult.add(dto);
            }
        } else {
            sql.append("SELECT distinct e.id AS evidence_id, -1 AS proof_id, ep.description, ep.id AS camera");
            sql.append(" FROM");
            sql.append(" evidence_evaluation ee,");
            sql.append(" rel_evidence_evaluation_evidence reee,");
            sql.append(" evidence e,");
            sql.append(" rel_evidence_request_evidence rel,");
            sql.append(" evidence_request er,");
            sql.append(" evidence_provider ep");
            sql.append(" WHERE");
            sql.append(" ee.observed_metric_id =");
            sql.append(observedMetricId);
            sql.append(" and reee.evidence_evaluation_id=ee.id");
            sql.append(" and reee.evidence_id = e.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 ee.rejected = false");
            sql.append(" ORDER BY ep.id");

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

            Integer evidenceId = null;
            EvidencesAndProofsDTO dto = null;
            List<Integer> proofs = null;
            for (Map m : list) {
                if (evidenceId == null || !evidenceId.equals((Integer) m.get("evidence_id"))) {
                    if (dto != null) {
                        dto.setProofsId(proofs);
                        listResult.add(dto);
                    }
                    evidenceId = (Integer) m.get("evidence_id");
                    dto = new EvidencesAndProofsDTO();
                    dto.setEvidenceId(evidenceId);
                    proofs = new ArrayList<Integer>();
                }
                proofs.add((Integer) m.get("proof_id"));
            }
            if (dto != null) {
                dto.setProofsId(proofs);
                listResult.add(dto);
            }

        }

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

---

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;/*from  www.  j av  a  2s.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  w w w  . java2 s  . 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.j  av 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  w  w.  ja v  a2  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  av a2 s.c om*/
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 ww. ja  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;/*w  w w  .ja  v a 2  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 ");
        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;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• »