@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;
}
}
@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;
}
}