Query complexa (somar quantidade de itens)

[dimiguel 0]

Tenho o seguinte cenário:

 

Tabelas: produtos, vendas

 

Preciso fazer uma busca pelo produto X e gerar um relatório com todas as vendas deste produto em determinado mês. Cada produto é distinto, identificado por um serial único. Preciso printar o serial de cada produto, a nota fiscal e a quantidade de produto por nota fiscal. A query está pronta. O problema:

 

A "unidade" do produto é variável. Por exemplo: produto A é vendido por unidade, produto B é vendido por quilo. Quando o produto é "unidade", sem problemas (em tese). O problema é quando o produto for por peso, ao invés de simplesmente somar as unidades, preciso fazer essa multiplicação (unidade * peso), pe.: produto A foi vendido 100 itens, sendo que o peso de cada item é de 2,5 quilos. O total da nota fiscal seria de 250 quilos, e não somente 100 unidades.

 

Alguém consegue me dar uma luz? Como falei, no caso de unidade eu consegui montar a consulta com uma subquery (que acabou por ficar um pouco lento, mas funciona...)

[Motta 645]

Tentou usar o CASE ?

 

seria algo (simplificando)

 

 

SELECT ...
       (CASE WHEN UNIDADE = 'UN' THEN QUANTIDADE
             WHEN UNIDADE = 'KG' THEN PESO*QUANTIDADE
             ELSE 0 END) TOTAL,
       ... 

[dimiguel 0]

 

Tentou usar o CASE ?

 

seria algo (simplificando)

 

 

SELECT ...
       (CASE WHEN UNIDADE = 'UN' THEN QUANTIDADE
             WHEN UNIDADE = 'KG' THEN PESO*QUANTIDADE
             ELSE 0 END) TOTAL,
       ... 

 

Como não tinha pensado nisso....

Vou tentar aqui e te falo se deu certo!

Obrigado!!!

[dimiguel 0]

Essa é minha subquery para fazer a "soma" dos produtos:

 

SELECT 
COUNT(DISTINCT(movimento_iis.iis)) AS Total
FROM movimento_iis
LEFT JOIN movimento_produtos
ON movimento_produtos.id = movimento_iis.movimento_produto_id
where movimento_produtos.id = MovimentoProduto.id
group by movimento_produtos.nota_fiscal

Como "encaixar" esse CASE nessa história?

[Motta 645]

Nolugar de movimento_iis.iis , nao sei qual a coluna da unidade e seu domínio, daí nao posso ir além.

[dimiguel 0]

Minha query completa:

SELECT 
DISTINCT(MovimentoIIS.iis),
MovimentoProduto.nota_fiscal,
(SELECT 
COUNT(
CASE WHEN Produto.unidade = 'un' THEN 1
	  WHEN Produto.unidade = 'kg' THEN Produto.peso * ?? [Não sei qual parâmetro usar] ??
ELSE 0 END)
AS Total
FROM movimento_iis
LEFT JOIN movimento_produtos
ON movimento_produtos.id = movimento_iis.movimento_produto_id
where movimento_produtos.id = MovimentoProduto.id
group by movimento_produtos.nota_fiscal) AS Qtde,
Fornecedor.name,
DATE_FORMAT(MovimentoProduto.data_notafiscal,"%d/%c/%Y") AS DataNotaFiscal
FROM movimento_produtos AS MovimentoProduto
LEFT JOIN fornecedores AS Fornecedor
ON Fornecedor.id = MovimentoProduto.fornecedor_id
LEFT JOIN 
movimento_iis AS MovimentoIIS
ON MovimentoIIS.movimento_produto_id = MovimentoProduto.id
LEFT JOIN 
movimento_produtos_itens AS MovimentoItem
ON MovimentoItem.movimento_produto_id = 242
LEFT JOIN produtos AS Produto 
ON Produto.id = MovimentoItem.produto_id
WHERE (MONTH(MovimentoProduto.data_movimento) = 11) AND (YEAR(MovimentoProduto.data_movimento) = 2013)
AND MovimentoProduto.empresa_id = 16
AND MovimentoProduto.status = 'ativo'
AND MovimentoProduto.tipo = 'entrada'

Poderia me dar um help?

[Motta 645]

tente algo como

SUM( CASE WHEN Produto.unidade = 'un' THEN MovimentoItem.QTD 
          WHEN Produto.unidade = 'kg' THEN Produto.peso * MovimentoItem.QTD  ELSE 0 END)

 

Não sei se MovimentoItem.QTD tem a quantidade vendida, mas creio que seria algo assim

[dimiguel 0]

Realmente, MovimentoItem.QTD não existe. Deve ser por isso que estou tendo tanto trabalho. Vou criar essa coluna e rodar uma query para alimentar esses valores, creio que assim será bem mais fácil.

 

Muitíssimo obrigado, Motta. Qualquer coisa digo aqui o que deu.

[Motta 645]

Não entendi bem esta questão da multiplicação, talvez o que fosse necessário seria incluir uma coluna unidade no relatório

Quantidade e Valor são campos básicos de um Item de venda

 

O rel seria algo do tipo

 

 

cod produto   descrição  qtd total     valor total       unidade
12345         arroz          5            12,38             Kg
23456         refrig.        2             3,00             Unidade
34567         cerveja        1            20,80             caixa(12)

 

seria isto ?

[dimiguel 0]

Na verdade não é bem simples assim. Vou tentar explicar:

 

Este relatório será feito por produto (por exemplo PARAFUSO). Cada PARAFUSO é único, identificado por um número serial. Eu preciso de um relatório com o serial de cada PARAFUSO e a quantidade (em peso ou unidade, de acordo com o tipo de PARAFUSO) agrupado por nota fiscal.

 

Seria mais ou menos assim:

 

PARAFUSO A - Entradas de 01/01/2014 a 31/01/2014

Serial    |    Nota Fiscal    |    Quantidade    |    Fornecedor    |    Data da Venda
---------------------------------------------------------------------------------------------------
123       |    000111         |            4     |     Zé João      |       10/01/2014 
124       |    000111         |            4     |     Zé João      |       10/01/2014 
125       |    000111         |            4     |     Zé João      |       10/01/2014 
126       |    000111         |            4     |     Zé João      |       10/01/2014 
127       |    000320         |            3     |     Casa 20      |       15/01/2014
128       |    000320         |            3     |     Casa 20      |       15/01/2014
129       |    000320         |            3     |     Casa 20      |       15/01/2014 

 

Esse relatório deve ser feito produto por produto, mês a mês. Eu gero um PDF e o cliente deve entregar, pasme, impresso! Tenho um cliente que só um produto em determinado mês tem mais de 60K de seriais. Eu sei, é o relatório mais ilógico do mundo, mas tem uma portaria que obriga que ele seja assim. Isso é coisa do Exército Brasileiro, estamos falando de rastreabilidade de produtos, por isso esse relatório - ainda assim sem o menor sentido...

Mais um detalhe: neste sistema não tratamos valores de venda. A multiplicação se dá pelo fato de que não tenho um campo onde está gravado essa informação (que deveria ser feita no momento da movimentação).

[Motta 645]

A coluna do rel. Quantidade é que depende da Unidade ?

 

Não acho o rel. tão ilogico, ilógico é imprimir, pode ser gravado em mídia ou ficar disponível para consulta.

 

A formatação poderia ser assim ?

 

 

PARAFUSO A - ENTRADAS DE 01/01/2014 A 31/01/2014
Unidade : Unidade

Serial    |    Nota Fiscal    |    Quantidade    |    Fornecedor    |    Data da Venda
---------------------------------------------------------------------------------------------------
123       |    000111         |            4     |     Zé João      |       10/01/2014
124       |    000111         |            4     |     Zé João      |       10/01/2014
125       |    000111         |            4     |     Zé João      |       10/01/2014
126       |    000111         |            4     |     Zé João      |       10/01/2014
127       |    000320         |            3     |     Casa 20      |       15/01/2014
128       |    000320         |            3     |     CASA 20      |       15/01/2014
129       |    000320         |            3     |     Casa 20      |       15/01/2014

ARROZ A - ENTRADAS DE 01/01/2014 A 15/01/2014
Unidade : Kg

Serial    |    Nota Fiscal    |    Quantidade    |    Fornecedor    |    Data da Venda
---------------------------------------------------------------------------------------------------
323       |    000111         |            4     |     Zé João      |       10/01/2014
324       |    000111         |            4     |     Zé João      |       10/01/2014

[dimiguel 0]

É isso mesmo. Meu erro foi não ter incluído o campo "quantidade" quando desenvolvi o sistema, há 7 anos. A solução que encontrei: criei este campo agora e vou gerar uma query para popular essa informação, o que tornará meu relatório muito mais fácil.

[dimiguel 0]

Motta, sua ajuda foi de grande valor até agora. Obrigado mesmo!

Como falei anteriormente, resolvi criar o campo "quantidade" em minha tabela, coisa que deveria ter feito desde o início, mas não fiz. Preciso jogar as informações neste campo para que o relatório seja criado corretamente.

 

Estou tentando a seguinte query:

UPDATE movimento_produtos_itens 
SET movimento_produtos_itens.qtde = (SELECT 
SUM(CASE WHEN produtos.unidade = "un" THEN (SELECT COUNT(DISTINCT(movimento_iis.iis)) FROM movimento_iis WHERE movimento_iis.movimento_produtos_item_id = movimento_produtos_itens.id)
         WHEN produtos.unidade = "kg" THEN (SELECT COUNT(DISTINCT(movimento_iis.iis)) FROM movimento_iis WHERE movimento_iis.movimento_produtos_item_id = movimento_produtos_itens.id)*produtos.peso
			ELSE 0 END) Total
FROM movimento_produtos_itens
left join produtos 
on produtos.id = movimento_produtos_itens.produto_id
left join movimento_produtos
on movimento_produtos.id = movimento_produtos_itens.movimento_produto_id
WHERE movimento_produtos_itens.movimento_produto_id = movimento_produtos.id
group by movimento_produtos_itens.movimento_produto_id,movimento_produtos_itens.produto_id)

A ideia é varrer todas as movimentações, fazer a soma de acordo com o tipo de unidade (se unidade apenas soma os produtos, se kg, soma as unidades e multiplica pelo peso do produto, conforme a gente já discutiu anteriormente). A subquery, que faz este cálculo, está trazendo os dados corretamente, porém o update não vai de jeito nenhum, retorna erro 1093 ("you can't specify target table 'movimento_produtos_itens' for update in from clause mysql").

 

Tem como me dar uma força em mais essa?

[Motta 645]

Talvez a solução aqui seja montar uma procedure provisória para atualizar esta nova coluna, conheço pouco de procedure em mysql

mas basicamente seria montar um sql que calcule a quantidade por cada chave da tabela alvo, colocar isto num cursor e atualizar para cada linha lida.