Recalcular saldo bancário

[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.

[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

[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.

[JoséQuintas]

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.

[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

[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

[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

[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

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

[JoséQuintas]

Ainda estou preso nisso pra eliminar o jpbancario.dbf.
Ainda tentando encontrar alternativa diferente do dbf.

[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.

[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 (9.19 KiB) Exibido 4726 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.

[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.

[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.

[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.