quinta-feira, 6 de setembro de 2012

Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo

A idéia primordial desse artigo é demonstrar como desenvolver uma query sql usando um plano de contas ou centro de custo, o principio é o mesmo, subtotalizando de forma invertida, considerando uma estrutura de balancete ou centro de custo, mas sempre usando um plano de contas com contas analiticas e sintéticas e vários niveis de sub-contas.

O SGBDR testados foram:
 Firebird 2.5.1
 Postgres 9.1
 SQL Server 2012
 Oracle 11g R2

Expondo o problema, exemplo:

Montar uma query relatório que mostre o resultado totalizado por níveis de contas lançadas.
O centro de custo irá ser lançado sempre no ultimo nivel e/ou conta analitica, ou seja 1.1.1 ou 1.2.1, etc. 

Conforme figura Figura 01, abaixo:

Figura 01 - Relatório Sumarizado por Plano de Contas

Temos uma estrutura de centro de custo modelada da seguinte maneira: 

Script para Firebird 2.5.1, Postgres 9.1, SQL Server 2012, Oracle 11g R2



-- USE tempdb; -- Descomentar caso use SQL Server 
--DROP TABLE centro_custo;
CREATE TABLE centro_custo
( 
   id_centro_custo  varchar(12) PRIMARY KEY   -- dados da conta ex. 1.02.01 
 , descricao        varchar(50)               -- descricao da conta cadastrada ex. Vendas Externas
 , tipo_conta       varchar(1)                -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check
); 

--DROP TABLE movimento;
CREATE TABLE movimento 
(
   id_movimento   integer   PRIMARY KEY  -- id do movimento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento
 , numero_doc   varchar(40)              -- numero do documento a ser informado  
 , id_centro_custo   varchar(12)         -- chave estrangeira para a tabela centro de custo, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null
 , valor_movimento numeric(15,2)         -- valor informado 
);

Agora iremos povoar a tabela de centro de custo

-- Receitas 

INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');

-- Despesas

INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');

-- Vamos povoar a tabela movimento: 

INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (1,'0000021','1.1.2',50.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (2,'0000042','1.2.2',100.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (3,'0000084','1.2.2',160.00);

INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (4,'0000142','2.2.1',40.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (5,'0000210','2.2.2',80.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (6,'0000242','2.2.2',20.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (7,'0000284','2.2.1',15.00);

Listando os centros de custos cadastrados ...
-- Listando conteudo de centro_custo 
SELECT * FROM centro_custo ORDER BY 1;
Figura 02 - Lista de Centro Custo

Listando os movimentos cadastrados, referenciando os centros de custos ...
-- Listando conteudo de movimento 
SELECT * FROM movimento;
Figura 03 - Lista de Movimento Script apenas para SQL Server 2012

-- Query SQL Centro de Custo, para SQL Server 2012, exemplo: 
  SELECT cc.id_centro_custo
       , cc.descricao 
       , sum(m.valor_movimento) AS total_conta 
    FROM centro_custo cc 
    JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo + '%'  -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo 
       , cc.descricao 
ORDER BY cc.id_centro_custo ASC
       ; 
Figura 04 - Query Centro Custo com Resultado em SQL Server 2012

Script apenas para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2
  
-- Query SQL Centro de Custo, para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2, exemplo: 
  SELECT cc.id_centro_custo
       , cc.descricao 
       , sum(m.valor_movimento) AS total_conta 
    FROM centro_custo cc 
    JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo || '%'  -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo 
       , cc.descricao 
ORDER BY cc.id_centro_custo ASC
       ; 
Figura 05 - Query Centro Custo com Resultado em Oracle 11g R2


A grande dica está na junção dos campos de conta usando like concatenado com '%' no final

Como no exemplo com Oracle 11g R2, abaixo descrito:

ON m.id_centro_custo LIKE cc.id_centro_custo || '%'

Existem outras formas de se chegar ao mesmo resultado, uma delas seria implementar union em contas de grupo e depois criar uma view, mas, fica complicado e não elegante, já que, será necessário alterar a query toda vez que o plano de contas mudar a estrutura.

Outra forma seria usar querys recursivas, que seria bem mais elegante que a opção anterior, entretanto, mais complexa, mas quanto ao uso de recursividade em querys, nem todos os SGBDRs atualmente dão suporte a essa técnica, outro problema reside no fato do desempenho e consumo de recursos, pois quanto mais níveis tiver o plano de contas maior será a pilha, em outras palavras, consumo de memória e processamento no servidor do banco de dados alto.

Eu particularmente já tive a oportunidade fazer parte de uma equipe de desenvolvimento, em que o meu chefe e a própria equipe insistiram em traçar uma modelagem que usava querys recursivas, apesar de não concordar, como era novato na equipe, não me deram crédito, então me reservei a sabedoria do silêncio e mesmo não concordando aproveitei a oportunidade para fazer acontecer e vê como ficaria um sistema de custos com uso maciço de querys recursivas, foi divertido, mas ainda não recomendo, pois a complexidade é alta das querys recursivas e da codificação da aplicação também, tornando o compartilhamento do conhecimento difícil, como também a manutenção da aplicação, considerando ainda o consumo extremo de memória e processamento a nível de infra-estrutura.

Mais uma vez espero ter ajudado.


Fique na Paz do Senhor Jesus Cristo !!!

23 comentários:

  1. Fiz um teste utilizando a mesma estrutura no MySql no entanto só me retornou as contas totalizadas sem as (pais) superiores qual a alteração a ser feita para que funcione?

    ResponderExcluir
  2. Só obtive tipo:
    id_centro_custo descricao total_conta

    1.1.2 Escritorio 50
    1.2.2 Brinquedos 260
    2.2.1 Materiais 50

    ResponderExcluir
  3. Olá Anônimo, quando escrevi este artigo, não havia testado em MySQL; realmente no MySQL com "||" vai dá o resultado como você apontou, para solucionar o problema em questão use a função concat do MySQL depois do LIKE, como no exemplo:

    SELECT cc.id_centro_custo
    , cc.descricao
    , sum(m.valor_movimento) AS total_conta
    FROM centro_custo cc
    JOIN movimento m
    ON m.id_centro_custo LIKE concat(cc.id_centro_custo, '%')
    GROUP BY cc.id_centro_custo
    , cc.descricao
    ORDER BY cc.id_centro_custo ASC
    ;

    Eu testei na versão 5.5.20 do MySQL

    SELECT @@version; -- 5.5.20

    Mais uma vez espero ter ajudado!

    ResponderExcluir
  4. Olá! Ajudou sim, ajudou mto a esclarecer minha dúvida. Agradeço a disponibilidade e a presteza com que você esclareceu minha dúvida.

    ResponderExcluir
  5. Outro artigo relacionado
    http://emersonhermann.blogspot.com.br/2013/01/desenvolvendo-querys-sql-para-razao-e.html

    ResponderExcluir
  6. Muito bom o artigo, o problema que estou passando é que em contas de despesas vai de 2.1 até 2.22 então a soma não está indo certa pois 2.1 vai se misturar com as 2.10, 2.11, 2.12 ..., você possui alguma solução para este problema? Obrigado

    ResponderExcluir
    Respostas
    1. Olá Flavio, o moído não é na query e sim na sua estrutura de contas, sugiro que crie uma conta sintética para 2.1 e analíticas para 2.1.01 a 2.1.22, lembrando que é necessário obedecer as mascaras no seu plano de contas exemplo: 9.9.99, neste caso tem-se 3 níveis, sendo 1 digito para primeiro nível, 1 digito para o segundo e dois dígitos para o terceiro nível, lembrando de colocar zeros a esquerda, no seu caso parece que não está sendo considerado isto, porquê, pois no exemplo exposto da conta 2.1 continuou até 9 quando chega em 2.10 até 2.22 sai da mascara com 1 digito para 2 dígitos, o segundo nível teria que ter dois dígitos então todas as contas nesse nível teriam que ter 2 dígitos na mascara, isso, é um problema que os contadores entendem bem.

      Excluir
  7. Por Favor é possível fazer isso no access 2010?? Se for Por Favor apresente a query. Grato Kurt

    ResponderExcluir
    Respostas
    1. Olá Kurt,

      Access não é a minha praia, dada a limitação do mesmo quanto ao SQL. Nos testes que fiz não obtive o resultado desejado. O Máximo que consegui foi totalizar as contas com a query abaixo, a subtotalização de contas talvez seja possível escrevendo em alguma linguagem a exemplo do VB.

      SELECT cc.id_centro_custo
      , cc.descricao
      , sum(m.valor_movimento) AS total_conta
      FROM centro_custo cc
      INNER JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo
      GROUP BY cc.id_centro_custo
      , cc.descricao ;

      Excluir
    2. A subtotalização é possível usando qualquer linguagem de programação invertendo a estrutura de contas, isto é, ordenando em forma decrescente por contas em conjunto com a query informada acima.

      Excluir
  8. Segue um código em VB no link abaixo, bem básico apenas mostrar como funciona o algoritmo.
    http://emersonhermann.blogspot.com.br/2013/12/codigoplanocontasvb.html

    ResponderExcluir
  9. Muito bom este artigo.Veio de encontro ao que estava necessitando. Valeu mesmo.
    Se me permite, gostaria de dar uma colaboração àqueles que trabalham com o mysql: Se quiserem que todo o centro de custo apareça na pesquisa, mesmo que ele esteja sem movimento, é só alterar a sql para esta:
    SELECT cc.id_centro_custo
    , cc.descricao
    , sum(case when m.valor_movimento > 0 then m.valor_movimento else 0 end ) AS total_conta
    FROM centro_custo cc
    LEFT JOIN movimento m
    ON m.id_centro_custo LIKE concat(cc.id_centro_custo, '%')
    GROUP BY cc.id_centro_custo
    , cc.descricao
    ORDER BY cc.id_centro_custo ASC
    ;

    O LEFT JOIN VAI DISPONIBILIZAR TODOS E O CASE JOGARÁ ZERO NOS DADOS SEM REGISTROS.
    Espero que seja útil para alguém.
    Mais uma vez , parabéns pela ajuda . Este site é muito bom. Já compartilhei em minhas redes sociais

    ResponderExcluir
  10. Parabéns pelo Post. Essa técnica de passar o LIKE concatenando com '%' não sabia. Sucesso!!

    ResponderExcluir
  11. SHow De Bola, o problema no meu caso é que demora cerca de 1min:40 segundos dependendo da movimentacao da empresa =( mas a estrutura e os valores funcionaram 100%
    eu estou realizando com o mysql e existe 9 niveis de plano de contas. '-'

    ResponderExcluir
    Respostas
    1. Rafael, geralmente a demora se deve por alguns fatores, o primeiro como o seu BD está configurado, o segundo quantos registros tem em movimento, e o terceiro quantos registros tem em centro_custo. Como solução sugiro algumas opções que são: Opção 1o - Recomendo indexar a coluna id_centro_custo em movimento já que centro_custo elá é PK (indexado por padrão); Opção 2o - Criar view; Opção 3o (MAIS RECOMENDÁVEL) - Criar store procedures e/ou stores functions; Opção 4o (MAIS COMPLEXA) - Criar uma tabela de saldo como movimento encerrado e acessar somente movimento selecionado: Opção 5o (NUNCA FIZ EM TODOS) - Testar em outros SGBDR, a exemplo do Oracle 11g R2, que é o meu caso, funciona com excelente desempenho, também testei com Firebird, mas o desempenho não foi bom; Opção 6o - Usar um mix de todas as opções citadas anteriormente.

      Excluir
    2. Opção 7o - Rever configurações do Servidor de Aplicação e BD
      PS: Também quanto mais níveis, maior será a demanda (tempo) para processar.

      Excluir
  12. Ola, Grato por suas opções, oq eu fiz foi rever as configuracoes do BD consegui diminuir 40 segundos rsrs.
    Ja fiz os index e ta bem mais rapido do que antes.
    Mas vou optar pela opcao 3 mesmo. ^^
    Grato por sua resposta e grato pelo post :D

    ResponderExcluir
    Respostas
    1. Apenas como demostração de caso de sucesso, eu implementei a mestra estrutura e conhecimentos, demonstrados nesse artigo e em outro [1] complementar, escritos neste blog em um Sistema de Contabilidade padrão COSIF usando linguagem de programação PHP e PLSql sendo banco de dados Oracle 11g R2, e posso te dizer que: Além da exatidão, item obrigatório, o desempenho é excelente, prentendo também testar com Postgres usando PG/PlSql, pelo que conheço o Postgres creio que os testes serão bem-sucedidos.
      [1] http://emersonhermann.blogspot.com.br/2013/01/desenvolvendo-querys-sql-para-razao-e.html

      Excluir
  13. Testei no firebird e funcionou 100%, muito obrigado!
    Pablo George

    ResponderExcluir