*/

quinta-feira, 29 de março de 2012

Store Function SQL Server para validar CHAVE de NFE

Store Function SQL Server para validar CHAVE de NFE

Store function para validar CHAVE de NFE em SQL do MSSQL SERVER 2005 e 2008.
Essa function serve para validar Nota Fiscal Eletrônica, a entrada de parametro é um varchar
retorna 1 para CHAVE de NFE válida e 0 para CHAVE de NFE inválida e NULL quanto for NULO.


Segue a function....


IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_calcula_dv_nfe]') 
               AND type IN (N'FN')
           )
   DROP FUNCTION usf_calcula_dv_nfe;
 
GO
  
CREATE FUNCTION usf_calcula_dv_nfe(@number varchar(max)) RETURNS bit
-- Nome Artefato/Programa..: usf_calcula_dv_nfe.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 29/03/2012
-- Data Atualizacao........: 29/03/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)  que calcula o digito verificador da chave de acesso NFe usando o modulo 11
-- OBS.....................: A entrada é um varchar e o retorno é um  bit 1 para valido e 0 para invalido 
-- ........................: 
--  
AS
BEGIN
 DECLARE @i       int,
         @ix      int, 
         @c       int,
         @keyx    int,
         @numberx varchar(44), 
         @r       int
 
      
 SET @c    = 2
 SET @i    = 1
 SET @ix   = 43 
 SET @keyx = 0 
 
 -- Pre-validacao 1, se e nulo, entao retorna nulo
 IF @number IS NULL BEGIN
    SET @r = NULL
    RETURN (@r)  
 END --fim_se      
 
 -- Pre-validacao 2, se e maior que 44 digitos , entao retorna 0 
 IF LEN(@number) < 44 BEGIN
    SET @r = 0 
    RETURN (@r)
 END --fim_se
 
 -- Pre-validacao 3, se e tem alguma letra, entao retorna 0 
 IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
    SET @r = 0
    RETURN (@r)
 END --fim_se  
 
 -- Pre-validacao 4, se e menor que 44 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros 
 SET @numberx = @number
 IF LEN(@number) < 44 BEGIN
    SET @numberx = REPLICATE('0',44-LEN(@number))+@number
 END --fim se
  
 -- Loop por cada numero mutiplicando-o pelos valores de @c
 -- Simulando o for invertido com passo -1 
 WHILE (@i <= (len(@numberx)-1)) BEGIN 
   
    --Verifica se o valor de @c for maior que nove, entao passa o valor pra 2
    IF @c > 9 BEGIN
       SET @c = 2
    END --fim_se

    --Soma os valores mutiplicados
    SET @keyx = @keyx + (convert(int,substring(@number,@ix,1)) * @c) 
  
    --Controle de contadores
    SET @c = @c + 1
    SET @i = @i + 1 
    SET @ix = @ix - 1 
 
 END  --fim_enquanto 
  
 --Obtem o digito verificador
 IF ((@keyx % 11) = 0 OR (@keyx % 11) = 1) BEGIN
    SET @r = 0
 END ELSE BEGIN --senao   
    SET @r  = 11 - (@keyx % 11)
 END --fim_se
  
 IF @r = convert(int,substring(@numberx,len(@numberx),1)) BEGIN
    SET @r = 1 
 END ELSE BEGIN  --senao   
    SET @r = 0       
 END --fim_se
  
 RETURN (@r)
 
END;
GO
-- Chamada da function 

SELECT dbo.usf_calcula_dv_nfe('24110509540525000194550010000007091242050760');
SELECT dbo.usf_calcula_dv_nfe('24110509540525000194550010000007071681710981');

-- Habilitando no campo da CHAVE de NFE (chave_nfe)
/*
ALTER TABLE nota_fiscal_eletronica
  ADD CONSTRAINT ck_nota_fiscal_eletronica_usf_calcula_dv_nfe 
CHECK (dbo.usf_calcula_dv_nfe(chave_nfe)=1); 
*/ 
GO

terça-feira, 27 de março de 2012

Formatar CPF em SQL

Store function para formatar CPF em SQL Server

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_formata_cpf]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_formata_cpf;
GO

CREATE FUNCTION dbo.usf_formata_cpf(@cpf varchar(max), @mascara bit) RETURNS varchar(max) 
AS
-- Nome Artefato/Programa..: usf_formata_cpf.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 21/02/2012
-- Data Atualizacao........: 21/02/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 CPF
-- OBS.....................: A entrada é um varchar e o retorno é um varchar formatado do cpf, parametro mascara setado em 0 apenas string com zeros, 1 formata o cpf de fato 
-- ........................: 
--  
BEGIN

  DECLARE @cpf_temp varchar(max) 
  
  -- Pre-validacao 1, se e nulo, entao retorna nulo
  IF @cpf IS NULL BEGIN
     RETURN (@cpf)  
  END --fim_se      

  -- Pre-validacao 2, se e diferente de 11 digitos , entao retorna 0 
  IF LEN(@cpf) > 11 BEGIN
     RETURN (@cpf)
  END --fim_se
  
  -- Pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 
  IF (SELECT CASE WHEN patindex('%[^0-9]%', @cpf) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
     RETURN (@cpf)
  END --fim_se  

  -- Pre-validacao 4, se e menor que 11 dig, pode ser oriundo de um bigint, entao colocar zeros a frente
  SET @cpf_temp = @cpf 
  IF LEN(@cpf) < 11 BEGIN
     SET @cpf_temp = REPLICATE('0',11-LEN(@cpf))+@cpf
  END --fim_se 
  
  -- Se e para formatar mesmo 
  IF @mascara = 1 BEGIN
     SET @cpf_temp = SUBSTRING(@cpf_temp,1,3) + '.' + SUBSTRING(@cpf_temp,4,3) + '.' + SUBSTRING(@cpf_temp,7,3) + '-' + SUBSTRING(@cpf_temp,10,2) 
  END --fim_se 
  
  RETURN (@cpf_temp) 
  
END;
GO

-- chamada da function 

--SELECT dbo.usf_formata_cpf('481604472z',0);
--SELECT dbo.usf_formata_cpf('0541371479',1);
--SELECT dbo.usf_formata_cpf('05413714793',1);
--SELECT dbo.usf_formata_cpf(18404,1);
--SELECT dbo.usf_formata_cpf(18404,0);

Validar CPF em SQL

Como validar CPF em SQL Server, store function para validar CPF em T-SQL.

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

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

Segue function....




IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cpf]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_valida_cpf;
GO

CREATE FUNCTION dbo.usf_valida_cpf(@cpf varchar(max)) RETURNS bit 
AS
-- Nome Artefato/Programa..: usf_valida_cpf.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) baseado em Script feito por:
-- ........................: Cristiano Martins Alves em http://www.devmedia.com.br
-- Data Inicio ............: 19/05/2011
-- Data Atualizacao........: 21/02/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 CPF
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--  
BEGIN
  DECLARE @index   int,
          @sumx    int,
          @dig1    int,
          @dig2    int,
          @cpf_temp   varchar(11),
          @cpfx             varchar(11), 
          @dig_equal  bit,  --0 para negativa e 1 para afirmativa
          @r    bit

  -- Pre-validacao 1, se e nulo, entao retorna nulo
  IF @cpf IS NULL BEGIN
     SET @r = NULL
     RETURN (@r)  
  END --fim_se      

  -- Pre-validacao 2, se e maior que 11 digitos , entao retorna 0 
  IF LEN(@cpf) > 11 BEGIN
     SET @r = 0 
     RETURN (@r)
  END --fim_se
  
  
  -- Pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 
  IF (SELECT CASE WHEN patindex('%[^0-9]%', @cpf) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
     SET @r = 0
     RETURN (@r)
  END --fim_se  
  
  -- Pre-validacao 4, se e menor que 11 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros 
  SET @cpfx = @cpf
  IF LEN(@cpf) < 11 BEGIN
     SET @cpfx = REPLICATE('0',11-LEN(@cpf))+@cpf
  END --fim se
   
  /*
  -- Pre-validcao 5, se a sequencia tem numeros iguais, entao retorna 0 
     Verificando se os digitos sao iguais.
     A Principio CPF com todos os numeros iguais sao invalidos,
      apesar de validar o calculo do digito verificador
     EX: O CPF 00000000000 é inválido, mas pelo calculo seria valido
  */

  SET @cpf_temp = SUBSTRING(@cpfx,1,1)

  SET @index = 1
  SET @dig_equal = 1

  WHILE (@index <= 11) BEGIN
     IF SUBSTRING(@cpfx,@index,1) <> @cpf_temp BEGIN
        SET @dig_equal = 0
     END 
     SET @index = @index + 1
  END;

  --Caso os digitos nao sejam todos iguais, comeca o calculo dos digitos
  IF @dig_equal = 0 BEGIN

     --Calculo do 1º digito
     SET @sumx = 0
     SET @index = 1
     
     WHILE (@index <= 9) BEGIN   
        SET @sumx = @sumx + CONVERT(int,SUBSTRING(@cpfx,@index,1)) * (11 - @index)       
        SET @index = @index + 1      
     END

     SET @dig1 = 11 - (@sumx % 11)

     IF @dig1 > 9 BEGIN     
        SET @dig1 = 0       
     END 

     -- Calculo do 2º digito
     SET @sumx = 0
     SET @index = 1
     
     WHILE (@index <= 10) BEGIN   
        SET @sumx = @sumx + CONVERT(int,SUBSTRING(@cpfx,@index,1)) * (12 - @index)     
        SET @index = @index + 1 
     END  --fim_enquanto 

     SET @dig2 = 11 - (@sumx % 11)

     IF @dig2 > 9 BEGIN    
        SET @dig2 = 0     
     END 

     -- Validando
     IF (@dig1 = SUBSTRING(@cpfx,LEN(@cpfx)-1,1)) AND (@dig2 = SUBSTRING(@cpfx,LEN(@cpfx),1)) BEGIN
        SET @r = 1 
     END ELSE BEGIN  --senao   
       SET @r = 0       
     END --fim_se
     
  END ELSE BEGIN --senao 

     SET @r = 0  -- invalido
    
  END --fim_se
  
  RETURN (@r) 
  
END;
GO

-- chamada da function 

--SELECT dbo.usf_valida_cpf('80481604472');  -- retorna 1
-- ou 
--SELECT dbo.usf_valida_cpf(80481604472); -- retorna 1 
-- ou 
--SELECT dbo.usf_valida_cpf('05413714793'); -- retorna 1
-- ou 
--SELECT dbo.usf_valida_cpf(05413714793); -- retorna 1



segunda-feira, 12 de março de 2012

Validando Cadastro Específico do INSS - CEI em SQL Server

Validando Cadastro Específico do INSS - CEI em SQL Server, como validar CEI em SQL Server usando functions do TRANSACT SQL
-- Nome Artefato/Programa..: usf_valida_cei.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 11/03/2012
-- Data Atualizacao........: 11/03/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 validar numero de CEI (Cadastro Especifico de INSS) 
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--
IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cei]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_valida_cei;
GO
CREATE FUNCTION  usf_valida_cei(@number varchar(max)) RETURNS bit AS
/*
CEI - Cadastro Específico do INSS 
O algorítmo abaixo demonstra como obter o dígito verificador da matricula CEI.
Formato : EE.NNN.NNNNN/AD
Onde:
EE - Número
NNNNNNNN - Número
A - Atividade
D - Dígito Verificador
a) Multiplicar os últimos 11 algarismos pelos seus respectivos pesos, conforme abaixo:
Pesos: 7,4,1,8,5,2,1,6,3,7,4
Algarismos: EENNNNNNNNA

Cálculo
7 * E = X1
4 * E = X2
1 * N = X3
8 * N = X4
5 * N = X5
2 * N = X6
1 * N = X7
6 * N = X8
3 * N = X9
7 * N = X10
4 * A = X11
D (posição do dígito)


b) Somar todos os produtos obtidos no item "a"

Soma = X1+2+X3+X4+X5+X6+X7+X8+X9+X10+X11

c) Com o total obtido no item "b", somar o algarismo da unidade com o algarismo da dezena.

Total = Dezena de soma + Unidade de soma

d) Subtrair de 10 o algarismo da unidade do obtido no item "c".

Resultado = 10 - Unidade de Total

O algarismo da unidade do resultado da subtração será o dígito verificador.

Digito verificador = Unidade de Resultado
*/  

BEGIN


 DECLARE @weight   varchar (11)
 DECLARE @total   int
 DECLARE @c    int
 DECLARE @result   int
 DECLARE @r    bit
 DECLARE @number_dig  int
 DECLARE @number_dig_c int
 DECLARE @number_dig_x varchar(2) 

 SET @weight = '74185216374'  -- peso estabelecido para gerar CEI 
 SET @total = 0 
 SET @c  = 1  
 SET @number_dig = substring(@number,len(@number),1) -- ultimo digito extraido do parametro informado

 --fazendo um uma pre-validação do CEI, validação 1 se é nulo
 IF @number IS NULL BEGIN
  SET @r = NULL
  RETURN (@r) 
 END --fim_se   

 --fazendo um uma pre-validação do CEI, validação 2 se o tamanho é diferente de 12 digitos 
 IF LEN(@number) > 12  BEGIN
  SET @r = 0
  RETURN (@r)
 END  --fim_se

 --fazendo um uma pre-validação do CEI, validação 3 se não for nulo e for mair que 12,
 -- ainda pode ser uma string 
 IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
  SET @r = 0
  RETURN (@r)
 END --fim_se   

 --fazendo um uma pre-validação do CEI, validação 4 se está vindo c/ 12 dig zerados
 IF CAST(@number AS bigint)=0 BEGIN
  SET @r = 0
  RETURN (@r)
 END  --fim_se  


 WHILE (@c<=11) BEGIN
  SET @result = cast (substring(@number, @c, 1) AS int) * cast(substring(@weight,@c, 1) AS int) 
  SET @total  = @total + @result
  SET @c = @c + 1 
 END -- fim_enquanto, neste caso, while simulando for 

 SET @number_dig_x = right(left(cast (@total as varchar(max)), 12), 2)
 SET @number_dig_c = (cast(left(@number_dig_x, 1) as int)) + (cast(right(@number_dig_x, 1) as int))
 SET @number_dig_c = 10 - (cast (Right(Left(@number_dig_c, 12), 1) as int))


 IF (@number_dig_c > 9) BEGIN
  SET @number_dig_c = 0
 END ELSE BEGIN 
  SET @number_dig_c = 10 - @number_dig_c
 END 

 IF (@number_dig <> @number_dig_c) BEGIN 
  SET @r = 0
 END ELSE BEGIN
  SET @r = 1 
 END 
    
 RETURN (@r)

END;
GO
-- Exemplo de chamada da function 
-- SELECT dbo.usf_valida_cei('741852163748');