PC Toledo

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

Atualizar estoque conforme lançamento

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

Página 1 de 1

Atualizar estoque conforme lançamento

Enviado: 26 Dez 2019 22:52
por JoséQuintas
Ainda vou começar a pesquisar, imagino que será usando CASE

Movimento de estoque: pode ser estiplan: 1=entrada, 2=saída
Número de depósito: de 1 a 9
Saldo no estoque: 1 a 9

No momento o objetivo é encontrar um comando SQL pra fazer isso, atualizar o saldo de 1 a 9, conforme o depósito e o entrada/saída.
Será usado o lançamento escolhido e/ou tudo (pra um recálculo, por exemplo)

Imagino algo neste estilo, mas ainda vou pesquisar, porque é atualizar um com base em outro, falta relacionamento ou algo parecido.
Vai que alguém já sabe, e puder ajudar....

Código: Selecionar todos

UPDATE JPITEM SET 
   CASE ESNUMDEP = '2' THEN SET JPITEM.IEQTD2 = JPITEM.IEQTD1 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   CASE ESNUMDEP = '3' THEN SET JPITEM.IEQTD3 = JPITEM.IEQTD3 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   CASE ESNUMDEP = '4' THEN SET JPITEM.IEQTD4 = JPITEM.IEQTD4 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   CASE ESNUMDEP = '5' THEN SET JPITEM.IEQTD5 = JPITEM.IEQTD5 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )   
   CASE ESNUMDEP = '6' THEN SET JPITEM.IEQTD6 = JPITEM.IEQTD6 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   CASE ESNUMDEP = '7' THEN SET JPITEM.IEQTD7 = JPITEM.IEQTD7 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   CASE ESNUMDEP = '8' THEN SET JPITEM.IEQTD8 = JPITEM.IEQTD8 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   CASE ESNUMDEP = '9' THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD9 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   ELSE                                 SET JPITEM.IEQTD1 = JPITEM.IEQTD1 + IF( ESTIPLAN='2', JPESTOQUE.ESQTDE, -JPESTOQUE.ESQTDE )
   END
   WHERE IDESTOQUE=  x

Atualizar estoque conforme lançamento

Enviado: 27 Dez 2019 01:50
por JoséQuintas
Na prática não consegui fazer nenhuma atualização de uma tabela com base em outra, nem mesmo a mais simples.
Ainda não consegui fazer funcionar nenhum exemplo da internet.
Se alguém puder passar as dicas iniciais, seria útil.

MySQL

Atualizar estoque conforme lançamento

Enviado: 27 Dez 2019 10:25
por JoséQuintas
Não é o que eu queria, porque atualiza campos que não precisava, mas aceitou.

Código: Selecionar todos

UPDATE JPITEM SET IEQTD1=0,IEQTD2=0,IEQTD3=0,IEQTD4=0,IEQTD5=0,IEQTD6=0,
IEQTD7=0,IEQTD8=0,IEQTD9=0;
update jpitem, jpestoque 
SET jpitem.IEQTD1 = jpitem.ieqtd1 + 
IF( JPESTOQUE.ESNUMDEP='1',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD2 = jpitem.ieqtd2 + 
IF( JPESTOQUE.ESNUMDEP='2',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD3 = jpitem.ieqtd3 + 
IF( JPESTOQUE.ESNUMDEP='3',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD4 = jpitem.ieqtd4 + 
IF( JPESTOQUE.ESNUMDEP='4',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD5 = jpitem.ieqtd5 + 
IF( JPESTOQUE.ESNUMDEP='5',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD6 = jpitem.ieqtd6 + 
IF( JPESTOQUE.ESNUMDEP='6',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD7 = jpitem.ieqtd7 + 
IF( JPESTOQUE.ESNUMDEP='7',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD8 = jpitem.ieqtd8 + 
IF( JPESTOQUE.ESNUMDEP='8',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0),
jpitem.IEQTD9 = jpitem.ieqtd9 + 
IF( JPESTOQUE.ESNUMDEP='9',IF( JPESTOQUE.ESTIPLAN='1',-jpestoque.esqtde,JPESTOQUE.ESQTDE), 0)
where JPITEM.IDITEM = JPESTOQUE.esitem
/* Registros afetados: 2.200 Registros encontrados: 0 Avisos: 0 Duração de 2 consultas: 1,375 sec. */
Nesse caso, seria um recálculo do estoque, processando todo arquivo de movimentação e atualizando os saldos.
Menos de 2 segundos, para alguns anos de movimentação - em MYSQL.

Nota: Está atualizando sempre 9 campos, mas só precisava atualizar 1... deve ser por isso que ficou demorado.

Atualizar estoque conforme lançamento

Enviado: 27 Dez 2019 13:28
por bencz
José, boa tarde!
Você está esquecendo de fazer um inner join....

Código: Selecionar todos

UPDATE jpitem,
INNER JOIN jpestoque ON jpestoque.esitem = jpitem.iditem  ------ Atenção aqui!!!!
SET    jpitem.ieqtd1 = jpitem.ieqtd1 + IF( jpestoque.esnumdep='1', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd2 = jpitem.ieqtd2 + IF( jpestoque.esnumdep='2', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd3 = jpitem.ieqtd3 + IF( jpestoque.esnumdep='3', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd4 = jpitem.ieqtd4 + IF( jpestoque.esnumdep='4', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd5 = jpitem.ieqtd5 + IF( jpestoque.esnumdep='5', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd6 = jpitem.ieqtd6 + IF( jpestoque.esnumdep='6', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd7 = jpitem.ieqtd7 + IF( jpestoque.esnumdep='7', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd8 = jpitem.ieqtd8 + IF( jpestoque.esnumdep='8', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0),
       jpitem.ieqtd9 = jpitem.ieqtd9 + IF( jpestoque.esnumdep='9', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)

Atualizar estoque conforme lançamento

Enviado: 28 Dez 2019 05:08
por JoséQuintas
Não está dando certo.
Como parece ficar somente o último lançamento, tentei assim também
O saldo ainda não bate
UPDATE jpitem
inner join
( select jpestoque.esitem, sum( IF( jpestoque.esnumdep='1', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma1,
sum( IF( jpestoque.esnumdep='2', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma2,
sum( IF( jpestoque.esnumdep='3', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma3,
sum( IF( jpestoque.esnumdep='4', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma4,
sum( IF( jpestoque.esnumdep='5', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma5,
sum( IF( jpestoque.esnumdep='6', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma6,
sum( IF( jpestoque.esnumdep='7', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma7,
sum( IF( jpestoque.esnumdep='8', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma8,
sum( IF( jpestoque.esnumdep='9', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma9
from jpestoque
group by jpestoque.esitem ) as soma

on jpitem.iditem = soma.esitem

set
jpitem.ieqtd1 = soma.soma1,
jpitem.ieqtd2 = soma.soma2,
jpitem.ieqtd3 = soma.soma3,
jpitem.ieqtd4 = soma.soma4,
jpitem.ieqtd5 = soma.soma5,
jpitem.ieqtd6 = soma.soma6,
jpitem.ieqtd7 = soma.soma7,
jpitem.ieqtd8 = soma.soma8,
jpitem.ieqtd9 = soma.soma9

Atualizar estoque conforme lançamento

Enviado: 28 Dez 2019 08:40
por JoséQuintas
update jpitem set ieqtd1=0;
update jpitem
inner join (
select jpestoque.esitem, sum( IF( jpestoque.esnumdep='1', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma1,
sum( IF( jpestoque.esnumdep='2', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma2,
sum( IF( jpestoque.esnumdep='3', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma3,
sum( IF( jpestoque.esnumdep='4', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma4,
sum( IF( jpestoque.esnumdep='5', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma5,
sum( IF( jpestoque.esnumdep='6', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma6,
sum( IF( jpestoque.esnumdep='7', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma7,
sum( IF( jpestoque.esnumdep='8', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma8,
sum( IF( jpestoque.esnumdep='9', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma9
from jpestoque
where esitem=3035
group by jpestoque.esitem ) as soma
on jpitem.iditem = soma.esitem
set jpitem.ieqtd1 = soma.soma1
como resultado disso, o item 3035 fica com saldo de -1000

mas o select sozinho:
este.png

Atualizar estoque conforme lançamento

Enviado: 28 Dez 2019 08:56
por JoséQuintas
Corrigindo a informação:

Deu certo sim.
É que o aplicativo está híbrido DBF + MySQL, então tava consultando DBF...

E fazendo o SUM() primeiro, reduziu pra menos de 1 segundo.

É de se imaginar porque:
no comando anterior a gravação acontecia pra cada lançamento de estoque.
no comando atual, só grava uma única vez com o total.
Menos gravações... mais rápido.

Atualizar estoque conforme lançamento

Enviado: 28 Dez 2019 20:04
por JoséQuintas
Como tem testes no meio de tudo, aqui o comando final:

Código: Selecionar todos

STATIC FUNCTION RecalculaSaldo()

   LOCAL nCont, cnMySql := ADOClass():New( AppConexao() )

   WITH OBJECT cnMySql
      :cSql := "UPDATE JPITEM SET IEQTD1 = 0, IEQTD2 = 0, IEQTD3 = 0, IEQTD4 = 0, IEQTD5 = 0 , " + ;
         " IEQTD6 = 0, IEQTD7 = 0, IEQTD8 = 0, IEQTD9 = 0"
      :ExecuteCmd()
      :cSql := "update jpitem" + ;
         " inner join (" + ;
         " select jpestoque.esitem, " + ;
         " sum( IF( jpestoque.esnumdep='1', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma1," + ;
         " sum( IF( jpestoque.esnumdep='2', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma2, " + ;
         " sum( IF( jpestoque.esnumdep='3', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma3," + ;
         " sum( IF( jpestoque.esnumdep='4', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma4," + ;
         " sum( IF( jpestoque.esnumdep='5', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma5," + ;
         " sum( IF( jpestoque.esnumdep='6', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma6," + ;
         " sum( IF( jpestoque.esnumdep='7', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma7," + ;
         " sum( IF( jpestoque.esnumdep='8', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma8," + ;
         " sum( IF( jpestoque.esnumdep='9', IF( jpestoque.estiplan='1', -jpestoque.esqtde, jpestoque.esqtde), 0)) as soma9" + ;
         " from jpestoque " + ;
         " group by jpestoque.esitem ) as soma" + ;
         " on jpitem.iditem = soma.esitem" + ;
         " set jpitem.ieqtd1 = soma.soma1, jpitem.ieqtd2 = soma.soma2, jpitem.ieqtd3 = soma.soma3, " + ;
         " jpitem.ieqtd4 = soma.soma4, jpitem.ieqtd5 = soma.soma5, jpitem.ieqtd6 = soma.soma6, " + ;
         " jpitem.ieqtd7 = soma.soma7, jpitem.ieqtd8 = soma.soma8, jpitem.ieqtd9 = soma.soma9"
      :ExecuteCmd()
      :cSql := "SELECT LPAD( IDITEM, 6, '0' ) AS ID, IEQTD1, IEQTD2, IEQTD3, IEQTD4, IEQTD5, " + ;
         " IEQTD6, IEQTD7, IEQTD8, IEQTD9 FROM JPITEM"
      :Execute()
      SELECT JPITEM
      DO WHILE ! :Eof()
         SEEK :String( "ID", 6 )
         :QueryCreate()
         FOR nCont = 1 TO 9
            :QueryAdd( "IEQTD" + Str( nCont, 1 ), :Number( "IEQTD" + Str( nCont, 1 ) ) )
         NEXT
         :DBFQueryExecuteUpdate( "JPITEM" )
         :MoveNext()
      ENDDO
      :CloseRecordset()
   ENDWITH

   RETURN NIL
Usa somente MySQL, mas no final atualiza os saldos do DBF, pra ficar igual.
De qualquer jeito, o ganho de velocidade foi muuuuito grande, mesmo mantendo DBF atualizado.
Todos os horários são UTC-03:00
Página 1 de 1

Powered by phpBB® Forum Software © phpBB Limited

Traduzido por: Suporte phpBB