Clipper On Line • Ver Tópico - Atualizar estoque conforme lançamento

Atualizar estoque conforme lançamento

Discussão sobre SQL

Moderador: Moderadores

 

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 26 Dez 2019 22:52

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

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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 27 Dez 2019 01:50

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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 27 Dez 2019 10:25

Não é o que eu queria, porque atualiza campos que não precisava, mas aceitou.

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.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Atualizar estoque conforme lançamento

Mensagempor bencz » 27 Dez 2019 13:28

José, boa tarde!
Você está esquecendo de fazer um inner join....

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)

Imagem
Avatar de usuário

bencz
Usuário Nível 4

Usuário Nível 4
 
Mensagens: 517
Data de registro: 28 Abr 2012 17:36
Curtiu: 6 vezes
Mens.Curtidas: 34 vezes

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 28 Dez 2019 05:08

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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 28 Dez 2019 08:40

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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 28 Dez 2019 08:56

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.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Atualizar estoque conforme lançamento

Mensagempor JoséQuintas » 28 Dez 2019 20:04

Como tem testes no meio de tudo, aqui o comando final:

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.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18014
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes




Retornar para SQL

Quem está online

Usuários vendo este fórum: Nenhum usuário registrado online e 7 visitantes


Ola Amigo, espero que meu site e forum tem lhe beneficiado, com exemplos e dicas de programacao.
Entao divulgue o link da Doacao abaixo para seus amigos e redes sociais ou faça uma doacao para o site forum...
MUITO OBRIGADO PELA SUA DOACAO!
Faça uma doação para o forum
cron
v
Olá visitante, seja bem-vindo ao Fórum Clipper On Line!
Efetue o seu login ou faça o seu Registro