Case Business Intelligence Query Dinamica
Query Dinamica BI(Business Intelligence) O BI permite nas variações SQL, HQL e Código que você crie queries dinamicas, que podem ser alteradas de acordo com os parâmetros informados. Isto significa que em um mesmo relatório é possível criar várias varições sem ser necessário o uso de código ou diversos if's.
Observe a seguinte query em HQL:
| Query |
|---|
| SELECT sum(cast(itPedido.quantidadeTotal as double)*cast(p.pesoUnitario as double)) AS F_PESO_TOTAL,
sum(itPedido.valorDesconto+itPedido.valorDescFinanceiro) as F_VALOR_DESCONTO, sum(cast(itPedido.quantidadeTotal as double)*cast(itPedido.valorUnitario as double) -(itPedido.valorDesconto+itPedido.valorDescFinanceiro)) as F_VALOR_LIQUIDO, sum(itPedido.quantidadeTotal) as F_QUANTIDADE_TOTAL, pessoa.identificador as F_IDENTIFICADOR, pessoa.nome as F_NOME, cid.descricao as F_DESCRICAO,uf.sigla as F_SIGLA,comp.cnpj as F_CNPJ, sum(cast(itPedido.quantidadeTotal as double)*cast(itPedido.valorUnitario as double)) as F_VALOR_BRUTO FROM Pedido pedido LEFT JOIN pedido.itemPedido itPedido LEFT JOIN itPedido.produto p LEFT JOIN pedido.unidadeFatCliente uniFat LEFT JOIN uniFat.cliente cli LEFT JOIN cli.pessoa pessoa LEFT JOIN pedido.empresa emp LEFT JOIN pessoa.endereco ende LEFT JOIN ende.cidade cid LEFT JOIN cid.uf uf LEFT JOIN pessoa.complemento comp @LEFT JOIN pessoa.grupoPessoas gruPessoa, :P_FILTRAR_PESSOA==2@ @LEFT JOIN emp.empresaDados empDados, :P_FILTRAR_EMPRESA==1@ @LEFT JOIN empDados.grupoEmpresa gruEmp, :P_FILTRAR_EMPRESA==1@ @LEFT JOIN emp.grupoEmpresasRel gruEmpRel, :P_FILTRAR_EMPRESA==2@ @LEFT JOIN gruEmp.grupoEmpresasRel gruEmpRel , :P_FILTRAR_EMPRESA==2@ WHERE ((:P_FILTRAR_PESSOA = 0) OR (:P_FILTRAR_PESSOA = 1 AND pessoa.identificador=:P_ID_PESSOA) @OR (:P_FILTRAR_PESSOA=2 AND gruPessoa.identificador=:P_ID_GRUPO_PESSOAS), :P_FILTRAR_PESSOA==2@) AND (:P_FILTRAR_DATA = 0 OR pedido.dataEmissao BETWEEN :P_DATA_INICIAL AND :P_DATA_FINAL) AND ((:P_FILTRAR_EMPRESA = 0) OR (:P_FILTRAR_EMPRESA = 1 AND emp.identificador=:P_ID_EMPRESA) @OR (:P_FILTRAR_EMPRESA = 1 AND gruEmp.identificador=:P_ID_GRUPO_EMPRESAS), :P_FILTRAR_EMPRESA==2@ @OR (:P_FILTRAR_EMPRESA = 2 AND gruEmpRel.identificador=:P_ID_GRUPO_EMPRESAS), :P_FILTRAR_EMPRESA==3@) |
Note as expressões dentro de dois @, @LEFT JOIN pessoa.grupoPessoas gruPessoa, :P_FILTRAR_PESSOA==2@ .
Isto significa que caso exista um parâmetro no BI P_FILTRAR_PESSOA, e este parametro possuir o valor 2, esta expressão será mantida, caso contrário retirada.
Basicamente, @EXPRESSAO_QUERY, CONDICAO@, onde temos uma expressão da query e após a virgula em qual condição a mesma será aplicada.
Observe que o parâmetro foi definido em dados adicionais no BI:
.
Caso esteja utilizando BI Código, você pode chamar o método prepareQuery(hql, getParams().getParameters().getParamsTodos()), passando os parametros para que o mesmo processe a query e retorne a mesma conforme os parâmetros. Este método já está definido em com.touchcomp.basementorservice.service.impl.buildbusinessintelligence.bicode.BaseBICode.
Supondo os parâmetros anteriores como :P_FILTRAR_PESSOA==2 e P_FILTRAR_EMPRESA==1, a query ficaria assim:
| Query Processada |
|---|
| SELECT sum(cast(itPedido.quantidadeTotal as double)*cast(p.pesoUnitario as double)) AS F_PESO_TOTAL,
sum(itPedido.valorDesconto+itPedido.valorDescFinanceiro) as F_VALOR_DESCONTO, sum(cast(itPedido.quantidadeTotal as double)*cast(itPedido.valorUnitario as double) -(itPedido.valorDesconto+itPedido.valorDescFinanceiro)) as F_VALOR_LIQUIDO, sum(itPedido.quantidadeTotal) as F_QUANTIDADE_TOTAL, pessoa.identificador as F_IDENTIFICADOR, pessoa.nome as F_NOME, cid.descricao as F_DESCRICAO,uf.sigla as F_SIGLA,comp.cnpj as F_CNPJ, sum(cast(itPedido.quantidadeTotal as double)*cast(itPedido.valorUnitario as double)) as F_VALOR_BRUTO FROM Pedido pedido LEFT JOIN pedido.itemPedido itPedido LEFT JOIN itPedido.produto p LEFT JOIN pedido.unidadeFatCliente uniFat LEFT JOIN uniFat.cliente cli LEFT JOIN cli.pessoa pessoa LEFT JOIN pedido.empresa emp LEFT JOIN pessoa.endereco ende LEFT JOIN ende.cidade cid LEFT JOIN cid.uf uf LEFT JOIN pessoa.complemento comp LEFT JOIN pessoa.grupoPessoas gruPessoa LEFT JOIN emp.empresaDados empDados LEFT JOIN empDados.grupoEmpresa gruEmp WHERE ((:P_FILTRAR_PESSOA = 0) OR (:P_FILTRAR_PESSOA = 1 AND pessoa.identificador=:P_ID_PESSOA) OR (:P_FILTRAR_PESSOA=2 AND gruPessoa.identificador=:P_ID_GRUPO_PESSOAS) AND (:P_FILTRAR_DATA = 0 OR pedido.dataEmissao BETWEEN :P_DATA_INICIAL AND :P_DATA_FINAL) AND ((:P_FILTRAR_EMPRESA = 0) OR (:P_FILTRAR_EMPRESA = 1 AND emp.identificador=:P_ID_EMPRESA) |