Example usage for org.hibernate Query setDate

List of usage examples for org.hibernate Query setDate

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

Introduction

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

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(String name, Date val)

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Find teemsheets for an employee by project
 * //w  w  w.  j  a va 2s  .  c  o m
 * @param initWeek
 * @param endWeek
 * @param employee
 * @return
 */
@SuppressWarnings("unchecked")
public List<TimesheetWrap> findTimesheetsForEmployeeByProject(Date initWeek, Date endWeek, Employee employee,
        Project project) {

    String q = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.TimesheetWrap( " + "ts, " + "p.status,"
            + "case when pa is null then 0 else pa.idActivity end, "
            + "case when pa is null then '' else pa.activityName end, "
            + "case when o is null then '' else o.operationName end, "
            + "case when p is null then 0 else p.idProject end, "
            + "case when p is null then '' else p.projectName end, "
            + "case when p is null then '' else p.chartLabel end, "
            + "case when p is null then '' else p.status end, "
            + "case when pm is null then 0 else pm.idEmployee end, "
            + "case when c is null then '' else c.fullName end) " + "FROM Timesheet ts "
            + "LEFT JOIN ts.employee e " + "LEFT JOIN ts.projectactivity pa " + "LEFT JOIN pa.project p "
            + "LEFT JOIN ts.operation o " + "LEFT JOIN p.employeeByProjectManager pm "
            + "LEFT JOIN pm.contact c " + "WHERE ts.employee = :employee " + "AND ts.initDate = :initWeek "
            + "AND ts.endDate = :endWeek " + "AND (p = :project OR ts.operation is not null ) "
            + "AND ((p.status = :statusClosed OR p.status = :statusArchived) " + "AND ts.status = :appLevel "
            + "OR (p.status != :statusClosed AND p.status != :statusArchived)"
            + "OR ts.operation is not null) ";

    Query query = getSession().createQuery(q);

    query.setDate("initWeek", initWeek);
    query.setDate("endWeek", endWeek);
    query.setEntity("employee", employee);
    query.setString("statusClosed", Constants.STATUS_CLOSED);
    query.setString("statusArchived", Constants.STATUS_ARCHIVED);
    query.setString("appLevel", Constants.TIMESTATUS_APP3);
    query.setEntity("project", project);

    return query.list();
}

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Find imputations hours APP3//from  ww w . j a  v  a 2 s.  com
 *
 * @param employee
 * @param initDate
 * @param endDate
 * @return
 */
public List<ImputationWrap> findImputations(Employee employee, Date initDate, Date endDate) {

    List<ImputationWrap> imputations = new ArrayList<ImputationWrap>();

    // Query project activities
    //
    String qAct = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.ImputationWrap( " + "p.projectName, "
            + "p.chartLabel, " + "coalesce(SUM("
            + "(case when (ts.hoursDay1 is not null and TO_DAYS(ts.initDate) >= TO_DAYS(:since) and TO_DAYS(ts.initDate) <= TO_DAYS(:until)) THEN ts.hoursDay1 ELSE 0 end ) + "
            + "(case when (ts.hoursDay2 is not null and TO_DAYS(ts.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 1 <= TO_DAYS(:until)) THEN ts.hoursDay2 ELSE 0 end ) + "
            + "(case when (ts.hoursDay3 is not null and TO_DAYS(ts.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 2 <= TO_DAYS(:until)) THEN ts.hoursDay3 ELSE 0 end ) + "
            + "(case when (ts.hoursDay4 is not null and TO_DAYS(ts.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 3 <= TO_DAYS(:until)) THEN ts.hoursDay4 ELSE 0 end ) + "
            + "(case when (ts.hoursDay5 is not null and TO_DAYS(ts.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 4 <= TO_DAYS(:until)) THEN ts.hoursDay5 ELSE 0 end ) + "
            + "(case when (ts.hoursDay6 is not null and TO_DAYS(ts.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 5 <= TO_DAYS(:until)) THEN ts.hoursDay6 ELSE 0 end ) + "
            + "(case when (ts.hoursDay7 is not null and TO_DAYS(ts.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 6 <= TO_DAYS(:until)) THEN ts.hoursDay7 ELSE 0 end )"
            + "), 0D) " + ") " + "FROM Timesheet ts " + "JOIN ts.employee e " + "JOIN ts.projectactivity pa "
            + "JOIN pa.project p " + "WHERE e.idEmployee = :idEmployee " + "AND ts.initDate >= :initDate "
            + "AND ts.endDate <= :endDate " + "AND ts.status = :status "
            + "GROUP BY p.projectName, p.chartLabel " + "ORDER BY p.projectName";

    Query query = getSession().createQuery(qAct);

    query.setString("status", Constants.TIMESTATUS_APP3);
    query.setInteger("idEmployee", employee.getIdEmployee());
    query.setDate("initDate", DateUtil.getFirstWeekDay(initDate));
    query.setDate("endDate", DateUtil.getLastWeekDay(endDate));
    query.setDate("since", initDate);
    query.setDate("until", endDate);

    List<ImputationWrap> imputationsActivity = query.list();

    // Add elements
    if (ValidateUtil.isNotNull(imputationsActivity)) {

        for (ImputationWrap imputationActivity : imputationsActivity) {

            if (imputationActivity.getHoursAPP3() != null && imputationActivity.getHoursAPP3() != 0.0) {
                imputations.add(imputationActivity);
            }
        }
    }

    // Query operations
    //
    String qOp = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.ImputationWrap( " + "op.operationName, "
            + "coalesce(SUM("
            + "(case when (ts.hoursDay1 is not null and TO_DAYS(ts.initDate) >= TO_DAYS(:since) and TO_DAYS(ts.initDate) <= TO_DAYS(:until)) THEN ts.hoursDay1 ELSE 0 end ) + "
            + "(case when (ts.hoursDay2 is not null and TO_DAYS(ts.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 1 <= TO_DAYS(:until)) THEN ts.hoursDay2 ELSE 0 end ) + "
            + "(case when (ts.hoursDay3 is not null and TO_DAYS(ts.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 2 <= TO_DAYS(:until)) THEN ts.hoursDay3 ELSE 0 end ) + "
            + "(case when (ts.hoursDay4 is not null and TO_DAYS(ts.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 3 <= TO_DAYS(:until)) THEN ts.hoursDay4 ELSE 0 end ) + "
            + "(case when (ts.hoursDay5 is not null and TO_DAYS(ts.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 4 <= TO_DAYS(:until)) THEN ts.hoursDay5 ELSE 0 end ) + "
            + "(case when (ts.hoursDay6 is not null and TO_DAYS(ts.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 5 <= TO_DAYS(:until)) THEN ts.hoursDay6 ELSE 0 end ) + "
            + "(case when (ts.hoursDay7 is not null and TO_DAYS(ts.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 6 <= TO_DAYS(:until)) THEN ts.hoursDay7 ELSE 0 end )"
            + "), 0D) " + ") " + "FROM Timesheet ts " + "JOIN ts.employee e " + "JOIN ts.operation op "
            + "WHERE e.idEmployee = :idEmployee " + "AND ts.initDate >= :initDate "
            + "AND ts.endDate <= :endDate " + "AND ts.status = :status " + "GROUP BY op.operationName "
            + "ORDER BY op.operationName";

    query = getSession().createQuery(qOp);

    query.setString("status", Constants.TIMESTATUS_APP3);
    query.setInteger("idEmployee", employee.getIdEmployee());
    query.setDate("initDate", DateUtil.getFirstWeekDay(initDate));
    query.setDate("endDate", DateUtil.getLastWeekDay(endDate));
    query.setDate("since", initDate);
    query.setDate("until", endDate);

    List<ImputationWrap> imputationsOperation = query.list();

    // Add elements
    if (ValidateUtil.isNotNull(imputationsOperation)) {

        for (ImputationWrap imputationOperation : imputationsOperation) {

            if (imputationOperation.getHoursAPP3() != null && imputationOperation.getHoursAPP3() != 0.0) {
                imputations.add(imputationOperation);
            }
        }
    }

    return imputations;
}

From source file:fr.gael.dhus.database.dao.ActionRecordWritterDao.java

License:Open Source License

public void cleanupOlderActionRecords(final int keep_period) {
    if (inactive) {
        logger.warn("Action record access has been deactivated by user via "
                + "\"action.record.inactive\" parameter: the action record tables " + "will not be purged.");
        return;//from w  ww .  ja  v  a 2s.c o m
    }

    getHibernateTemplate().execute(new HibernateCallback<Void>() {
        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            long days = keep_period * 24 * 60 * 60 * 1000L;
            Date date = new Date(System.currentTimeMillis() - days);
            String pattern = "DELETE FROM <table> WHERE created < ?";

            String hql = pattern.replace("<table>", "ActionRecordDownload");
            Query query = session.createQuery(hql);
            query.setDate(0, date);
            query.executeUpdate();

            hql = pattern.replace("<table>", "ActionRecordLogon");
            query = session.createQuery(hql);
            query.setDate(0, date);
            query.executeUpdate();

            hql = pattern.replace("<table>", "ActionRecordSearch");
            query = session.createQuery(hql);
            query.setDate(0, date);
            query.executeUpdate();

            hql = "FROM ActionRecordUpload WHERE created < ?";
            query = session.createQuery(hql);
            query.setDate(0, date);

            @SuppressWarnings("unchecked")
            List<ActionRecordUpload> uploads = query.list();
            for (ActionRecordUpload upload : uploads) {
                session.evict(upload);
                session.delete(upload);
            }
            return null;
        }
    });
}

From source file:fr.hoteia.qalingo.core.dao.impl.RuleRepositoryDaoImpl.java

License:Apache License

public List<RuleRepository> findActiveRuleRepositories() {
    Session session = (Session) em.getDelegate();
    String sql = "FROM RuleRepository WHERE active = 1 AND startDate <= :currentDate AND endDate >= :currentDate";
    Query query = session.createQuery(sql);
    query.setDate("currentDate", new Date());
    List<RuleRepository> ruleRepositories = (List<RuleRepository>) query.list();
    return ruleRepositories;
}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1056] Gerar Relatrio de Acompanhamento dos Registros de Atendimento
 * /*from  w  ww.  j  av a2  s. c o m*/
 * @author Hugo Leonardo, Diogo Peixoto
 * @date 28/09/2010, 26/04/2011
 * 
 * @param FiltrarAcompanhamentoRegistroAtendimentoHelper
 * @return Collection
 * @throws ErroRepositorioException
 */
public Collection pesquisarRelatorioAcompanhamentoRAAnalitico(
        FiltrarAcompanhamentoRegistroAtendimentoHelper helper) throws ErroRepositorioException {

    Collection retorno = null;
    String consulta = "";
    Query query = null;
    Session session = HibernateUtil.getSession();
    String groupByMunicipio = "";
    String orderBy = " rau.unidadeOrganizacional ";

    if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
        groupByMunicipio = " GROUP BY munRA.nome, munRA.id,  rau.unidadeOrganizacional.id, step.descricao, ra.registroAtendimento, "
                + " ra.dataEncerramento, ra.dataEncerramento, ame.descricao, rau.unidadeOrganizacional.descricao, ame.id, ra.id ";
        orderBy = " munRA.nome ";
    }

    try {
        consulta += this.montarSelectRelatorioAcompanhamentoAnalitico(helper)
                + this.montarFromRelatorioAcompanhamentoAnalitico(helper)
                + " where ra.unidadeAtual = uni.id and ";

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {
            consulta += " rau.unidadeOrganizacional = :unidade  and " + " rau.atendimentoRelacaoTipo = 1 and ";
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            consulta += " (munRA.id IN (:municipios) or munImo.id IN (:municipios)) and ";
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {

            consulta += " ame.id in (:motivo) and ";
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            consulta += " (ra.registroAtendimento between :dtAtendimentoIncial and :dtAtendimentoFinal) and ";
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {
            consulta += " (ra.dataEncerramento between :dtEncerramentoIncial and :dtEncerramentoFinal) and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA())) {
            consulta += " ra.codigoSituacao = :situacao and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("1")) {

            consulta += " (ra.dataPrevistaAtual >= :dtCorrente "
                    + " or ra.dataPrevistaOriginal >= :dtCorrente) and ";

        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("0")) {

            consulta += " (ra.dataPrevistaAtual < :dtCorrente "
                    + " or ra.dataPrevistaOriginal < :dtCorrente) and ";
        }

        // remove o ltimo AND
        consulta = Util.removerUltimosCaracteres(consulta, 4);
        consulta += groupByMunicipio;
        consulta += " ORDER BY " + orderBy;

        query = (Query) session.createQuery(consulta);

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {

            query.setString("unidade", helper.getIdUnidadeAtendimento().toString());
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            query.setParameterList("municipios", helper.getMunicipiosAssociados());
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {

            query.setParameterList("motivo", helper.getIdsMotivoEncerramentoSelecionados());
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            query.setDate("dtAtendimentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoAtendimentoInicial()));
            query.setDate("dtAtendimentoFinal", Util.formatarDataFinal(helper.getPeriodoAtendimentoFinal()));
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            query.setDate("dtEncerramentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoEncerramentoInicial()));
            query.setDate("dtEncerramentoFinal", Util.formatarDataFinal(helper.getPeriodoEncerramentoFinal()));
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && (helper.getSituacaoRAAbertos().equals("0") || helper.getSituacaoRAAbertos().equals("1"))) {

            query.setDate("dtCorrente", new Date());
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_PENDENTE);
        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("1")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_ENCERRADO);
        }

        retorno = query.list();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }

    return retorno;
}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1056] Gerar Relatrio de Acompanhamento dos Registros de Atendimento
 * //from w  ww  .ja  v  a2  s. c o m
 * @author Hugo Leonardo, Diogo Peixoto
 * @date 30/09/2010, 26/04/2011
 * 
 * @param FiltrarAcompanhamentoRegistroAtendimentoHelper
 * @return Integer
 * @throws ErroRepositorioException
 */
public Integer countPesquisarRelatorioAcompanhamentoRAAnalitico(
        FiltrarAcompanhamentoRegistroAtendimentoHelper helper) throws ErroRepositorioException {

    Integer retorno = 0;
    String consulta = "";
    Query query = null;
    Session session = HibernateUtil.getSession();

    try {
        consulta += " select count(distinct ra.id) " //0 
                + " from gcom.atendimentopublico.registroatendimento.RegistroAtendimento ra "
                + " inner join ra.solicitacaoTipoEspecificacao step "
                + " left join ra.atendimentoMotivoEncerramento ame ";

        consulta += " inner join ra.registroAtendimentoUnidades rau "
                + " inner join rau.unidadeOrganizacional uni " + " left join ra.localidade locRA "
                + " left join locRA.municipio munRA " + " left join ra.imovel imov "
                + " left join imov.localidade locImo " + " left join locImo.municipio munImo "

                + " where ra.unidadeAtual = uni.id and ";

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {

            consulta += " rau.unidadeOrganizacional = :unidade  and " + " rau.atendimentoRelacaoTipo = 1 and ";
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            consulta += " (munRA.id IN (:municipios) or munImo.id IN (:municipios)) and ";
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {

            consulta += " ame.id in (:motivo) and ";
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            consulta += " (ra.registroAtendimento between :dtAtendimentoIncial and :dtAtendimentoFinal) and ";
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            consulta += " (ra.dataEncerramento between :dtEncerramentoIncial and :dtEncerramentoFinal) and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA())) {

            consulta += " ra.codigoSituacao = :situacao and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("1")) {

            consulta += " (ra.dataPrevistaAtual >= :dtCorrente "
                    + " or ra.dataPrevistaOriginal >= :dtCorrente) and ";
        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("0")) {

            consulta += " (ra.dataPrevistaAtual < :dtCorrente "
                    + " or ra.dataPrevistaOriginal < :dtCorrente) and ";
        }

        // remove o ltimo AND
        consulta = Util.removerUltimosCaracteres(consulta, 4);

        query = (Query) session.createQuery(consulta);

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {

            query.setString("unidade", helper.getIdUnidadeAtendimento().toString());
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            query.setParameterList("municipios", helper.getMunicipiosAssociados());
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {

            query.setParameterList("motivo", helper.getIdsMotivoEncerramentoSelecionados());
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            query.setDate("dtAtendimentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoAtendimentoInicial()));
            query.setDate("dtAtendimentoFinal", Util.formatarDataFinal(helper.getPeriodoAtendimentoFinal()));
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            query.setDate("dtEncerramentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoEncerramentoInicial()));
            query.setDate("dtEncerramentoFinal", Util.formatarDataFinal(helper.getPeriodoEncerramentoFinal()));
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && (helper.getSituacaoRAAbertos().equals("0") || helper.getSituacaoRAAbertos().equals("1"))) {

            query.setDate("dtCorrente", new Date());
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_PENDENTE);

        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("1")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_ENCERRADO);
        }

        retorno = (Integer) query.setMaxResults(1).uniqueResult();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }

    return retorno;
}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1056] Gerar Relatrio de Acompanhamento dos Registros de Atendimento
 * //from   w  ww  .ja  v a2s  . c  o m
 * @author Hugo Leonardo, Diogo Peixoto
 * @date 01/10/2010, 28/04/2011
 * 
 * @param FiltrarAcompanhamentoRegistroAtendimentoHelper
 * @return Collection
 * @throws ErroRepositorioException
 */
public Collection pesquisarRelatorioAcompanhamentoRASinteticoAberto(
        FiltrarAcompanhamentoRegistroAtendimentoHelper helper) throws ErroRepositorioException {

    Collection retorno = null;
    String consulta = "";
    Query query = null;
    Session session = HibernateUtil.getSession();
    String joinMunicipio = "";

    if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())
            && (helper.getIdUnidadeAtendimento() == null || helper.getIdUnidadeAtendimento().equals(""))) {
        consulta += " select rau.unidadeOrganizacional.descricao, " + " muni.nome, " + " count(distinct ra.id) "
                + " from gcom.atendimentopublico.registroatendimento.RegistroAtendimento ra "
                + " inner join ra.solicitacaoTipoEspecificacao step "
                + " left join ra.atendimentoMotivoEncerramento ame ";

        consulta += " inner join ra.localidade loc " + " inner join loc.municipio muni ";
    } else {

        consulta += " select rau.unidadeOrganizacional.descricao, ";
        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            consulta += " muni.nome, ";
            joinMunicipio = " inner join ra.localidade loc " + " inner join loc.municipio muni ";
        }
        consulta += " count(distinct ra.id) "
                + " from gcom.atendimentopublico.registroatendimento.RegistroAtendimento ra "
                + " inner join ra.solicitacaoTipoEspecificacao step "
                + " left join ra.atendimentoMotivoEncerramento ame ";
    }
    consulta += joinMunicipio + " inner join ra.registroAtendimentoUnidades rau "
            + " inner join rau.unidadeOrganizacional uni " + " where ra.unidadeAtual = uni.id and ";

    try {
        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {

            consulta += " rau.unidadeOrganizacional = :unidade  and " + " rau.atendimentoRelacaoTipo = 1 and ";
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {

            consulta += " ame.id in (:motivo) and ";
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {

            consulta += " muni.id in (:municipios) and ";
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            consulta += " (ra.registroAtendimento between :dtAtendimentoIncial and :dtAtendimentoFinal) and ";
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            consulta += " (ra.dataEncerramento between :dtEncerramentoIncial and :dtEncerramentoFinal) and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA())) {

            consulta += " ra.codigoSituacao = :situacao and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("1")) {

            consulta += " (ra.dataPrevistaAtual >= :dtCorrente "
                    + " or ra.dataPrevistaOriginal >= :dtCorrente) and ";
        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("0")) {

            consulta += " (ra.dataPrevistaAtual < :dtCorrente "
                    + " or ra.dataPrevistaOriginal < :dtCorrente) and ";
        }

        // remove o ltimo AND
        consulta = Util.removerUltimosCaracteres(consulta, 4);

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())
                && (helper.getIdUnidadeAtendimento() == null || helper.getIdUnidadeAtendimento().equals(""))) {

            consulta += " GROUP BY muni.nome, rau.unidadeOrganizacional.descricao "
                    + " ORDER BY muni.nome, rau.unidadeOrganizacional.descricao  ";
        } else {
            String groupBy = " GROUP BY ";
            String orderBy = " ORDER BY ";
            if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
                groupBy += " muni.nome, ";
                orderBy += " muni.nome, ";
            }
            groupBy += " rau.unidadeOrganizacional.descricao ";
            orderBy += " rau.unidadeOrganizacional.descricao ";
            consulta += groupBy + orderBy;
        }

        query = (Query) session.createQuery(consulta);

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {

            query.setString("unidade", helper.getIdUnidadeAtendimento().toString());
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {

            query.setParameterList("motivo", helper.getIdsMotivoEncerramentoSelecionados());
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {

            query.setParameterList("municipios", helper.getMunicipiosAssociados());
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            query.setDate("dtAtendimentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoAtendimentoInicial()));
            query.setDate("dtAtendimentoFinal", Util.formatarDataFinal(helper.getPeriodoAtendimentoFinal()));
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            query.setDate("dtEncerramentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoEncerramentoInicial()));
            query.setDate("dtEncerramentoFinal", Util.formatarDataFinal(helper.getPeriodoEncerramentoFinal()));
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && (helper.getSituacaoRAAbertos().equals("0") || helper.getSituacaoRAAbertos().equals("1"))) {

            query.setDate("dtCorrente", new Date());
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_PENDENTE);
        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("1")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_ENCERRADO);
        }

        retorno = query.list();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }

    return retorno;
}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1056] Gerar Relatrio de Acompanhamento dos Registros de Atendimento
 * // ww w.j a  v  a  2 s.  co  m
 * @author Hugo Leonardo, Diogo Peixoto
 * @date 01/10/2010, 28/04/2011
 * 
 * @param FiltrarAcompanhamentoRegistroAtendimentoHelper
 * @return Collection
 * @throws ErroRepositorioException
 */
public Collection pesquisarRelatorioAcompanhamentoRASinteticoEncerrado(
        FiltrarAcompanhamentoRegistroAtendimentoHelper helper) throws ErroRepositorioException {

    Collection retorno = null;
    String consulta = "";
    Query query = null;
    Session session = HibernateUtil.getSession();

    String selectMunicipio = "";
    String joinMunicipio = "";
    if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
        selectMunicipio = ", muni.nome";
        joinMunicipio = " inner join ra.localidade loc inner join loc.municipio muni ";
    }

    try {
        consulta += " select rau.unidadeOrganizacional.descricao, ame.descricao, " + " count(distinct ra.id) "
                + selectMunicipio + " from gcom.atendimentopublico.registroatendimento.RegistroAtendimento ra "
                + " inner join ra.solicitacaoTipoEspecificacao step "
                + " left join ra.atendimentoMotivoEncerramento ame ";

        consulta += " inner join ra.registroAtendimentoUnidades rau "
                + " inner join rau.unidadeOrganizacional uni " + joinMunicipio
                + " where ra.unidadeAtual = uni.id and ";

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {

            consulta += " rau.unidadeOrganizacional = :unidade  and " + " rau.atendimentoRelacaoTipo = 1 and ";
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {
            consulta += " ame.id in (:motivo) and ";
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            consulta += " muni.id in (:municipios) and ";
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            consulta += " (ra.registroAtendimento between :dtAtendimentoIncial and :dtAtendimentoFinal) and ";
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            consulta += " (ra.dataEncerramento between :dtEncerramentoIncial and :dtEncerramentoFinal) and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA())) {

            consulta += " ra.codigoSituacao = :situacao and ";
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("1")) {

            consulta += " (ra.dataPrevistaAtual >= :dtCorrente "
                    + " or ra.dataPrevistaOriginal >= :dtCorrente) and ";
        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && helper.getSituacaoRAAbertos().equals("0")) {

            consulta += " (ra.dataPrevistaAtual < :dtCorrente "
                    + " or ra.dataPrevistaOriginal < :dtCorrente) and ";
        }

        // remove o ltimo AND
        consulta = Util.removerUltimosCaracteres(consulta, 4);

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())
                && (helper.getIdUnidadeAtendimento() == null || helper.getIdUnidadeAtendimento().equals(""))) {

            consulta += " GROUP BY muni.nome, rau.unidadeOrganizacional.descricao, ame.descricao "
                    + " ORDER BY muni.nome, rau.unidadeOrganizacional.descricao, ame.descricao ";
        } else {
            String groupBy = " GROUP BY ";
            String orderBy = " ORDER BY ";
            if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
                groupBy += " muni.nome, ";
                orderBy += " muni.nome, ";
            }
            groupBy += " rau.unidadeOrganizacional.descricao, ame.descricao ";
            orderBy += " rau.unidadeOrganizacional.descricao, ame.descricao ";
            consulta += groupBy + orderBy;
        }

        query = (Query) session.createQuery(consulta);

        if (Util.verificarNaoVazio(helper.getIdUnidadeAtendimento())) {
            query.setString("unidade", helper.getIdUnidadeAtendimento().toString());
        }

        if (!Util.isVazioOrNulo(helper.getIdsMotivoEncerramentoSelecionados())) {
            query.setParameterList("motivo", helper.getIdsMotivoEncerramentoSelecionados());
        }

        if (!Util.isVazioOrNulo(helper.getMunicipiosAssociados())) {
            query.setParameterList("municipios", helper.getMunicipiosAssociados());
        }

        if (helper.getPeriodoAtendimentoInicial() != null && helper.getPeriodoAtendimentoFinal() != null) {

            query.setDate("dtAtendimentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoAtendimentoInicial()));
            query.setDate("dtAtendimentoFinal", Util.formatarDataFinal(helper.getPeriodoAtendimentoFinal()));
        }

        if (helper.getPeriodoEncerramentoInicial() != null && helper.getPeriodoEncerramentoFinal() != null) {

            query.setDate("dtEncerramentoIncial",
                    Util.formatarDataInicial(helper.getPeriodoEncerramentoInicial()));
            query.setDate("dtEncerramentoFinal", Util.formatarDataFinal(helper.getPeriodoEncerramentoFinal()));
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")
                && Util.verificarNaoVazio(helper.getSituacaoRAAbertos())
                && (helper.getSituacaoRAAbertos().equals("0") || helper.getSituacaoRAAbertos().equals("1"))) {

            query.setDate("dtCorrente", new Date());
        }

        if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("0")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_PENDENTE);
        } else if (Util.verificarNaoVazio(helper.getSituacaoRA()) && helper.getSituacaoRA().equals("1")) {

            query.setShort("situacao", RegistroAtendimento.SITUACAO_ENCERRADO);
        }

        retorno = query.list();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }

    return retorno;
}

From source file:gov.nih.nci.firebird.service.annual.registration.AnnualRegistrationServiceBean.java

License:Open Source License

@SuppressWarnings("unchecked")
// Hibernate list() method is untyped
private List<AnnualRegistration> getRegistrationsRequiringRenewal() {
    Date renewalDate = DateUtils.addDays(new Date(), daysBeforeDueDateToSendFirstNotification);
    Query query = getSession().createQuery(REGISTRATIONS_REQUIRING_RENEWAL_HQL);
    query.setDate("renewalDate", renewalDate);
    return query.list();
}

From source file:gov.nih.nci.firebird.service.annual.registration.AnnualRegistrationServiceBean.java

License:Open Source License

@SuppressWarnings("unchecked")
// Hibernate list() method is untyped
private List<AnnualRegistration> getUnSubmittedWithoutReminderSentRegistrationsWithinSecondNotificationWindow() {
    Query query = getSession()
            .createQuery(UNSUBMITTED_WITHOUT_REMINDER_SENT_REGISTRATIONS_WITH_DUE_DATES_BEFORE_DATE_HQL);
    Date date = DateUtils.addDays(new Date(), daysBeforeDueDateToSendSecondNotification);
    query.setDate("date", date);
    return query.list();
}