PC Toledo

Em homenagem a Paulo Cesar Toledo
https://pctoledo.org/

Recalcular saldo bancário

https://pctoledo.org/viewtopic.php?t=24425

Página 1 de 2

Recalcular saldo bancário

Enviado: 06 Jul 2020 19:34
por JoséQuintas
Ainda não sei como fazer.

Necessidade: recalcular o saldo bancário, sendo que o saldo é gravado em cada lançamento, e pode haver mais de uma conta

Se dá pra fazer renumeração de código, provavelmente também dá pra recalcular saldo.

Só não sei como fazer ainda.

Por comando, não por stored procedure !

Se alguém souber e puder mostrar, agradeço.

Recalcular saldo bancário

Enviado: 06 Jul 2020 19:40
por JoséQuintas
Uia, mais fácil que eu pensava... digo sobre encontrar, e não sobre fazer... kkkkk

https://www.devmedia.com.br/forum/saldo ... sql/597158

Ainda vou complicar mais que isso, porque vou querer mostrar o saldo somente do final de cada dia.

Agora mãos à obra

Recalcular saldo bancário

Enviado: 06 Jul 2020 19:52
por JoséQuintas
Esse não resolve, seria este outro.

https://kb.elipse.com.br/criando-uma-so ... -consulta/

Código: Selecionar todos

SELECT t1.E3TimeStamp,t1.Campo, SUM(T2.Campo) as Soma
FROM Somatorio AS t1
INNER JOIN Somatorio AS t2 on t1.E3TimeStamp >= t2.E3TimeStamp
GROUP BY  t1.E3TimeStamp,t1.Campo
ORDER BY t1.E3TimeStamp  ASC
agora é tentar entender.

Mas esse relacionamento é novidade pra mim, e abre muitas possibilidades !!!
Não é um simples relacionamento, é uma espécie de filtro.

Recalcular saldo bancário

Enviado: 06 Jul 2020 20:31
por JoséQuintas
teste.png
Deu certinho, até calculando separado contas/aplicações.
Notem que o saldo gravado na tabela bate com o saldo calculado com o comando.
O problema é que demorou 42 segundos, e isso pra servidor é demorado demais.

Depois vou testar com intervalo de data, pra ver se fica melhor, mas à primeira vista não serve por causa da demora.

Recalcular saldo bancário

Enviado: 07 Jul 2020 08:36
por JoséQuintas
Assim faz rápido, mas não separou por conta/áplic

Código: Selecionar todos

SET @saldo = 0;
SET @conta = 'x';
SET @aplic = 'x';
SELECT bavalor, basaldo, @saldo := if( baconta = @conta AND baaplic = @baaplic, 0, @saldo + bavalor ), @conta := baconta, @aplic := baaplic
 FROM jpbancario
 ORDER BY baconta, baaplic, badatban, badatemi, idbancario

Recalcular saldo bancário

Enviado: 08 Jul 2020 18:49
por Poka
Boa noite

Quintas, sempre vai considerar todos os lançamentos da conta para achar o saldo?
não vai pegar o saldo do dia anterior + cred - deb do dia para achar o saldo do dia atual?

Poka

Recalcular saldo bancário

Enviado: 08 Jul 2020 20:13
por JoséQuintas
Ainda fazendo testes.
Tem coisas a considerar nos testes anteriores:

1 - Fiz sem índice no teste
2 - Mesmo com índice, o problema é o concat() que obriga mais processamento, e não permite aproveitamento de índice
3 - Se for possível deixar gravado, talvez dê pra otimizar, e fazer só a partir de uma data alterada, e só numa conta/aplicação, o que deixaria tudo rápido e salvo

Enquanto vou pensando nisso, vou alterando outras coisas .... rs

Recalcular saldo bancário

Enviado: 19 Jul 2020 18:17
por JoséQuintas
Fui usar e deu erro, mas no HeidiSQL funciona.
Tenho que mandar os comandos separados no aplicativo?

Código: Selecionar todos

SET @SOMA = 0;
SELECT BADATBAN, BADATEMI, BARESUMO, BAHIST,
IF( BAVALOR > 0, BAVALOR, 0 ) AS ENTRADA,
IF( BAVALOR < 0, BAVALOR, 0 ) AS SAIDA,
@SOMA := @SOMA + BAVALOR AS SALDO
FROM JPBANCARIO
WHERE BAVALOR <> 0
ORDER BY BADATBAN, BADATEMI, IDBANCARIO

Recalcular saldo bancário

Enviado: 19 Jul 2020 18:20
por JoséQuintas
Ok, assim funcionou, um comando de cada vez.

Código: Selecionar todos

   WITH OBJECT cnSQL
      :cSQL := "SET @SOMA = 0"
      :ExecuteCmd()
      :cSQL := "SELECT BADATBAN, BADATEMI, BARESUMO, BAHIST, " + ;
         " IF( BAVALOR > 0, BAVALOR, 0 ) AS ENTRADA," + ;
         " IF( BAVALOR < 0, BAVALOR, 0 ) AS SAIDA," + ;
         " @SOMA := @SOMA + BAVALOR AS SALDO" + ;
         " FROM JPBANCARIO" + ;
         " WHERE BAVALOR <> 0"  + ;
         " ORDER BY BADATBAN, BADATEMI, IDBANCARIO"
      :Execute()
Me chamou a atenção, porque em certos casos, ao invés de STORED PROCEDURE, dá pra usar esse esquema !!!!

Recalcular saldo bancário

Enviado: 20 Jul 2020 21:41
por JoséQuintas
Ainda estou preso nisso pra eliminar o jpbancario.dbf.
Ainda tentando encontrar alternativa diferente do dbf.

Agilizar rotina de recálculo

Enviado: 21 Jul 2020 15:45
por JoséQuintas
Esta rotina fica ótima... mas só se já estiver tudo calculado.
Alguma idéia pra agilizar?

Código: Selecionar todos

   Mensagem( "Recalculando" )
   WITH OBJECT cnSQL
      :cSQL := "SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI, BAVALOR," + ;
         " BASALDO, BAIMPSLD, IF( BASALDO < 0, 2, 1 ) AS ORDEM" + ;
         " FROM JPBANCARIO" + ;
         " ORDER BY BACONTA, BAAPLIC, BADATBAN, BADATEMI, ORDEM, IDBANCARIO"
      :Execute()
      GrafTempo( "Fase1" )
      DO WHILE nKey != K_ESC .AND. ! :Eof()
         cConta := :String( "BACONTA" )
         cAplic := :String( "BAAPLIC" )
         nSaldo := 0
         DO WHILE nKey != K_ESC .AND. cConta == :String( "BACONTA" ) .AND. cAplic == :String( "BAAPLIC" ) .AND. ! :Eof()
            GrafTempo( :AbsolutePosition(), :RecordCount() )
            nKey := Inkey()
            nSaldo += :Number( "BAVALOR" )
            IF Str( :Number( "BASALDO" ), 16, 2 ) != Str( nSaldo, 16, 2 )
               :QueryCreate()
               :QueryAdd( "BASALDO", nSaldo )
               Encontra( StrZero( :Number( "IDBANCARIO" ), 6 ), "jpbancario", "primary" )
               jpbancario->( :DBFQueryExecuteUpdate() )
               :QueryExecuteUpdate( "JPBANCARIO", "IDBANCARIO = " + NumberSQL( :Number( "IDBANCARIO" ) ) )
            ENDIF
            :MoveNext()
         ENDDO
      ENDDO
      :CloseRecordset()
      :cSQL := "SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI, BAVALOR, BASALDO, BAIMPSLD," + ;
         " IF( BASALDO < 0, 2, 1 ) AS ORDEM" + ;
         " FROM JPBANCARIO" + ;
         " ORDER BY BACONTA DESC, BAAPLIC DESC, BADATBAN DESC, BADATEMI DESC, ORDEM DESC, IDBANCARIO DESC"
      :Execute()
      GrafTempo( "Fase 2" )
      dBanco   := Ctod("")
      dEmissao := Ctod("")
      cConta   := :String( "BACONTA" )
      cAplic   := :String( "BAAPLIC" )
      DO WHILE nKey != K_ESC .AND. ! :Eof()
         GrafTempo( :AbsolutePosition(), :RecordCount() )
         nKey := Inkey()
         IF cConta != :String( "BACONTA" ) .OR. ;
            cAplic != :String( "BAAPLIC" ) .OR. ;
            dBanco != :Date( "BADATBAN" ) .OR. ;
            ( dBanco = Stod( "29991231" ) .AND. dEmissao != :Date( "BADATEMI" ) )
            cImpSld := "S"
         ELSE
            cImpSld := "N"
         ENDIF
         IF :Number( "BAVALOR" ) == 0
            cImpSld := "N"
         ENDIF
         IF :String( "BAIMPSLD" ) != cImpSld
            :QueryCreate()
            :QueryAdd( "BAIMPSLD", cImpSld )
            jpbancario->( Encontra( StrZero( :Number( "IDBANCARIO" ), 6 ), "jpbancario", "primary" ) )
            jpbancario->( :DBFQueryExecuteUpdate() )
            :QueryExecuteUpdate( "JPBANCARIO", "IDBANCARIO = " + NumberSQL( :Number( "IDBANCARIO" ) ) )
         ENDIF
         cConta   := :String( "BACONTA" )
         cAplic   := :String( "BAAPLIC" )
         dBanco   := :Date( "BADATBAN" )
         dEmissao := :Date( "BADATEMI" )
         :MoveNext()
      ENDDO
      :CloseRecordset()
   ENDWITH

   RETURN .T.
Parte 1:

Coloca na ordem, e recalcula saldo nessa ordem, cada conta/aplicação é calculada individualmente

Parte 2:

Apenas marca a última data pra mostrar saldo

Data do Banco = o que já caiu no banco, então mostra o saldo do final desse dia
Data de Emissão = a data do evento, que pode ou não ter caído no banco. Acaba sendo usada quando ainda não caiu no banco
A "data em branco" do banco é 31/12/2999, uso isso pra ela ficar no final da fila
E valor ZERO, uso pra ter o título da conta
&nbsp/ /&nbsp&nbsp/ /&nbsp&nbspCONTA X&nbsp&nbsp0&nbsp&nbsp0&nbsp
&nbsp01.01.1980&nbsp&nbsp01.01.1980&nbsp&nbspsaldo anterior&nbsp&nbsp2.000&nbsp&nbsp2.000&nbsp
&nbsp20.07.2020&nbsp&nbsp19.07.2020&nbsp&nbspcheque emitido1&nbsp&nbsp500&nbsp&nbsp&nbsp
&nbsp20.07.2020&nbsp&nbsp20.07.2020&nbsp&nbspcheque emitido2&nbsp&nbsp500&nbsp&nbsp1.000&nbsp
&nbsp31.12.2999&nbsp&nbsp21.07.2020&nbsp&nbspcheque emitido3&nbsp&nbsp500&nbsp&nbsp500&nbsp
Lógico no tbrowse ajusta pra espaço em branco o que não interessa.

Recalcular saldo bancário

Enviado: 21 Jul 2020 16:49
por JoséQuintas
EUREKA !!!!
Assim como no Harbour, existe o = e o :=

Código: Selecionar todos

FUNCTION BARecalcula() // , dDataInicial, m_RecGeral )

   LOCAL aContaList := {}, oConta, nInicio, nFinal
   LOCAL cnSQL := ADOClass():New( AppConexao() )

   Mensagem( "Recalculando" )
   nInicio := hb_MilliSeconds()
   WITH OBJECT cnSQL
      :cSQL := "SELECT DISTINCT BACONTA, BAAPLIC FROM JPBANCARIO"
      :Execute()
      DO WHILE ! :Eof()
         AAdd( aContaList, { :String( "BACONTA" ), :String( "BAAPLIC" ) } )
         :MoveNext()
      ENDDO
      FOR EACH oConta IN aContaList
         :ExecuteCmd( "SET @SOMA = 0" )
         :ExecuteCmd( "UPDATE JPBANCARIO" + ;
            " INNER JOIN" + ;
               "( SELECT IDBANCARIO, BADATBAN, BADATEMI, BAVALOR," + ;
               " BASALDO, IF( BAVALOR < 0, 2, 1 ) AS ORDEM, @SOMA := @SOMA + BAVALOR AS SALDO" + ;
               " FROM JPBANCARIO" + ;
               " WHERE BACONTA = " + StringSQL( oConta[ 1 ] ) + ;
               " AND BAAPLIC = " + StringSQL( oConta[2 ] ) + ;
               " ORDER BY BACONTA, BAAPLIC, BADATBAN, BADATEMI, ORDEM, IDBANCARIO ) AS A" + ;
               " ON JPBANCARIO.IDBANCARIO = A.IDBANCARIO" + ;
            " SET JPBANCARIO.BASALDO = A.SALDO" + ;
            " WHERE JPBANCARIO.BACONTA = " + StringSQL( oConta[ 1 ] ) + " AND JPBANCARIO.BASALDO <> A.SALDO" )
      NEXT
      :ExecuteCmd( "SET @SOMA = NULL" )
      :ExecuteCmd( "SET @CONTA = NULL" )
      :ExecuteCmd( "SET @APLIC = NULL" )
      :ExecuteCmd( "SET @DATBAN = NULL" )
      :ExecuteCmd( "SET @DATEMI = NULL" )
      :ExecuteCmd( "UPDATE JPBANCARIO" + ;
         " INNER JOIN" + ;
            "( SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI, BAVALOR, BAIMPSLD," + ;
            " IF( BAVALOR < 0, 2, 1 ) AS ORDEM, @C = BACONTA AS CONTA, @D = BAAPLIC AS APLIC," + ;
            " @E = BADATBAN AS DATBAN, @F = BADATEMI AS DATEMI," + ;
            " IF( BACONTA <> @CONTA OR BAAPLIC <> @APLIC OR BADATBAN <> @DATBAN OR " + ;
            " ( BADATEMI = '2999-12-31' AND BADATEMI <> @DATEMI ), 'N', 'S' ) AS IMPSLD" + ;
            " FROM JPBANCARIO" + ;
            " ORDER BY BACONTA DESC, BAAPLIC DESC, BADATBAN DESC, BADATEMI DESC, ORDEM DESC, " + ;
            " IDBANCARIO DESC ) AS A" + ;
            " ON JPBANCARIO.IDBANCARIO = A.IDBANCARIO" + ;
         " SET JPBANCARIO.BAIMPSLD = A.IMPSLD" + ;
         " WHERE JPBANCARIO.BAIMPSLD <> A.IMPSLD" )
      :ExecuteCmd( "SET @CONTA = NULL" )
      :ExecuteCmd( "SET @APLIC = NULL" )
      :ExecuteCmd( "SET @DATBAN = NULL" )
      :ExecuteCmd( "SET @DATEMI = NULL" )
      :CloseRecordset()
   ENDWITH
   nFinal := hb_MilliSeconds()
   MsgExclamation( Str( nFinal - nInicio ) )

   RETURN .T.
Nota: usando a recém descoberta de criar variáveis.
bancario.png
bancario.png (9.19 KiB) Exibido 4732 vezes
140 milésimos de segundo pra atualizar tudo.

Ainda precisa otimizar, porque com 60.000 registros tá demorando 2 segundos.
Pra recálculo geral pode até estar bom, mas pra recalcular a cada lançamento, isso é demorado.

Recalcular saldo bancário

Enviado: 21 Jul 2020 17:59
por JoséQuintas
Aproveitar pra chamar a atenção:

Este é um caso muito interessante, em vários sentidos.

- Comparando com DBF... extremamente mais rápido, até mesmo comparando com DBF LOCAL

- Fazendo pelo MySQL, não tive o problema de ponto flutuante que aconteceu no Harbour

- Fazendo no MySQL, no estilo MySQL, ficou mais rápido do que usando estilo DBF
Não basta mudar de DBF pra SQL, precisa aproveitar os recursos disponíveis e nenhum componente/LIB vai fazer isso

- Mas e agora? atualizar DBF e MySQL ao mesmo tempo?
Ao contrário de todas as outras tabelas, parece que pra esta o melhor é acabar com a gravação simultânea

- Que o aplicativo vai ficar em MySQL eu já sei. O que mais está me empolgando é que estou aprendendo coisa nova a cada dia, igual ao que acontecia no começo da programação.

Recalcular saldo bancário

Enviado: 22 Jul 2020 02:16
por JoséQuintas
Na falta de CTE no MySQL 5... tô inventando CTE via código fonte.....

Define tabela A

Código: Selecionar todos

      cSQLA := " SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI," + ;
         " IF( BAVALOR < 0, 2, 1 ) AS ENTSAI, @A := @A + 1 AS ORDEM" + ;
         " FROM JPBANCARIO" + ;
         " ORDER BY BACONTA, BAAPLIC, BADATBAN, ENTSAI, BADATEMI, IDBANCARIO"
Define tabela B

Código: Selecionar todos

      cSQLB := " SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI," + ;
         " IF( BAVALOR < 0, 2, 1 ) AS ENTSAI, @B := @B + 1 AS ORDEM" + ;
         " FROM JPBANCARIO" + ;
         " ORDER BY BACONTA, BAAPLIC, BADATBAN, ENTSAI, BADATEMI, IDBANCARIO"
Define tabela C, usando dados da tabela B

Código: Selecionar todos

      cSQLC := " SELECT BACONTA, BAAPLIC, BADATBAN, MAX( ORDEM ) AS MAXIMO" + ;
         " FROM ( " + cSQLB + " ) AS B" + ;
         " GROUP BY BACONTA, BAAPLIC, BADATBAN"
Define tabela D, usando dados das tabelas A e C

Código: Selecionar todos

      cSQLD := " SELECT IDBANCARIO FROM" + ;
         " ( " + cSQLA + " ) AS A" + ;
         " INNER JOIN" + ;
         " ( " + cSQLC + " ) AS C" + ;
         " ON A.ORDEM = C.MAXIMO"
Atualiza bancário, usando dados da tabela D

Código: Selecionar todos

      :ExecuteCmd( " UPDATE JPBANCARIO" + ;
         " LEFT JOIN ( " + cSQLD + " ) AS D" + ;
         " ON JPBANCARIO.IDBANCARIO = D.IDBANCARIO" + ;
         " SET BAIMPSLD = IF( JPBANCARIO.IDBANCARIO = D.IDBANCARIO, 'S', 'N' )" + ;
         " WHERE BAIMPSLD <> IF( JPBANCARIO.IDBANCARIO = D.IDBANCARIO, 'S', 'N' )" )
Ainda não está do jeito que quero.
Queria a parte pendente mostrando saldo pela outra data, mas qualquer tentativa estraga o resto.

Pra quem não percebeu, é tudo um único comando.
Apenas no fonte está dividido, pra facilitar a montagem.

Recalcular saldo bancário

Enviado: 22 Jul 2020 13:08
por JoséQuintas
Curiosidade: o comando executado

Código: Selecionar todos

UPDATE JPBANCARIO 
LEFT JOIN 
   (  SELECT IDBANCARIO FROM 
      (  SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI, IF( BAVALOR < 0, 2, 1 ) AS ENTSAI, @A := @A + 1 AS ORDEM 
         FROM JPBANCARIO 
        ORDER BY BACONTA, BAAPLIC, BADATBAN, ENTSAI, BADATEMI, IDBANCARIO 
      ) AS A 
      INNER JOIN 
         (  SELECT BACONTA, BAAPLIC, BADATBAN, MAX( ORDEM ) AS MAXIMO FROM 
             (  SELECT BACONTA, BAAPLIC, IDBANCARIO, BADATBAN, BADATEMI, IF( BAVALOR < 0, 2, 1 ) AS ENTSAI, @B := @B + 1 AS ORDEM 
                FROM JPBANCARIO 
                ORDER BY BACONTA, BAAPLIC, BADATBAN, ENTSAI, BADATEMI, IDBANCARIO 
            ) AS B 
       GROUP BY BACONTA, BAAPLIC, BADATBAN ) AS C ON A.ORDEM = C.MAXIMO 
   ) AS D ON JPBANCARIO.IDBANCARIO = D.IDBANCARIO 
SET BAIMPSLD = IF( JPBANCARIO.IDBANCARIO = D.IDBANCARIO, 'S', 'N' ) 
WHERE BAIMPSLD <> IF( JPBANCARIO.IDBANCARIO = D.IDBANCARIO, 'S', 'N' )
Difícil conferir isso.
No MySQL 8 criaram o recurso de CTE, pra fazer algo parecido com o que fiz via fonte Harbour.
Na falta do MySQL 8, resta isso que mostrei no post anterior, dividindo em blocos.

Estão aí duas coisas que comento sempre:

- O fonte é pra NÓS humanos, não para o computador. Dá pra deixar o fonte mais "humano".

- As novidades em linguagem de programação são pra deixar tudo mais "humano".
A gente sempre considerou apenas como facilidade.... mas na prática, é porque fica mais "humano", de um jeito que o ser humano entende mais fácil.
Todos os horários são UTC-03:00
Página 1 de 2

Powered by phpBB® Forum Software © phpBB Limited

Traduzido por: Suporte phpBB