Atualizar estoque conforme lançamento

Forum sobre SQL.

Moderador: Moderadores

Responder
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Avatar do usuário
bencz
Usuário Nível 4
Usuário Nível 4
Mensagens: 524
Registrado em: 28 Abr 2012 17:36
Contato:
Contato bencz

Atualizar estoque conforme lançamento

Mensagem 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)
Imagem
Voltar ao topo
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Avatar do usuário
JoséQuintas
Administrador
Administrador
Mensagens: 20267
Registrado em: 26 Fev 2007 11:59
Localização: São Paulo-SP

Atualizar estoque conforme lançamento

Mensagem 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.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg mt, fivewin 25.04, multithread, dbfcdx, MySQL, ADOClass, PDFClass, SefazClass, (hwgui mt), (hmg3), (hmg extended), (oohg), PNotepad, ASP, stored procedure, stored function, Linux (Flagship/harbour 3.2)
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
https://github.com/JoseQuintas/
Voltar ao topo
Responder

Voltar para “SQL”