DROP TEMPORARY TABLE IF EXISTS tmpMaterias, tmpMaterias2,tmpMaterias3;

CREATE TEMPORARY TABLE `tmpmaterias` (
`materia` VARCHAR(50) NULL DEFAULT NULL,
`descricao` VARCHAR(153) NULL DEFAULT NULL,
`codigoFornecedor` VARCHAR(16) NOT NULL,
`FANTASIA` VARCHAR(250) NULL DEFAULT NULL,
`numero` VARCHAR(50) NULL DEFAULT NULL,
`unidade` SMALLINT(5) NOT NULL DEFAULT '0',
`lead_time` INT(10) NOT NULL DEFAULT '0',
`est_minimo` DECIMAL(24,6) NOT NULL DEFAULT '0.000000',
`compramini` DECIMAL(24,6) NOT NULL DEFAULT '0.000000',
`comprometido` DECIMAL(50,6) NULL DEFAULT NULL,
`consumodemanda` DECIMAL(50,6) NULL DEFAULT NULL,
`estoque` DECIMAL(24,6) NOT NULL DEFAULT '0.000000',
`codigo` VARCHAR(16) NULL DEFAULT NULL,
`descricaoProd` VARCHAR(500) NULL DEFAULT NULL,
`comprado` DECIMAL(47,6) NOT NULL DEFAULT '0.000000',
`atrazados` DECIMAL(23,0) NOT NULL DEFAULT '0',
`entregaHoje` DECIMAL(23,0) NOT NULL DEFAULT '0',
`saldo` DECIMAL(51,6) NULL DEFAULT NULL,
`LoteEntrega` VARCHAR(64) NULL DEFAULT NULL,
`lote_entregaid` INT(10) NULL DEFAULT '0',
`custo` DECIMAL(24,6) NULL DEFAULT NULL,
INDEX `materia` (`materia`),
INDEX `codigoFornecedor` (`codigoFornecedor`)
)
SELECT
m.materia,
CONCAT(mp2.descricao,' - ',mp.descricao) AS descricao,
cr.codigo AS codigoFornecedor,
cr.FANTASIA,
m.numero,
mp2.unidade,
mp.leadTime AS lead_time,
mp.est_minimo,
mp.compraMinima AS compramini,
SUM(IF(m.baixada = 0, m.quantidade, ((m.quantidade - m.baixada) * m.saldo))) AS comprometido,
md.consumodemanda,
mp.est_atual AS estoque,
c.codigo,
p.descricao AS descricaoProd,
IFNULL(z.quanti, 0) AS comprado,
IFNULL(z.atrasados, 0) AS atrazados,
IFNULL(z.entregaHoje, 0) AS entregaHoje,
((mp.est_atual + IFNULL(z.quanti, 0)) - SUM(IF(m.baixada = 0, m.quantidade, ((m.quantidade - m.baixada) * m.saldo)))) AS saldo,
CONCAT(le.lote_entregaid, ' - ', le.descricao) AS LoteEntrega,
le.lote_entregaid,
mp.custo
FROM materiaporcomando m
INNER JOIN comandos c ON c.Id = m.comandoId
INNER JOIN produtos p ON p.Id = c.ProdutoId
INNER JOIN materias_por_cor mp ON mp.Id = m.MateriaPorCorId
INNER JOIN materiaprima mp2 ON mp2.id = mp.MateriaPrimaId
INNER JOIN cfrt cr ON cr.Id = mp2.FornecedorId
INNER JOIN ordem_producao op ON op.Id = c.OrdemProducaoId
LEFT JOIN matdemanda md ON m.materia = md.materia
LEFT JOIN lote_entrega le ON le.lote_entregaId = op.lote

LEFT  JOIN
(
SELECT z.codigoPedidoCompra,
z.codigo,
SUM(z.quanti) AS quanti,
MAX(atrasados) AS atrasados,
MAX(entregaHoje) AS entregaHoje
FROM (
SELECT
cc.pedido AS codigoPedidoCompra,
i.Codigo,
SUM(i.quantidade-i.Quantidade_recebida) AS quanti,
SUM(cc.entrega < CURDATE()) AS atrasados,
SUM(cc.entrega BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 3 DAY)) AS entregaHoje
FROM itens_compra i
INNER JOIN compra_insumo cc ON cc.Pedido = i.Pedido
WHERE i.recebida = 0 AND cc.cancelado = 0
GROUP BY i.codigo

UNION

SELECT
o.ordem AS codigoPedidoCompra,
m.codigocor AS codigo,
SUM(i.t1 - i.v1)  AS quanti,
SUM(o.data_finalizacao < CURDATE()) AS atrasados,
SUM(o.data_finalizacao BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 3 DAY)) AS entregaHoje
FROM ordem_producao o
INNER JOIN itens_producao i ON i.ordem = o.ordem
INNER JOIN estoque e ON e.codigocor = i.cod_cor
INNER JOIN materias_por_cor m ON m.id = e.materiasPorCorId
WHERE o.Finalizada = 0
GROUP BY m.codigocor
) AS z
GROUP BY z.codigo
) AS z ON z.codigo = m.materia
WHERE mp2.ativo_inativo = 1
GROUP BY m.materia
HAVING 1 = 1  AND comprometido > 0.01
ORDER BY mp2.fornecedor, m.materia;

CREATE TEMPORARY TABLE tmpMaterias2 ENGINE=MEMORY AS
SELECT * FROM tmpMaterias;

CREATE TEMPORARY TABLE tmpMaterias3 ENGINE=MEMORY AS
SELECT * FROM tmpMaterias;





INSERT INTO tmpMaterias(materia, descricao, codigoFornecedor, fantasia, numero, unidade, lead_time, est_minimo, compramini,
comprometido, estoque, codigo, descricaoProd, comprado, atrazados, entregaHoje, saldo, loteEntrega, lote_entregaId)
SELECT
m.codigocor AS codmateria,
CONCAT(mm.descricao,' - ',m.descricao) AS descricao,
c.codigo        AS codigoFornecedor,
c.FANTASIA  AS fantasia,
'',
mm.unidade  AS unidade,
m.leadTime  AS leadTime,
m.est_minimo    AS est_minimo,
m.compraMinima AS compraMinima,
0           AS comprometido,
m.est_atual AS estoque,
'',
'',
ifnull(z.quanti,0)          AS comprado,
IFNULL(z.atrasados, 0)      AS atrasado,
IFNULL(z.entregaHoje, 0)    AS entregaHoje,
0,
'',
0
FROM (

SELECT DISTINCT b.codigocor
FROM tmpmaterias3 t
INNER JOIN materias_por_cor m ON m.codigocor = t.materia
INNER JOIN materias_por_cor b ON b.id = m.id_materiaBase
WHERE m.id_materiaBase > 0

) AS zz
INNER JOIN materias_por_cor m ON m.codigocor = zz.codigocor
INNER JOIN materiaprima         mm ON mm.Codigo     = m.Codigo
INNER JOIN cfrt                     c  ON c.Codigo  = mm.Fornecedor
INNER JOIN grupos_mps           g   ON g.Codigo     = mm.Cod_grupo
INNER JOIN grupos_mps           sg ON sg.Codigo     = mm.Cod_subgrupo
LEFT JOIN (
SELECT z.codigoPedidoCompra,
z.codigo,
SUM(z.quanti) AS quanti,
MAX(atrasados) AS atrasados,
MAX(entregaHoje) AS entregaHoje
FROM (
SELECT
cc.pedido AS codigoPedidoCompra,
i.Codigo,
SUM(i.quantidade-i.Quantidade_recebida) AS quanti,
SUM(cc.entrega < CURDATE()) AS atrasados,
SUM(cc.entrega BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 3 DAY)) AS entregaHoje
FROM itens_compra i
INNER JOIN compra_insumo cc ON cc.Pedido = i.Pedido
WHERE i.recebida = 0 AND cc.cancelado = 0
GROUP BY i.codigo

UNION

SELECT
o.ordem AS codigoPedidoCompra,
m.codigocor AS codigo,
SUM(i.t1 - i.v1) AS quanti,
SUM(o.data_finalizacao < CURDATE()) AS atrasados,
SUM(o.data_finalizacao BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 3 DAY)) AS entregaHoje
FROM ordem_producao o
INNER JOIN itens_producao i ON i.ordem = o.ordem
INNER JOIN estoque e ON e.codigocor = i.cod_cor
INNER JOIN materias_por_cor m ON m.id = e.materiasPorCorId
WHERE o.Finalizada = 0
GROUP BY m.codigocor
) AS z
GROUP BY z.codigo
) AS z ON z.codigo = m.Codigo
WHERE  mm.Tipo = 1  AND m.est_atual > 0

AND m.codigocor NOT IN (
SELECT t.materia
FROM tmpmaterias2 t
)
GROUP BY codmateria
HAVING 1 = 1
ORDER BY mm.Cod_grupo, mm.Cod_subgrupo;

SELECT * FROM tmpMaterias ORDER BY codigoFornecedor, materia;