@SuppressWarnings("unchecked") public List<OrdemServico> listarOrdemFaturamentoAFaturar( final Pessoa cliente, final Date de, final Date ate) { StringBuilder sb = new StringBuilder( "SELECT h FROM " + entityClass.getName() + " h WHERE (h.dataFinalizacao IS NOT NULL) AND (h.cliente = :cliente) AND (h.unidadePai IS NULL) AND (h.notaFiscalSaida.dtEmissao BETWEEN :dataDe AND :dataAte) AND (h.faturamento IS NULL) ORDER BY h.numeroOrdemServico"); Query query = sessionFactory.getCurrentSession().createQuery(sb.toString()); query.setParameter("cliente", cliente); if (de != null) { query.setParameter("dataDe", de); } else { query.setParameter("dataDe", new Date(0)); } if (ate != null) { query.setParameter("dataAte", DateUtils.nextDay(ate)); } else { query.setParameter("dataAte", DateUtils.nextDay(new Date())); } return query.list(); }
@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; } }