Array
(
)

Stored Procedure Livro Caixa

Renan
   - 13 set 2017

Amigos, estou tentando montar uma pequena procedure que me traga os dados de uma tabela chamada movimentacao.
No formulario, vou selecionar o nome do banco e o periodo
A estrutura da tabela é:
#Código

CODIGO
COD_BANCO //nome banco. ex BB, bradesco, itau
DATA
LANCAMENTO 
TIPO //SE É CREDITO OU DEBITO.
VALOR

Achei essa procedure, mas não consegui entender como adaptar para pesquisar na minha tabela. Ao que parece ela pesquisa em 2 tabelas.
#Código
CREATE PROCEDURE SALDO_CONTA ( 
    conta varchar(15), 
    datainicial date) 
returns ( 
    data date, 
    contamovimentada varchar(15), 
    saldoanterior numeric(15,2), 
    credito numeric(15,2), 
    debito numeric(15,2), 
    saldoatual numeric(15,2)) 
as 
declare variable valorcredito numeric(15,2); 
declare variable valordebito numeric(15,2); 
declare variable datafinal date; 
begin 
  data = datainicial; 
  contamovimentada = conta; 
  -- datafinal será sempre a data atual do sistema -- 
  datafinal = current_date; 

  -- ************************************************************** 
  -- estas linhas estão fora do while para que sejam           -- * 
  -- executadas apenas 1 vez, aumentando a performance.        -- * 
  -- depois apenas faço a soma dos valores obtidos dia a dia   -- * 
  -- para sempre obter o saldo anterior correto                -- * 
  -- ************************************************************** 
  select coalesce(sum(valor),0)                                -- * 
  from credito                                                 -- * 
  where conta_c = :conta and data < :data into :valorcredito;  -- * 
                                                               -- * 
  select coalesce(sum(valor),0)                                -- * 
  from debito                                                  -- * 
  where conta_d = :conta and data < :data into :valordebito;   -- * 
                                                               -- * 
  -- obtenho o saldo anterior subtraindo o débito do crédito   -- * 
  saldoanterior = valorcredito - valordebito;                  -- * 
  -- ************************************************************** 

  -- verifico o movimento dia a dia, até o dia anterior à data atual 
  while (data <datafinal> 0) or (debito > 0))  then 
      suspend; 

    data = data + 1; 
    saldoanterior = saldoanterior + (credito - debito); 
  end 

  -- somo os créditos da data atual 
  select coalesce(sum(valor),0) 
  from credito 
  where conta_c = :conta and data = :data into :credito; 

  -- somo os débitos da data atual 
  select coalesce(sum(valor),0) 
  from debito 
  where conta_d = :conta and data = :data into :debito; 

  saldoatual = saldoanterior + (credito - debito); 

  -- exibe o registro com o saldo do dia atual 
  suspend; 
end

O resultado da procedure acima, seria algo assim
#Código
data       |conta     |saldo anterior |    debito |    credito |    saldo atual 
31/01/06    6                    0,00         0,00        60,00           60,00 
28/02/06    6                   60,00         0,00        30,00           90,00 
31/03/06    6                   90,00        40,00         0,00           50,00 
30/04/06    6                   50,00        60,00         0,00          -10,00 
19/01/07    6                  -10,00         0,00         0,00          -10,00

Alguma sugestão?

Gilis
|
MVP
Pontos: 130
    13 set 2017


Citação:
Amigos, estou tentando montar uma pequena procedure que me traga os dados de uma tabela chamada movimentacao.

No formulario, vou selecionar o nome do banco e o periodo

A estrutura da tabela é:

#Código

CODIGO
COD_BANCO //nome banco. ex BB, bradesco, itau
DATA
LANCAMENTO 
TIPO //SE É CREDITO OU DEBITO.
VALOR


Achei essa procedure, mas não consegui entender como adaptar para pesquisar na minha tabela. Ao que parece ela pesquisa em 2 tabelas.

#Código
CREATE PROCEDURE SALDO_CONTA ( 
    conta varchar(15), 
    datainicial date) 
returns ( 
    data date, 
    contamovimentada varchar(15), 
    saldoanterior numeric(15,2), 
    credito numeric(15,2), 
    debito numeric(15,2), 
    saldoatual numeric(15,2)) 
as 
declare variable valorcredito numeric(15,2); 
declare variable valordebito numeric(15,2); 
declare variable datafinal date; 
begin 
  data = datainicial; 
  contamovimentada = conta; 
  -- datafinal será sempre a data atual do sistema -- 
  datafinal = current_date; 

  -- ************************************************************** 
  -- estas linhas estão fora do while para que sejam           -- * 
  -- executadas apenas 1 vez, aumentando a performance.        -- * 
  -- depois apenas faço a soma dos valores obtidos dia a dia   -- * 
  -- para sempre obter o saldo anterior correto                -- * 
  -- ************************************************************** 
  select coalesce(sum(valor),0)                                -- * 
  from credito                                                 -- * 
  where conta_c = :conta and data < :data into :valorcredito;  -- * 
                                                               -- * 
  select coalesce(sum(valor),0)                                -- * 
  from debito                                                  -- * 
  where conta_d = :conta and data < :data into :valordebito;   -- * 
                                                               -- * 
  -- obtenho o saldo anterior subtraindo o débito do crédito   -- * 
  saldoanterior = valorcredito - valordebito;                  -- * 
  -- ************************************************************** 

  -- verifico o movimento dia a dia, até o dia anterior à data atual 
  while (data <datafinal> 0) or (debito > 0))  then 
      suspend; 

    data = data + 1; 
    saldoanterior = saldoanterior + (credito - debito); 
  end 

  -- somo os créditos da data atual 
  select coalesce(sum(valor),0) 
  from credito 
  where conta_c = :conta and data = :data into :credito; 

  -- somo os débitos da data atual 
  select coalesce(sum(valor),0) 
  from debito 
  where conta_d = :conta and data = :data into :debito; 

  saldoatual = saldoanterior + (credito - debito); 

  -- exibe o registro com o saldo do dia atual 
  suspend; 
end


O resultado da procedure acima, seria algo assim

#Código
data       |conta     |saldo anterior |    debito |    credito |    saldo atual 
31/01/06    6                    0,00         0,00        60,00           60,00 
28/02/06    6                   60,00         0,00        30,00           90,00 
31/03/06    6                   90,00        40,00         0,00           50,00 
30/04/06    6                   50,00        60,00         0,00          -10,00 
19/01/07    6                  -10,00         0,00         0,00          -10,00


Alguma sugestão?

Olá Renan, envie as tabelas, pode ser com dados fictícios, para exemplificar melhor.

Renan
   - 14 set 2017

Bom dia Gilis, como vai?

Segue um print da tabela com os dados, pode ser assim?

Clique na imagem para abrir em uma nova janela

Gilis
|
MVP
Pontos: 130
    14 set 2017

Olá Renan, perdão pela demora, verifiquei a Procedure e uma alternativa mais simples é criar uma única tabela com os campos (cod, cd_banco, data, lancamento, saldo, debito, credito, tipo) e criar uma VIEW conforme abaixo:

#Código

CREATE VIEW NomeDaView AS
SELECT cd_banco, 
            data,
            lancamento,
            saldo - debito - credito AS saldo_atual
FROM conta

Renan
   - 14 set 2017

Olá Gilis.

Para fazer da forma que você citou, eu teria que mudar muita coisa no meu sistema.
Não tem como fazer essa view usando a minha estrutura atual ou modificar a procedure?

Obrigado e desculpa qualquer coisa.

Gilis
|
MVP
Pontos: 130
    15 set 2017

Olá Renan, sim é possível, basta criar a VIEW utilizando FULL JOIN que é a consulta com todos os dados em duas tabelas ou mais, veja o exemplo abaixo:

#Código

CREATE VIEW nome_da_view AS
SELECT data, cod_banco, saldo AS saldo_anterior, debito, credito, saldo-credito-debito AS saldo_atual FROM movimentacao
FULL JOIN conta
ON conta.cod = movimentacao.cod 

O resultado será os dados dos campos conforme a ordem: data, cod_banco, saldo_anterior, debito, credito, saldo_atual.

Obs: para executar a VIEW deve usar o script SELECT * FROM nome_da_view



Espero ter ajudado e bons estudos.

Renan
   - 15 set 2017

Bom dia Gilis.

Obrigado pela dica.
Encontrei erros ao tentar cria-la, via ibexpert.

Usei este comando:

#Código

CREATE VIEW nome_da_view AS
SELECT data, cod_banco, saldo AS saldo_anterior, debito, credito, saldo-credito-debito AS saldo_atual FROM movimentacao
FULL JOIN BANCOS
ON BANCOS.codIGO = movimentacao.cod_banco


O erro é que a coluna debito e credito, são desconhecidas, pois elas não existem na minha tabela de movimentação.

Allan Monteiro
|
MVP
Pontos: 400
    15 set 2017

Experimente colocar 'alias' antes dos campos, como não sei de qual tabela são os campos do seu select, não fiz a query.
Mas ficaria mais ou menos assim:

CREATE VIEW nome_da_view AS
SELECT
M.DATA,
b.cod_banco,
FROM movimentacao M
FULL JOIN bancos b
ON b.codIGO = m.cod_banco;

ou pode colocar o próprio nome da tabela antes dos campos, ficando assim:


CREATE VIEW nome_da_view AS
SELECT
movimentacao.DATA,
bancos .cod_banco,
FROM movimentacao
FULL JOIN bancos
ON bancos.codIGO = movimentacao.cod_banco;

Renan
   - 15 set 2017

A tabela é aquela da imagem que postei acima. Vou add ela aqui.

Clique na imagem para abrir em uma nova janela

Lanço todos os valores no mesmo campo e identifico se é credito ou debito pelo campo TIPO.

Renan
   - 18 set 2017

Fiz uma alteração e está rodando na minha estrutura, só restaram 2 problemas.

1- adicionar a data final, pois está usando a data atual.
2- Não agrupar registros do mesmo dia, mas sim lançar um por um. No modelo atual, se tenho 5 creditos de 1000,00, a procedure cria apenas 1 registro de 5.000,00. Eu desejo mostrar os 5 itens.

Veja como está:

#Código

create or alter procedure SALDO_CONTA ( 
    CONTA integer, 
    DATAINICIAL date) 
returns ( 
    DATA date, 
    CONTAMOVIMENTADA integer, 
    SALDOANTERIOR numeric(15,2), 
    CREDITO numeric(15,2), 
    DEBITO numeric(15,2), 
    SALDOATUAL numeric(15,2)) 
as 
declare variable VALORCREDITO numeric(15,2); 
declare variable VALORDEBITO numeric(15,2); 
declare variable DATAFINAL date; 
begin 
  data = datainicial; 
  contamovimentada = conta; 
  -- datafinal será sempre a data atual do sistema -- 
  datafinal = current_date; 

  -- ************************************************************** 
  -- estas linhas estão fora do while para que sejam           -- * 
  -- executadas apenas 1 vez, aumentando a performance.        -- * 
  -- depois apenas faço a soma dos valores obtidos dia a dia   -- * 
  -- para sempre obter o saldo anterior correto                -- * 
  -- ************************************************************** 
  select coalesce(sum(valor),0)                                -- * 
  from movimentacao                                                 -- * 
  where tipo = 'C' and cod_banco = :conta and data < :data into :valorcredito;  -- * 
                                                               -- * 
  select coalesce(sum(valor),0)                                -- * 
  from movimentacao                                                  -- * 
  where tipo = 'D' and cod_banco = :conta and data < :data into :valordebito;   -- * 
                                                               -- * 
  -- obtenho o saldo anterior subtraindo o débito do crédito   -- * 
  saldoanterior = valorcredito - valordebito;                  -- * 
  -- ************************************************************** 

  -- verifico o movimento dia a dia, até o dia anterior à data atual 
  while (data < datafinal) do 
  begin 
    -- somo os créditos do dia 
    select coalesce(sum(valor),0) 
    from movimentacao 
    where tipo = 'C' and cod_banco = :conta and data = :data into :credito; 

    -- somo os débitos do dia 
    select coalesce(sum(valor),0) 
    from movimentacao 
    where tipo = 'D' and cod_banco = :conta and data = :data into :debito; 

    saldoatual = saldoanterior + (credito - debito); 

    -- se houve algum movimento no dia, exibo o registro 
    if ((credito > 0) or (debito > 0))  then 
      suspend; 

    data = data + 1; 
    saldoanterior = saldoanterior + (credito - debito); 
  end 

  -- somo os créditos da data atual 
  select coalesce(sum(valor),0) 
  from movimentacao 
  where tipo = 'C' and cod_banco = :conta and data = :data into :credito; 

  -- somo os débitos da data atual 
  select coalesce(sum(valor),0) 
  from movimentacao 
  where tipo = 'D' and cod_banco = :conta and data = :data into :debito; 

  saldoatual = saldoanterior + (credito - debito); 

  -- exibe o registro com o saldo do dia atual 
  suspend; 
end