Ejemplo n.º 1
0
  @SuppressWarnings("unchecked")
  public List<ConsultaTO> realizarConsultaOrdemServico(final Consulta consulta) throws Exception {
    try {
      DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

      final StringBuilder stringSql =
          new StringBuilder(
              "select "
                  + "os.numero_ordem_servico as nOS, "
                  + "os.numeroordemservicopai, "
                  + "os.unidade_pai_id, "
                  + "os.garantia, "
                  + "os.lpu_id, "
                  + "p.tipo, "
                  + "p.numero, "
                  + "os.status, "
                  + "rep.condicao as repCondicao, "
                  + "rep.dt_fim as repDtFim, "
                  + "orc.condicao as orcCondicao, "
                  + "orc.dt_fim as orcDtFim, "
                  + "os.nomecliente, "
                  + "os.nomeunidade, "
                  + "os.numero_serie_fabricante as nsFabricante, "
                  + "os.numero_serie_cliente as nsCliente, "
                  + "notafs.numero as nNF, "
                  + "notaf.numero as nNFS, "
                  + "os.nomelaboratorio, "
                  + "(select isAprovado from itemproposta itp where ordem_servico_id = os.id and proposta_id = p.id AND dataAprovacao IS NOT NULL) as isAprovado, "
                  + "(select dataaprovacao from itemproposta itp where ordem_servico_id = os.id and proposta_id = p.id AND dataAprovacao IS NOT NULL) as dataAprovacao, "
                  + "os.caseavaya, "
                  + "os.clienteavaya, "
                  + "(select statusString from itemestoqueavaya where os_original_id = os.id and os_original_id is not null) as statusEstoque, "
                  + "(select posicao from itemestoqueavaya where os_original_id = os.id and os_original_id is not null) as estoquePosicao, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_nota_fiscal_id = os.nota_fiscal_id and escopo = 3 group by h.ref_nota_fiscal_id) as obsNotaFiscal, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_faturamento_id = os.faturamento_id and escopo = 3 group by h.ref_faturamento_id) as obsFaturamento, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_nf_saida_id = os.nota_fiscal_saida_id and escopo = 3 group by h.ref_nf_saida_id) as obsNotaFiscalSaida, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_orcamento_id = os.orcamento_id and escopo = 3 group by h.ref_orcamento_id) as obsOrcamento, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_ordem_servico_id = os.id and escopo = 3 group by h.ref_ordem_servico_id) as obsOrdemServico, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_reparo_id = os.reparo_id and escopo = 3 group by h.ref_reparo_id) as obsReparo, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_proposta_id = os.proposta_id and escopo = 3 group by h.ref_proposta_id) as obsProposta, "
                  + "notaf.data_chegada, "
                  + "orc.id as idOrc, "
                  + "rep.id as idRep, "
                  + "os.id as idOs, "
                  + "notaf.data_nota_fiscal, "
                  + "notafs.dtEmissao, "
                  + "notaf.data_chegada, "
                  + "(SELECT concat('OBS: ', group_concat(h.texto separator ', OBS: ')) FROM observacao h where ref_ordem_servico_id = os.id and escopo = 3 and origem = 'Consulta' group by h.ref_ordem_servico_id) as obsConsulta "
                  + "from ordemservico os "
                  + "left join notafiscal notaf on os.nota_fiscal_id = notaf.id  "
                  + "left join notafiscalremessa notafs on os.nota_fiscal_saida_id = notafs.id  "
                  + "left join reparo rep on os.reparo_id = rep.id  "
                  + "left join orcamento orc on os.orcamento_id = orc.id  "
                  + "left join proposta p on os.proposta_id = p.id  ");

      stringSql.append(" WHERE os.data_abertura BETWEEN :dataDe AND :dataAte ");

      if (consulta.getNumeroOS() != null && !consulta.getNumeroOS().isEmpty()) {
        stringSql.append("AND os.numero_ordem_servico = :numeroOrdemServico ");
      }

      if (consulta.getNumeroNotaFiscal() != null && !consulta.getNumeroNotaFiscal().isEmpty()) {
        stringSql.append("AND notaf.numero = :numeroNotaFiscal ");
      }

      if (consulta.getNumeroNotaFiscalSaida() != null
          && !consulta.getNumeroNotaFiscalSaida().isEmpty()) {
        stringSql.append("AND notafs.numero = :numeroNotaFiscalSaida ");
      }

      if (consulta.getCliente() != null && consulta.getCliente().getId() != 0) {
        stringSql.append("AND os.cliente_id = :cliente ");
      }

      stringSql.append(" ORDER BY os.numero_ordem_servico + 0 ");

      Query q = sessionFactory.getCurrentSession().createSQLQuery(stringSql.toString());

      if (consulta.getDataDe() != null) {
        q.setParameter("dataDe", consulta.getDataDe());
      } else {
        q.setParameter("dataDe", new Date(0));
      }

      if (consulta.getDataAte() != null) {
        q.setParameter("dataAte", DateUtils.nextDay(consulta.getDataAte()));
      } else {
        q.setParameter("dataAte", DateUtils.nextDay(new Date()));
      }

      if (consulta.getNumeroNotaFiscal() != null && !consulta.getNumeroNotaFiscal().isEmpty()) {
        q.setParameter("numeroNotaFiscal", consulta.getNumeroNotaFiscal().trim());
      }

      if (consulta.getCliente() != null && consulta.getCliente().getId() != 0) {
        q.setParameter("cliente", consulta.getCliente());
      }

      if (consulta.getNumeroNotaFiscalSaida() != null
          && !consulta.getNumeroNotaFiscalSaida().isEmpty()) {
        q.setParameter("numeroNotaFiscalSaida", consulta.getNumeroNotaFiscalSaida().trim());
      }

      if (consulta.getNumeroOS() != null && !consulta.getNumeroOS().isEmpty()) {
        q.setParameter("numeroOrdemServico", consulta.getNumeroOS().trim());
      }

      SessionStatistics sessionStats = sessionFactory.getCurrentSession().getStatistics();
      Statistics stats = this.sessionFactory.getStatistics();
      System.out.println(stats);
      List<Object[]> lista = q.list();
      List<ConsultaTO> listaRetorno = converterConsultaTO(lista);
      return listaRetorno;
    } catch (Exception e) {
      e.printStackTrace();
      logger.error(e);
      return null;
    }
  }
Ejemplo n.º 2
0
  @SuppressWarnings("unchecked")
  public List<NotaFiscalRemessa> realizarConsultaGeral(final Consulta consulta) throws Exception {
    try {
      DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

      final StringBuilder queryString =
          new StringBuilder("SELECT a FROM " + entityClass.getName() + " a WHERE ");

      queryString.append(" a.dtCriacao BETWEEN :dataDe AND :dataAte ");

      if (consulta.getNumeroOS() != null && !consulta.getNumeroOS().isEmpty()) {
        queryString.append("AND a.numeroOrdemServico = :numeroOrdemServico ");
      }

      if (consulta.getNumeroNotaFiscal() != null && !consulta.getNumeroNotaFiscal().isEmpty()) {
        queryString.append("AND a.notaFiscal.numero = :numeroNotaFiscal ");
      }

      if (consulta.getNumeroNotaFiscalSaida() != null
          && !consulta.getNumeroNotaFiscalSaida().isEmpty()) {
        queryString.append("AND a.notaFiscalSaida.numero = :numeroNotaFiscalSaida ");
      }

      if (consulta.getCliente() != null && consulta.getCliente().getId() != 0) {
        queryString.append("AND a.cliente = :cliente ");
      }

      Query q = sessionFactory.getCurrentSession().createQuery(queryString.toString());

      if (consulta.getDataDe() != null) {
        q.setParameter("dataDe", consulta.getDataDe());
      } else {
        q.setParameter("dataDe", new Date(0));
      }

      if (consulta.getDataAte() != null) {
        q.setParameter("dataAte", consulta.getDataAte());
      } else {
        q.setParameter("dataAte", new Date());
      }

      if (consulta.getNumeroNotaFiscal() != null && !consulta.getNumeroNotaFiscal().isEmpty()) {
        q.setParameter("numeroNotaFiscal", consulta.getNumeroNotaFiscal().trim());
      }

      if (consulta.getCliente() != null && consulta.getCliente().getId() != 0) {
        q.setParameter("cliente", consulta.getCliente());
      }

      if (consulta.getNumeroNotaFiscalSaida() != null
          && !consulta.getNumeroNotaFiscalSaida().isEmpty()) {
        q.setParameter("numeroNotaFiscalSaida", consulta.getNumeroNotaFiscalSaida().trim());
      }

      if (consulta.getNumeroOS() != null && !consulta.getNumeroOS().isEmpty()) {
        q.setParameter("numeroOrdemServico", consulta.getNumeroOS().trim());
      }

      return q.list();
    } catch (Exception e) {
      return null;
    }
  }