quinta-feira, 23 de abril de 2015

Bíblia Português João Ferreira de Almeida - Edição Revista e Corrigida em formato Linux Fortune

Para quem gosta de usar o Linux Fortune Segue a Bíblia em Português João Ferreira de Almeida - Edição Revista e Corrigida em formato Linux Fortune, conforme link abaixo: BÍBLIA_JFARC_FORTUNES

quinta-feira, 2 de abril de 2015

Ultimo Dia Util Considerando Feriados em Oracle PL/SQL

/* Geralmente é complicado obter o ultimo dia válido considerando os feriados nacionais,
muito usado no ramo financeiro. */

/* Esse artigo pretende dá uma opção de como obter o ultimo dia util dentre várias soluções
encontradas na net, com o uso de stores functions do Oracle PL/SQL */

/* Mostrando como criar uma função SQL Oracle isto é em PL/SQL para obter o ultimo dia útil, considerando os feriados nacionais dentre outros assuntos correlacionados. */

/* Primeiro criamos uma tabela feriado para melhor elucidar as store functions, conforme script abaixo: */


-- DROP TABLE FERIADO;
CREATE TABLE FERIADO
(
   ID_FERIADO                       NUMBER(10,0)       NOT NULL ENABLE
 , DATA                             DATE               NOT NULL ENABLE
 , DESCRICAO                        VARCHAR2(50 BYTE)  NOT NULL ENABLE
 , ID_TIPO_FERIADO                  NUMBER(3,0)        NOT NULL ENABLE -- 1 NACIONAL, 2 ESTADUAL, 3 MUNICIPAL, 4 OUTROS
 , OBS                              NVARCHAR2(15)
 , CONSTRAINT PK_FERIADO_ID_FERIADO PRIMARY KEY (ID_FERIADO)
 , CONSTRAINT UQ_FERIADO_DATA UNIQUE (DATA)
);

-- DROP SEQUENCE FERIADO_FCSEQ; 
CREATE SEQUENCE FERIADO_FCSEQ
INCREMENT BY 1
START WITH 1
NOCYCLE;

-- DROP TRIGGER TG_FERIADO_ID_FERIADO_BI;
CREATE OR REPLACE TRIGGER TG_FERIADO_ID_FERIADO_BI BEFORE INSERT ON FERIADO
FOR EACH ROW
 WHEN (new.ID_FERIADO IS NULL) BEGIN
  SELECT FERIADO_FCSEQ.NEXTVAL INTO :new.ID_FERIADO FROM dual;
END;
/
ALTER TRIGGER TG_FERIADO_ID_FERIADO_BI ENABLE;


/* Se desejar usar o o arquivo CSV dos feriados chamado FERIADOS.CSV com CRLF no final de cada linha, use o SQL Loader do Oracle para dá carga do arquivo, evitando assim digitação de datas, é possível obtê-lo CLICANDO AQUI! */
/* Criando o arquivo de controle chamado FERIADO.CTL salvo no diretorio C:\TEMP */
load data
  infile 'FERIADO.CSV'
  into table feriado
  fields terminated by "|"
  (
      data  DATE "YYYY-MM-DD"
    , descricao
    , id_tipo_feriado
    , obs
  )
/* Depois executar o SQL Loader, no Linux é igual ao do Windows considerando nome da pasta, no Windows fica assim: */
/* Lembrando de considerar, isto é, permutar seu usuario e senha, ip_servidor e SID do Oracle do seu servidor*/
sqlldr usuario/senha@ip_servidor/SID control=FERIADO.CTL log=X.LOG bad-Z.BAD READSIZE=10000000


/* Depois criamos uma store function para idenficar dias feriados usando a tabela feriado conforme script abaixo: */


CREATE OR REPLACE FUNCTION usf_feriado (d in date) 
RETURN integer 
AS
  
  x integer; 

BEGIN 

  SELECT count(*) 
    INTO x
    FROM feriado 
   WHERE to_char(data,'YYYY-MM-DD') = to_char(d,'YYYY-MM-DD'); 
   
  IF (x >= 1) THEN 
      RETURN 1;
  ELSE 
      RETURN 0; 
  END IF;
  
END usf_feriado; 



/* Logo em seguida criamos um store function para tratar o ultimo dia util, conforme script abaixo: */


CREATE OR REPLACE FUNCTION usf_ultimo_dia_util ( dt_base in date ) 
RETURN date 
AS

  dt_basex date; 
  bo_fimx  boolean; 

BEGIN 

  dt_basex := dt_base; 
  bo_fimx  := false; 
  
  WHILE NOT (bo_fimx) LOOP 
  
      bo_fimx := to_char(dt_basex,'d') NOT IN ('1','7');
      
      IF usf_feriado(dt_basex) = 1 THEN 
          bo_fimx := false;
      END IF; 
      
      IF NOT (bo_fimx) THEN 
          dt_basex := dt_basex - 1; 
      END IF; 
      
  END LOOP; 
  
  RETURN dt_basex; 
  
EXCEPTION 

  WHEN others THEN 
  RAISE; 
  
  
END usf_ultimo_dia_util;


/* Testando o uso da function em SQL puro */


SELECT usf_feriado(sysdate) AS DT FROM DUAL; -- RETORNA 1 PARA FERIADO E 0 PARA OUTROS DIAS 

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- ALTERA DATA PARA FORMATO 'YYYY-MM-DD'

SELECT usf_ultimo_dia_util ('2015-04-05') from dual; -- RETORNA O ULTIMO DIA UTIL -> 2015-04-02

/* Espero ter ajudado! */

/* Seja Feliz!! */

/* APdSJC */

segunda-feira, 2 de março de 2015

Vida de DBA

Isso é a pura realidade de quem não sabe trabalhar com Postgres :)

Apenas compartilhando ...

Resultado, sobra pro DBA resolver a bagunça. Vida de DBA é assim!

...
Estraído do site www.vidadeprogramador.com.br
http://vidadeprogramador.com.br/2015/02/28/drop-database/

quarta-feira, 9 de julho de 2014

Como Listar Estrutura de Campos e Tabelas via query em SQL Server

Geralmente quando queremos saber informações sobre colunas e tabelas, no SQL Server usamos a store procedure sp_help [nome_tabela], mas o objetivo dessa query é filtrar determinadas condições a exemplo de tamanho de identificadores superiores a 30 caracteres, o clássico erro ORA-00972: identifier is too long. do Oracle, para quem trabalha com sistemas Multi SGBDR ou migração de dados, essa query resolve essa e outras perguntas.

Segue:

SELECT TABLE_CATALOG              AS BD
     , TABLE_SCHEMA               AS ESQUEMA_TABELA
     , TABLE_NAME                 AS TABELA 
     , COLUMN_NAME                AS COLUNA 
     , ORDINAL_POSITION           AS ORD_POS
     , DATA_TYPE                  AS TIPO 
     , CHARACTER_MAXIMUM_LENGTH   AS TAM_CARACTER
     , DATETIME_PRECISION         AS TAM_DATA
     , NUMERIC_PRECISION          AS TAM_NUMERICO
     , CASE WHEN CHARACTER_MAXIMUM_LENGTH  IS NOT NULL THEN
                CAST(CHARACTER_MAXIMUM_LENGTH AS NUMERIC(16,6))
            WHEN DATETIME_PRECISION IS NOT NULL THEN
                CAST(DATETIME_PRECISION AS NUMERIC(16,6))
            ELSE
                CAST(CAST(NUMERIC_PRECISION AS VARCHAR) + '.' + CAST( NUMERIC_SCALE AS VARCHAR) AS NUMERIC(16,6))
       END                        AS TAM
     , CASE WHEN IS_NULLABLE = 'YES' THEN
           'SIM'
       ELSE
           'NÃO'
       END                        AS [NULO?]
     , COLLATION_NAME             AS COLLATION
     , LEN(COLUMN_NAME)           AS TAM_NOME_COLUNA 
     , LEN(TABLE_NAME)            AS TAM_NOME_TABELA 
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE 1=1 
   --Testa condição de identificadores maior que 30 caracteres usado na migração para Oracle ou sistemas multi SGBDR
   --O clássico erro ORA-00972: identifier is too long. do Oracle, para quem trabalha com sistemas Multi SGBDR ou migração dados.
   --AND LEN(COLUMN_NAME) > 30 OR  LEN( TABLE_NAME) > 30 
   --Testa por nome de tabela   
   --AND TABLE_NAME  = 'NOME_TABELA'
   --Testa por coluna (campo) 
   --AND COLUMN_NAME = 'NOME_COLUNA'

Mais uma vez, espero ter ajudado. Seja feliz e fique na Paz de Jesus Cristo.

quinta-feira, 26 de dezembro de 2013

Código, Algoritmo de um Plano de Contas escrito em VB

Complementando um artigo descrito em http://emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo, apenas exponho um simples código para elucidar como deveria ser feito, isto é, como seria implementado um algoritmo para totalizar um plano de contas em uma linguagem de programação, a exemplo aqui do VBA, em casos que não é possível usar uma query ou um SGBD sem um suporte mais abrangente ao SQL a exemplo do ACCESS 2010.

'Um código bem básico para uma estrutura de três níveis escrito em 'VB para o Access 2010 
Option Compare Database

Sub centro_custo()
' funciona para uma estrutura de contas em 3 niveis
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim soma_n1, soma_n2, soma_n3 As Double
Dim tamanho_conta As Integer
Dim strSQL As String
' lista todas as contas cadastradas em centro_custo
Set rs1 = CurrentDb.OpenRecordset("SELECT id_centro_custo, descricao, tipo_conta FROM centro_custo ORDER BY id_centro_custo DESC;")
Do While Not rs1.EOF
   ' contas nivel 3
   ' todas contas analiticas, nessa estrutura as contas tem o nivel 3
   If (rs1("tipo_conta") = "A") Then
       ' processa o nivel 3 na query em strSQL
       strSQL = "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 WHERE cc.id_centro_custo =  '" & rs1("id_centro_custo") & "' GROUP BY cc.id_centro_custo, cc.descricao;"
       Set rs2 = CurrentDb.OpenRecordset(strSQL)
       If Not rs2.EOF Then
           soma_n3 = rs2("total_conta")
       Else
           soma_n3 = 0
       End If
       soma_n2 = soma_n2 + soma_n3
       Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n3
   Else

       tamanho_conta = Len(rs1("id_centro_custo"))
       ' contas nivel 2
       If tamanho_conta = 3 Then
           Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n2
           soma_n1 = soma_n1 + soma_n2
           soma_n2 = 0
       Else
           ' contas nivel 1
           If tamanho_conta = 1 Then
               Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n1
               soma_n1 = 0
           End If
       End If
   End If
   ' avanca proximo registro
   rs1.MoveNext
Loop

End Sub

' Informo ainda que os níveis podem ser configurados via matrizes 
' e código desenvolvido acima é procedural
' Recomendo definir uma mascara do plano de contas

Resultado do Código em VBA Acima Descrito


Mais uma vez espero ter ajudado!
APdSJC!

sexta-feira, 29 de novembro de 2013

Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle

Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle


Sabe quando um amigo lhe pede uma ajuda sobre como desenvolver uma determinada query e você não sabe como explicar, essa é a dica para quem já passou por esse tipo de problema chama-se SQL Fiddle
Muitas vezes não é possível testar uma determinada query por não ter o SGBDR instalado ou está sem uma conexão segura ao SGBDR, então a dica é usar o SQL Fiddle.
Nesse site http://sqlfiddle.com/ ainda existe a possibilidade de verificar como funciona a mesma query nos diversos SGBDRs e suas diversas versões, uma feature interessante para quem desenvolve sistemas multi-SGBDRs.

Segue a dica para trabalhar com SQL Fiddle.

Acessar o SQL Fiddle no seguinte site: http://sqlfiddle.com/ Na primeira coluna coloca-se os comandos DDL (de criação de estrutura, tabelas, views, etc).
Executar com o botão Run Schema.
Na segunda coluna coloca-se os comandos DML (insert, update, delete, selects).
Executar com o botão Run SQL.
Outra observação é que o SQL Fiddle não armazena o cache dos scripts, então há de se rodar toda a query de uma única vez.

Mais uma vez espero ter ajudado.

APdSJC!

quinta-feira, 1 de agosto de 2013

Converter várias linhas em uma única linha no SQL Server

Como converter várias linhas em uma única linha no SQL Server, separando por ponto e virgula ";".


USE tempdb;
-- DROP TABLE pessoa_fisica;
CREATE TABLE pessoa_fisica 
(
   id_pessoa_fisica int IDENTITY (1,1) PRIMARY KEY 
 , nome             varchar(100)       NOT NULL 
 , sexo             varchar(1)         NOT NULL 
); 

-- DROP TABLE email;
CREATE TABLE email 
(
   id_email         int IDENTITY (1,1) PRIMARY KEY 
 , email            varchar(100)       NOT NULL 
 , id_pessoa_fisica int                NOT NULL REFERENCES pessoa_fisica(id_pessoa_fisica) 
);  


-- inserindo na tabela pessoa_fisica
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Maria','F'); 
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Ieda','F'); 
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Ilma','F'); 
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Felipe','F'); 

-- listando todas as pessoas fisicas cadastradas 
SELECT * FROM pessoa_fisica;

-- inserindo na tabela email 

-- inserindo emails de Maria
INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria@exemplo.com', 1); 
INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria.rosa@exemplo.com', 1); 
INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria.silva@exemplo.com', 1); 

-- inserindo emails de Ieda
INSERT INTO email (email, id_pessoa_fisica) VALUES ('ieda@exemplo.com', 2); 
INSERT INTO email (email, id_pessoa_fisica) VALUES ('ieda.lima@exemplo.com', 2); 


-- inserindo emails de Felipe 
INSERT INTO email (email, id_pessoa_fisica) VALUES ('felipe@exemplo.com', 4); 
INSERT INTO email (email, id_pessoa_fisica) VALUES ('felipe.santos@exemplo.com', 4); 

-- listando todos os emails cadastrados
SELECT * FROM email;

-- listando emails de Maria 

-- listando emails de Maria em várias linhas 
-- exemplo 1.1

SELECT email + '; ' FROM email WHERE id_pessoa_fisica = 1;

-- listando emails de Maria em uma única linha 
-- exemplo 1.2  

SELECT stuff( (SELECT email + '; ' FROM email WHERE id_pessoa_fisica = 1  FOR XML PATH('')),1,0,'');

-- listando todos os emails cadastrados com todas as pessoas cadastradas, 
--  considerando vários emails em uma unica linha.
-- exemplo 2

SELECT pessoa_fisica.nome
     , stuff ( 
               (
                 SELECT email.email + '; ' 
                   FROM email 
                  WHERE email.id_pessoa_fisica = pessoa_fisica.id_pessoa_fisica  
                    FOR XML PATH('')
               ),1,0,''
             ) AS emails
  FROM pessoa_fisica
     ;



Mais uma vez espero ter ajudado ...

quarta-feira, 24 de julho de 2013

Remover caracteres especiais em campos do Postgres

Function scalar para Postgres, com objetivo de remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function em procedimentos de ETL, migração de dados ou em consultas que precisem remover esses tais caracteres da tabela ASCII.

Fica aqui o compartilhamento do conhecimento.

Abraços.

--
-- Nome Artefato/Programa..: usf_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) 
-- ........................: O Peregrino / http://emersonhermann.blogspot.com
-- Data Inicio ............: 09/10/2008
-- Data Atual..............: 24/07/2012
-- Versao..................: 0.02
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store function para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis 
-- OBS.....................: 
--
--habilitando linguagem plpgsql 
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; 
-- languages 
--SELECT * FROM pg_language;
-- Apaga function se existir 
DROP FUNCTION IF EXISTS usf_rm_special_chars (TEXT);
CREATE OR REPLACE FUNCTION usf_rm_special_chars (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo TEXT;
BEGIN
  -- Tabela ASCII
  -- Caracteres não imprimiveis, 33 ao total, menos o NUL ficam 32 
                                                    --  hex d abr ctl descricao  
                                                    --  00  0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata 
  stextonovo := translate(stexto, chr(1),'');       --  01  1 SOH ^A Start of Header - Início do cabeçalho
  stextonovo := translate(stextonovo, chr(2),'');   --  02  2 STX ^B Start of Text - Início do texto
  stextonovo := translate(stextonovo, chr(3),'');   --  03  3 ETX ^C End of Text - Fim do texto
  stextonovo := translate(stextonovo, chr(4),'');   --  04  4 EOT ^D End of Tape - Fim de fita
  stextonovo := translate(stextonovo, chr(5),'');   --  05  5 ENQ ^E Enquire - Interroga identidade do terminal
  stextonovo := translate(stextonovo, chr(6),'');   --  06  6 ACK ^F Acknowledge - Reconhecimento
  stextonovo := translate(stextonovo, chr(7),'');   --  07  7 BEL ^G Bell - Campainha
  stextonovo := translate(stextonovo, chr(8),'');   --  08  8 BS  ^H  Back-space - Espaço atrás
  stextonovo := translate(stextonovo, chr(9),'');   --  09  9 HT  ^I  Horizontal Tabulation - Tabulação horizontal
  stextonovo := translate(stextonovo, chr(10),'');  --  0A 10 LF  ^J Line-Feed - Alimenta linha
  stextonovo := translate(stextonovo, chr(11),'');  --  0B 11 VT  ^K  Vertical Tabulation - Tabulação vertical
  stextonovo := translate(stextonovo, chr(12),'');  --  0C 12 FF  ^L Form-Feed - Alimenta formulário
  stextonovo := translate(stextonovo, chr(13),'');  --  0D 13 CR  ^M Carriage-Return - Retorno do carro (enter)
  stextonovo := translate(stextonovo, chr(14),'');  --  0E 14 SO  ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.)
  stextonovo := translate(stextonovo, chr(15),'');  --  0F 15 SI  ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.)
  stextonovo := translate(stextonovo, chr(16),'');  --  10 16 DLE ^P Data-Link Escape
  stextonovo := translate(stextonovo, chr(17),'');  --  11 17 DC1 ^Q Device-Control 1
  stextonovo := translate(stextonovo, chr(18),'');  --  12 18 DC2 ^R Device-Control 2
  stextonovo := translate(stextonovo, chr(19),'');  --  13 19 DC3 ^S Device-Control 3
  stextonovo := translate(stextonovo, chr(20),'');  --  14 20 DC4 ^T Device-Control 4
  stextonovo := translate(stextonovo, chr(21),'');  --  15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento
  stextonovo := translate(stextonovo, chr(22),'');  --  16 22 SYN ^V Synchronous Idle
  stextonovo := translate(stextonovo, chr(23),'');  --  17 23 vETB^W  End-of-Transmission Block
  stextonovo := translate(stextonovo, chr(24),'');  --  18 24 CAN ^X Cancel
  stextonovo := translate(stextonovo, chr(25),'');  --  19 25 EM  ^Y  End-Of-Medium
  stextonovo := translate(stextonovo, chr(26),'');  --  1A 26 SUB ^Z Substitute
  stextonovo := translate(stextonovo, chr(27),'');  --  1B 27 ESC ^[ Escape
  stextonovo := translate(stextonovo, chr(28),'');  --  1C 28 FS  ^\  File Separator
  stextonovo := translate(stextonovo, chr(29),'');  --  1D 29 GS  ^] Group Separator
  stextonovo := translate(stextonovo, chr(30),'');  --  1E 30 RS  ^^  Record Separator
  stextonovo := translate(stextonovo, chr(31),'');  --  1F 31 US  ^_ Unit Separator
  stextonovo := translate(stextonovo, chr(127),''); --  7F127 DEL ^?  Delete

  RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;


Test 1

Test 2



-- string com caracteres especiais, formato 2 
SELECT 'SEM STRESS... ' || chr(1) || 'TECLA  ENTER: ' || chr(13) || ' TECLA ESC: ' || chr(27) || ' TESTE OK ' AS test3; 
-- string sem caracteres espciais usando a function, formato 2 
SELECT usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA  ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test4; 

-- verificando tamanho da string, 53 caracteres, incluindo 3 especiais 
SELECT length('SEM STRESS... ' || chr(1) || 'TECLA  ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test5; 
-- verificando tamanho da string, 50 caracteres, removido 3 especiais
SELECT length(usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA  ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ')) AS test6; 


terça-feira, 29 de janeiro de 2013

Desenvolvendo querys SQL para Razão e Balancete Contábil.

Desenvolvendo querys SQL para Razão e Balancete Contábil.

Em um artigo escrito neste blog no dia 06-09-2012 Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo conforme link http://www.emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html, mostrei como desenvolver uma query em uma estrutura de plano de contas ou centro de custo, dessa vez irei apresentar de forma prática, gradual e por exemplos de como desenvolver uma query para relatório de Razão, Razão Sumarizado e Balancete Contábil.

Nivel de Complexidade: Intermediário, Avançado

Quem desenvolve sabe como é complicado criar relatórios para contabilidade ou centro de custos, seja codificando em alguma linguagem ou mesmo tentando encurtar o tempo ou a pressão, usando algum gerador de relatórios, a exemplo do MS Report Service, SAP Crystal Reports, Script Case, etc.

As querys scripts mostradas nesse artigo foram desenvolvidas para os seguintes SGBDRs em ordem alfabética:

Firebird 2.5.1
Oracle 11g R2
Postgres 9.1
SQL Server 2012

A modelagem aqui apresentada não seguiu o rigor acadêmico, a exemplo de implementação de constraints, chaves primárias, etc, bem como os exemplos citados para area Contabil.

O objetivo principal deste artigo é mostrar como desenvolver querys para Sistemas Contabeis, houve uma simplificação com intuíto de facilitar o entendimento, entretanto, com os exemplos expostos é possivel adotar em qualquer modelo relacional.

Houve também uma preocupação em apresentar os scripts desenvolvidos nos SGBDRs citados no inicio deste documento.

Pode-se chegar aos mesmos resultados de uma forma mais performática e simples; estou apto a sugestões.

Seguem os exemplos em scripts SQL:

Criando as tabelas ... (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)

-- USE tempdb; -- Descomentar caso use SQL Server, recomendo criar um banco de teste para os outros SGBDRs.
--DROP TABLE plano_conta;
CREATE TABLE plano_conta
( 
   id_plano_conta   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 lancamento;
CREATE TABLE lancamento 
(
   id_lancamento    integer   PRIMARY KEY    -- id do lancamento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento
 , dt_lancamento    date                     -- data do lancamento 
 , numero_doc       varchar(40)              -- numero do documento a ser informado  
 , id_plano_conta   varchar(12)              -- chave estrangeira para a tabela plano_conta, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null
 , tipo_lancamento  varchar(1)               -- tipo de lancamento 'E' = Entrada 'S' = Saida 
 , historico        varchar(100)             -- historico do lancamento 
 , valor_lancamento numeric(15,2)            -- valor informado 
);

Povoando os tabelas...
-- Receitas 
 
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');
 
-- Despesas
 
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');
 
-- Vamos povoar a tabela lancamento: 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (1,'2012-07-03','0000084','1.2.2','E',NULL,10.55); 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (2,'2012-07-03','0000084','1.2.2','S',NULL,2.50); 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (3,'2012-07-01','0000021','1.1.2','E',NULL,50.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento,numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (4,'2012-07-01','0000042','1.2.2','E',NULL,100.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (5,'2012-07-04','0000084','1.2.2','E',NULL,160.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (6,'2012-07-04','0000084','1.2.2','S',NULL,80.00);
 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (7,'2012-07-04','0000142','2.2.1','S',NULL,40.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (8,'2012-07-07','0000210','2.2.2','S',NULL,80.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (9,'2012-07-13','0000242','2.2.2','S',NULL,20.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (10,'2012-07-13','0000284','2.2.1','S',NULL,15.00);



Vamos as querys relatórios...

Razão Detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012) 

   SELECT lan.id_lancamento
        , lan.dt_lancamento 
        , lan.numero_doc
        , lan.id_plano_conta
        , lan.tipo_lancamento
        , lan.historico 
        , 
          coalesce(
          (
          SELECT sum
                 ( 
                  CASE WHEN tipo_lancamento = 'E' THEN 
                            valor_lancamento
                       WHEN tipo_lancamento = 'S' THEN 
                            valor_lancamento * -1 
                       ELSE 
                            0.00
                  END
                 ) 
            FROM lancamento 
            WHERE dt_lancamento < lan.dt_lancamento 
              AND id_plano_conta  = lan.id_plano_conta 
          ),0)  AS saldo_inicial  
        , CASE WHEN tipo_lancamento = 'E' THEN 
                    valor_lancamento
               ELSE 
                    0.00 
          END AS entrada 
        , CASE WHEN tipo_lancamento = 'S' THEN 
                    valor_lancamento
               ELSE 
                    0.00 
          END AS saida 
        ,  
          coalesce(
          (
          SELECT sum
                 ( 
                  CASE WHEN tipo_lancamento = 'E' THEN 
                            valor_lancamento 
                       WHEN tipo_lancamento = 'S' THEN 
                            valor_lancamento * -1 
                       ELSE 
                            0.00
                  END
                 ) 
            FROM lancamento
           WHERE dt_lancamento <= lan.dt_lancamento 
             AND id_plano_conta  = lan.id_plano_conta 
          ),0)  AS saldo_final
     FROM lancamento AS lan
     JOIN plano_conta AS plc 
       ON plc.id_plano_conta  = lan.id_plano_conta
    WHERE lan.dt_lancamento >= '2012-07-04'
      AND lan.dt_lancamento <= '2012-07-14'
      AND lan.id_plano_conta  = '2.2.2'
 ORDER BY lan.dt_lancamento ASC 
        ;


Razão Sumarizado Por Plano de Conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao sumarizado por plano de conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)   
    SELECT x.id_plano_conta
         , pcx.descricao 
         , coalesce(
           (
            SELECT sum
                   ( 
                    CASE WHEN tipo_lancamento = 'E' THEN 
                              valor_lancamento
                         WHEN tipo_lancamento = 'S' THEN 
                              valor_lancamento * -1 
                         ELSE 
                              0.00
                     END
                   ) 
              FROM lancamento 
             WHERE dt_lancamento < '2012-07-04'
               AND id_plano_conta  = x.id_plano_conta 
           ),0)  AS saldo_inicial 
         , sum(x.entrada) AS entrada
         , sum(x.saida)   AS saida
         , coalesce(
           (
            SELECT sum
                   ( 
                    CASE WHEN tipo_lancamento = 'E' THEN 
                              valor_lancamento 
                         WHEN tipo_lancamento = 'S' THEN 
                              valor_lancamento * -1 
                         ELSE 
                              0.00
                    END
                   ) 
              FROM lancamento
             WHERE dt_lancamento <=  '2012-07-14'
               AND id_plano_conta  = x.id_plano_conta 
           ),0)  AS saldo_final
      FROM
         ( 
           SELECT lan.id_plano_conta
                , CASE WHEN tipo_lancamento = 'E' THEN 
                            valor_lancamento
                       ELSE 
                            0.00 
                  END AS entrada 
                , CASE WHEN tipo_lancamento = 'S' THEN 
                            valor_lancamento
                       ELSE 
                            0.00 
                  END AS saida 
             FROM lancamento AS lan
             JOIN plano_conta AS plc 
               ON plc.id_plano_conta  = lan.id_plano_conta
            WHERE lan.dt_lancamento >= '2012-07-04'
              AND lan.dt_lancamento <= '2012-07-14'
              AND lan.id_plano_conta  = '2.2.2'
         ) AS x
      JOIN plano_conta AS pcx
        ON pcx.id_plano_conta = x.id_plano_conta 
  GROUP BY x.id_plano_conta
         , pcx.descricao
  ORDER BY x.id_plano_conta ASC 
      ;


Balancete Contabil (Script para SQL Server 2012)
-- balancete contabil (Script para SQL Server 2012) 
 SELECT pcx.id_plano_conta
       , pcx.descricao 
       , sum(xx.saldo_inicial) AS saldo_inicial 
       , sum(xx.entrada)       AS entrada
       , sum(xx.saida)         AS saida 
       , sum(xx.saldo_final)   AS saldo_final 
    FROM
       (
        SELECT x.id_plano_conta
             , coalesce(
               (
                SELECT sum
                       ( 
                        CASE WHEN tipo_lancamento = 'E' THEN 
                                  valor_lancamento
                             WHEN tipo_lancamento = 'S' THEN 
                                  valor_lancamento * -1 
                             ELSE 
                                  0.00
                        END
                       ) 
                  FROM lancamento 
                 WHERE dt_lancamento < '2012-07-04'
                   AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_inicial 
             , sum(x.entrada) AS entrada
             , sum(x.saida)   AS saida
             , coalesce(
               (
                 SELECT sum
                        ( 
                         CASE WHEN tipo_lancamento = 'E' THEN 
                                   valor_lancamento 
                              WHEN tipo_lancamento = 'S' THEN 
                                   valor_lancamento * -1 
                              ELSE 
                                   0.00
                         END
                        ) 
                   FROM lancamento
                  WHERE dt_lancamento <=  '2012-07-14'
                    AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_final
          FROM
             ( 
               SELECT lan.id_plano_conta
                    , CASE WHEN lan.tipo_lancamento = 'E' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS entrada 
                    , CASE WHEN lan.tipo_lancamento = 'S' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS saida 
                 FROM lancamento AS lan
                WHERE lan.dt_lancamento >= '2012-07-04'
                  AND lan.dt_lancamento <= '2012-07-14'
                --AND lan.id_plano_conta  = '2.2.2'
             ) AS x
      GROUP BY x.id_plano_conta
       ) AS xx
    JOIN plano_conta pcx 
      ON xx.id_plano_conta LIKE pcx.id_plano_conta + '%'
GROUP BY pcx.id_plano_conta 
       , pcx.descricao 
ORDER BY pcx.id_plano_conta ASC
       ; 


Balancete Contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)
-- balancete contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)   
  SELECT pcx.id_plano_conta
       , pcx.descricao 
       , sum(xx.saldo_inicial) AS saldo_inicial 
       , sum(xx.entrada)       AS entrada
       , sum(xx.saida)         AS saida 
       , sum(xx.saldo_final)   AS saldo_final 
    FROM
       (
        SELECT x.id_plano_conta
             , coalesce(
               (
                SELECT sum
                       ( 
                        CASE WHEN tipo_lancamento = 'E' THEN 
                                  valor_lancamento
                             WHEN tipo_lancamento = 'S' THEN 
                                  valor_lancamento * -1 
                             ELSE 
                                  0.00
                        END
                       ) 
                  FROM lancamento 
                 WHERE dt_lancamento < '2012-07-04'
                   AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_inicial 
             , sum(x.entrada) AS entrada
             , sum(x.saida)   AS saida
             , coalesce(
               (
                 SELECT sum
                        ( 
                         CASE WHEN tipo_lancamento = 'E' THEN 
                                   valor_lancamento 
                              WHEN tipo_lancamento = 'S' THEN 
                                   valor_lancamento * -1 
                              ELSE 
                                   0.00
                         END
                        ) 
                   FROM lancamento
                  WHERE dt_lancamento <=  '2012-07-14'
                    AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_final
          FROM
             ( 
               SELECT lan.id_plano_conta
                    , CASE WHEN lan.tipo_lancamento = 'E' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS entrada 
                    , CASE WHEN lan.tipo_lancamento = 'S' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS saida 
                 FROM lancamento AS lan
                WHERE lan.dt_lancamento >= '2012-07-04'
                  AND lan.dt_lancamento <= '2012-07-14'
                --AND lan.id_plano_conta  = '2.2.2'
             ) AS x
      GROUP BY x.id_plano_conta
       ) AS xx
    JOIN plano_conta pcx 
      ON xx.id_plano_conta LIKE pcx.id_plano_conta || '%'
GROUP BY pcx.id_plano_conta 
       , pcx.descricao 
ORDER BY pcx.id_plano_conta ASC
       ; 


Figura 01 - Resultado das Querys

Para facilitar, caso haja interesse, recomendo criar views ou functions ou procedures das querys acimas, pois são demasiadamente extensas.

Mais uma vez espero ter ajudado.

APDSJ!

quarta-feira, 19 de setembro de 2012

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!

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

terça-feira, 28 de agosto de 2012

Storing and Retrieving Images from SQL Server using Store Procedures in Transact-SQL - Armazenando e Recuperando Imagens do SQL Server usando Store Procedures em Transact-SQL

In SQL Server does not have the capability to export a varbinary (BLOB) images directly via SQL, for the Operating System (Windows), just as import, export is done via SSRS or using any programming language, but it was developed a stored procedure called usp_lo_export, below, follows the solution to the problem in question.

No SQL Server não tem o recurso para exportar um varbinary (blob), imagens, diretamente via SQL, para o Sistema Operacional (Windows), apenas como importar, a exportação é feita via SSRS ou usando alguma linguagem de programação, mas para isso foi desenvolvido uma store procedure chamada usp_lo_export, abaixo, segue a solução para o problema em questão.

Configuration for SQL Server 2008/2012
-- 
-- Functionality over safety
-- Funcionalidade em detrimento da segurança

-- Routine Tested in SQL SERVER 2008 Enterprise and SQL SERVER 2012 Enterprise
-- Procedimento Testado no SQL SERVER 2008 Enterprise e SQL SERVER 2012 Enterprise

-- Configuration for SQL Server procedure call xp_cmdshell
-- Configuracao do SQL Server para chamada de procedure xp_cmdshell
USE master; 
GO

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- make dir c:\images
EXEC xp_cmdshell 'mkdir c:\images';

-- list dir c:\images 
EXEC xp_cmdshell 'dir c:\images\*.*';
Store Procedure usp_lo_export
USE master; -- change to your database - mudar para o seu bd
GO
-- Creating Stored Procedure
-- start procedure 
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[usp_lo_export]') AND type IN (N'P') )
 DROP PROCEDURE usp_lo_export;
GO
CREATE PROCEDURE usp_lo_export (@bdx varchar(65), @schemax varchar(65), @tablex varchar(65), @col_bin_imgx varchar(65), @pk_tablex varchar(65), @idx bigint, @file_namex varchar(255))
AS
-- Artifact Name/Program...: usp_lo_exportx.sql
-- Author..................: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com)
-- Date Start..............: 28/08/2012
-- Date Update.............: 08/12/2014
-- Version.................: 0.03
-- Compiler/Interpreter....: T-SQL (Transact SQL) 
-- OS......................: Windows
-- RDBMS...................: MS SQL Server 2008/2012
-- Kernel..................: Not informed! 
-- Porpuse - Finalidade....: Stored Procedure to export images (varbinary) to binary file database for SO - SP para exportar imagens (varbinary) para arquivo binário do banco de dados para o SO 
-- Notice .................: The parameters are: db, schema, table name, column type binary, primary key, primary key id and name of the binary file -
-- ........................: Os parametros são: bd, esquema, nome da tabela, coluna do tipo binaria, chave primária, id da chave primaria e nome do arquivo binario
--  
BEGIN
 SET NOCOUNT ON
 BEGIN TRY
   
  DECLARE @sql_stmt varchar(4000)
        , @sql_query  varchar(4000) 
        , @v_sql      varchar(4)
        , @echo_v_sql varchar(255)
        , @dir_bcp varchar(255) 
 
  -- example of using bcp into SQL Server 2008 to form Bcp.fmt file (set in Y last option)
  -- exemplo de uso de bcp em SQL Server 2008, para formar archive bcp.fmt (setar Y na ultima opcao) 
  -- bcp testdb.dbo.uvw_img out c:\images\test1.png -T 
  -- bcp.fmt created - ja criado 
  -- bcp testdb.dbo.uvw_img out c:\images\test1.png -T -f c:\images\bcp.fmt     
 
  -- model fmt file bcp (bcp.fmt) to import binary, SQL SERVER 2008 
  -- modelo de arquivo fmt do bcp (bcp.fmt) para importação binaria, SQL SERVER 2008 
  --10.0
  --1
  --1       SQLBINARY           0       0       ""   1     archive            ""
     
 
  -- generating configuration file
  -- gerando arquivo de configuracao 
  SET @v_sql      = SUBSTRING(CAST (SERVERPROPERTY('productversion') AS VARCHAR(max)),1,4) -- version sql server 
  SET @echo_v_sql = 'ECHO ' + @v_sql + ' > c:\images\conf_blob.fmt'
    
  EXEC xp_cmdshell @echo_v_sql, no_output
  EXEC xp_cmdshell 'ECHO 1 >> c:\images\conf_blob.fmt', no_output
  EXEC xp_cmdshell 'ECHO 1       SQLBINARY           0       0       ""   1     archive            "" >> c:\images\conf_blob.fmt', no_output
 
  IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
       DROP TABLE ##blobx
  END
  CREATE TABLE ##blobx
  (
       file_binx varbinary(max)
  )
   
  -- populating global temporary table with the table data, where the blob
  -- povoando tabela temporária global com os dados da tabela, onde se encontra o blob
  SET @sql_query = 'INSERT INTO tempdb.dbo.##blobx
                    SELECT ' + @col_bin_imgx  + '
                      FROM ' + @bdx + '.' + @schemax + '.' + @tablex + '
                     WHERE ' + @pk_tablex + ' = ' + ltrim(str(@idx)) 
  PRINT @sql_query
  EXEC (@sql_query)

            
  -- for use with dynamic sql xp_cmdshell SP - sql dinamico para uso com SP xp_cmdshell
  -- bcp tempdb.dbo.##blobx out c:\images\test1.png -T -f c:\images\bcp.fmt
  IF @v_sql = '10.0' BEGIN
       SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp"'
  END IF @v_sql = '11.0' BEGIN
       SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp"'
  END ELSE BEGIN
       SET @dir_bcp = 'bcp'
  END
   
  SET @sql_stmt = @dir_bcp + ' tempdb.dbo.##blobx out c:\images\' + ltrim(str(@idx)) + '_' + @file_namex + ' -T -f c:\images\conf_blob.fmt'
 
  PRINT 'Exporting binary ...'
  EXEC xp_cmdshell @sql_stmt, no_output
       
 END TRY
 
 BEGIN CATCH
  SELECT ERROR_NUMBER()  AS error_number
       , ERROR_SEVERITY()  AS error_severity
       , ERROR_STATE()  AS error_state
       , ERROR_PROCEDURE() AS error_procedure
       , ERROR_LINE()   AS error_line
       , ERROR_MESSAGE()  AS error_message;    
 END CATCH;
  
 -- cleaning dirt, eliminating temporary table - limpando sujeira, eliminando tabela temporaria
 IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
      DROP TABLE ##blobx
 END 
 -- cleaning, eliminating the bcp format file - limpando sugeira, eliminando arquivo de formatacao do bcp 
 EXEC xp_cmdshell 'DEL c:\images\conf_blob.fmt', no_output
 
 SET NOCOUNT ON
END; 
GO 
-- end procedure 
Working with stored procedure usp_lo_export
USE testdb; -- change to your database - mudar para o seu bd
GO

-- Creating Table Test
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[image]') AND type IN (N'U') )
 DROP TABLE image;
GO
CREATE TABLE image
(
   id_image int identity(1,1) NOT NULL  PRIMARY KEY
 , file_name nvarchar(1000) NOT NULL
 , archive varbinary(max) -- binary type, implemented in sql2005 sp2
);
GO

-- Only test - Apenas para teste
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[uvw_img]') AND type IN (N'V') )
 DROP VIEW uvw_img;
GO
CREATE VIEW uvw_img AS
SELECT TOP 1 archive FROM image
GO

-- If you need to test again - Caso precise fazer teste novamente
--TRUNCATE TABLE image;


-- Populating table with images - Povoando tabela com imagens
INSERT INTO image (file_name, archive) 
SELECT 'koala.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\koala.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'desert.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\desert.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'penguins.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\penguins.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'plan_test.xlsx', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Documents\plan_test.xlsx', SINGLE_BLOB) AS Image;
GO

-- Listing images
SELECT * FROM image;


-- Call the Store Procedure - Examples
-- Ex 01 - correct data reported
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',1,'koala.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',2,'desert.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',3,'penguins.jpg';

SELECT * FROM image;

-- Ex 02 - Table wrongly informed - Tabela informada errada 
EXEC master.dbo.usp_lo_export 'testdb','dbo', 'imagex','archive','id_image',3,'penguins.jpg';

-- Ex 03 - Correct data reported, electronically excel spreadsheet - Dados informados corretos, planilha eletronica do excel 
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',4,'plan_excel.xlsx';

-- Ex 04 - Use with anonymous block - Uso com bloco anônimo 
DECLARE @file_namex varchar(255)
      , @idx bigint 
SET @idx = 1
SELECT @file_namex = file_name FROM image WHERE id_image = @idx
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image', @idx, @file_namex; 
The peace of the Lord Jesus