Formatar CNPJ em SQL

Store function para formatar CNPJ em SQL Server

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_formata_cnpj]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_formata_cnpj;
GO
 
CREATE FUNCTION dbo.usf_formata_cnpj(@cnpj varchar(max), @mascara bit) RETURNS varchar(max) 
AS
-- Nome Artefato/Programa..: usf_formata_cnpj.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 19/09/2012
-- Data Atualizacao........: 19/09/2012
-- Versao..................: 0.01
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure (Function) para mascara o numero do CNPJ
-- OBS.....................: A entrada é um varchar e o retorno é um varchar formatado do CNPJ, parametro mascara setado em 0 apenas string com zeros, 1 formata o CNPJ de fato 
-- ........................: 
--  
BEGIN
 
  DECLARE @cnpj_temp varchar(max) 
   
  -- Pre-validacao 1, se e nulo, entao retorna nulo
  IF @cnpj IS NULL BEGIN
     RETURN (@cnpj)  
  END --fim_se      
 
  -- Pre-validacao 2, se e maior que 14 digitos , entao retorna 0 
  IF LEN(@cnpj) > 14 BEGIN
     RETURN (@cnpj)
  END --fim_se
   
  -- Pre-validacao 3, se e tem alguma letra no CNPJ, entao retorna 0 
  IF (SELECT CASE WHEN patindex('%[^0-9]%', @cnpj) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
     RETURN (@cnpj)
  END --fim_se  
 
  -- Pre-validacao 4, se e menor que 14 dig, pode ser oriundo de um bigint, entao colocar zeros a frente
  SET @cnpj_temp = @cnpj
  IF LEN(@cnpj) < 14 BEGIN
     SET @cnpj_temp = REPLICATE('0',14-LEN(@cnpj))+@cnpj
  END --fim_se 
   
  -- Se e para formatar mesmo 
  IF @mascara = 1 BEGIN
     SET @cnpj_temp = SUBSTRING(@cnpj_temp,1,2) + '.' + SUBSTRING(@cnpj_temp,3,3) + '.' + SUBSTRING(@cnpj_temp,6,3) + '/' + SUBSTRING(@cnpj_temp,9,4) + '-' + SUBSTRING(@cnpj_temp,13,2) 
  END --fim_se 
   
  RETURN (@cnpj_temp) 
   
END;
GO
 
-- chamada da function 

-- test 1
SELECT dbo.usf_formata_cnpj('00000000000191',1); -- Banco do Brasil Sede - 00.000.000/0001-91
SELECT dbo.usf_formata_cnpj('00000000000191',0); -- Banco do Brasil Sede - 00000000000191
-- test 2 
SELECT dbo.usf_formata_cnpj('191',1); -- Banco do Brasil Sede - 00.000.000/0001-91
SELECT dbo.usf_formata_cnpj('191',0); -- Banco do Brasil Sede - 00000000000191

Validar CNPJ em SQL

Como validar CNPJ em SQL Server, store function para validar CNPJ em T-SQL (Transact-SQL)

Esta store function, foi desenvolvida pensando no armazenamento do CNPJ em BIGINT, mas pode ser usada facilmente sem adaptações com CNPJ tipo VARCHAR(14) sem mascaras ou formatações.

Recomendável persistência do CNPJ em BIGINT por motivo principal de desempenho.

Segue function....

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cnpj]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_valida_cnpj;
GO
 
CREATE FUNCTION dbo.usf_valida_cnpj(@cnpjx varchar(14)) RETURNS bit
AS
-- Nome Artefato/Programa..: usf_valida_cnpj.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) baseado em Script feito por:
-- ........................: Baseado em código de Fernando Jacinto da Silva em http://www.devmedia.com.br
-- Data Inicio ............: 18/09/2011
-- Data Atualizacao........: 19/09/2012
-- Versao..................: 0.02
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure (Function) para validar o numero do CNPJ
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--  
BEGIN

DECLARE
        @cnpj   varchar(14) 
      , @indice int
      , @soma   int
      , @dig1   int
      , @dig2   int
      , @var1   int
      , @var2   int
      , @resultado bit

    SET @cnpj = @cnpjx 
    SET @soma = 0
    SET @indice = 1
    SET @resultado = 0

    -- pre-validacao 1, se e nulo, entao retorna nulo
    IF @cnpj IS NULL BEGIN
        SET @resultado = NULL
        RETURN (@resultado)  
    END --fim_se      
 
    -- pre-validacao 2, se e maior que 11 digitos , entao retorna 0 
    IF LEN(@cnpj) > 14 BEGIN
        SET @resultado = 0 
        RETURN (@resultado)
    END --fim_se
   
    -- pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 
    IF (SELECT CASE WHEN patindex('%[^0-9]%', @cnpj) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
       SET @resultado = 0
       RETURN (@resultado)
    END --fim_se   
 
    -- pre-validacao 4, se e menor que 11 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros 
    IF LEN(@cnpj) < 14 BEGIN
        SET @cnpj = REPLICATE('0',14-LEN(@cnpj))+@cnpj
    END --fim se 
 
    /* algorítimo para o primeiro dígito 543298765432 */
    /* cálculo do 1º dígito */
    /* cálculo da 1ª parte do algorítiom 5432 */

    SET @var1 = 5 /* 1a parte do algorítimo começando de "5" */

    WHILE (@indice <= 4) BEGIN

        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var1

        SET @indice = @indice + 1 /* navegando um-a-um até < = 4, as quatro primeira posições */

        SET @var1 = @var1 - 1  /* subtraindo o algorítimo de 5 até 2 */

    END

    /* cálculo da 2ª parte do algorítiom 98765432 */

    SET @var2 = 9

    WHILE (@indice <= 12) BEGIN

        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var2

        SET @indice = @indice + 1

        SET @var2 = @var2 - 1            
    END

    SET @dig1 = (@soma % 11)

    /* se o resto da divisão for < 2, o digito = 0 */

    IF @dig1 < 2 BEGIN

        SET @dig1 = 0

    END ELSE BEGIN /* se o resto da divisão não for < 2*/

        SET @dig1 = 11 - (@soma % 11)
    END

    /* cálculo do 2º dígito */
    /* zerando o indice e a soma para começar a calcular o 2º dígito*/   

    SET @indice = 1
    SET @soma = 0

    /* cálculo da 1ª parte do algorítiom 65432 */

    SET @var1 = 6 /* 2a parte do algorítimo começando de "6" */

    SET @resultado = 0  

    WHILE (@indice <= 5) BEGIN
  
        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var1
        SET @indice = @indice + 1 /* navegando um-a-um até < = 5, as quatro primeira posições */
        SET @var1 = @var1 - 1       /* subtraindo o algorítimo de 6 até 2 */

    END

    /* cálculo da 2ª parte do algorítiom 98765432 */
    SET @var2 = 9

    WHILE (@indice <= 13) BEGIN

        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var2
        SET @indice = @indice + 1
        SET @var2 = @var2 - 1            

    END
 
    SET @dig2 = (@soma % 11) 

    /* se o resto da divisão for < 2, o digito = 0 */

    IF @dig2 < 2 BEGIN

        SET @dig2 = 0

    END ELSE BEGIN /* se o resto da divisão não for < 2*/

        SET @dig2 = 11 - (@soma % 11)
    END
 
    -- validando

    IF (@dig1 = SUBSTRING(@cnpj,LEN(@cnpj)-1,1)) AND (@dig2 = SUBSTRING(@cnpj,LEN(@cnpj),1)) BEGIN

        SET @resultado = 1

    END ELSE BEGIN

        SET @resultado = 0 
  
    END 

RETURN (@resultado)

END;
GO

-- chamada da function 

-- test 1
SELECT dbo.usf_valida_cnpj('00000000000191'); -- Banco do Brasil Sede - 1 (válido)
-- test 2 
SELECT dbo.usf_valida_cnpj('191'); -- Banco do Brasil Sede - 1 (válido)
-- test 3
SELECT dbo.usf_valida_cnpj('00000000000192'); -- Banco do Brasil Sede - 0 (inválido) 

Cache de memória do SQL Server

O cache de memória, é uma área de memória reservada pelo SQL Server, com o objetivo de se evitar ao máximo acesso ao disco magnético, pois o barramento deste periférico é bem inferior ao da memória e do processador, analogamente seria como sistema de encanamento com canos pequenos e grandes, mas para manter a vazão cria-se um caixa de água na mudança de diâmetro desses canos, o cache de memória é a caixa de água, sendo assim a entrada e a saída no meio magnético (Imput-Output, I/O) será reduzida, acelerando a execução de procedures, transações, querys que são processadas com maior maior frequência.

Quando o SQL Server começa apresentar lentidão e as operações começam a mostrar erros devido à falta de memória, ou muitas operações de I/O, neste caso recomenda-se, verificar a necessidade de redimensionar a memória do servidor SQL Server.

No momento que o servidor SQL Server é reinicializado toda a memória cache é automaticamente zerada, isto é limpa, porém em servidor de produção, esse procedimento torna-se inviável, neste cenário o ideal seria redimensionar o servidor, porém como forma paliativa, são sugeridos três comandos que podem ser úteis, são eles:

-- limpa as entradas do cache do sistema
DBCC FREESYSTEMCACHE; 
-- limpa as entradas do cache da sessao 
DBCC FREESESSIONCACHE;
-- elimina todas as entradas do cache de procedures
DBCC FREEPROCCACHE;

Quando executado os três comandos acima no servidor, será limpado a memória cache e por conseguinte liberado memória de forma imediata para o SQL Server.

Outra forma de liberar memória cache é na otimização de consultas no SQL Server, bem comum compararmos diferentes formas de construção de uma consulta (query), normalmente dentro de uma mesma sessão no SQL Server, por examplo usando MS SQL Server Management Studio.

O grande moído é que a primeira consulta (query) a ser executada, sempre é mais rápido do que a primeira, pois na primeira vez em que é executada ela é armazenada na memória cache e já segunda em diante a execução se baseia na memória cache.

Analisando com critério a query executada, faz-se necessário sempre evitar o uso do cache, ao menos no momento de otimização de consultas.

Três comandos simples a ser executado na sessão Management Studio para solucionar, eliminar, desprezar o cache de memória, são eles:

-- elimina paginas de buffer limpas
DBCC DROPCLEANBUFFERS;
-- elimina todas as entradas do cache de procedures
DBCC FREEPROCCACHE;
-- limpa as entradas de cache nao utilizadas 
DBCC FREESYSTEMCACHE ( 'ALL' );

A dica, é executar esses três comandos supracitados, no momento de otimizar querys.

Fique na Paz do Senhor Jesus!

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 !!!