DROP TEMPORARY TABLE if EXISTS tmp_horarios_produto;
CREATE TEMPORARY TABLE tmp_horarios_produto
SELECT
t.codigo,
t.celulaId,
t.dia,
t.hora,
SUM(t.qtdApontada) AS qtdApontada,
SUM(t.tempoApontado) AS tempoApontado
FROM (
SELECT z.*,
SUM(i.tempo),
SUM(i.t1+i.t2+i.t3+i.t4+i.t5+i.t6+i.t7+i.t8+i.t9+i.t10+i.t11+i.t12),
ROUND(SUM(i.tempo) / SUM(i.t1+i.t2+i.t3+i.t4+i.t5+i.t6+i.t7+i.t8+i.t9+i.t10+i.t11+i.t12),2) AS tempoUn,
z.qtdApontada * ROUND(SUM(i.tempo) / SUM(i.t1+i.t2+i.t3+i.t4+i.t5+i.t6+i.t7+i.t8+i.t9+i.t10+i.t11+i.t12),2) AS tempoApontado
FROM (
SELECT
c.id AS comandoId,
c.codigo,
a.celula_id AS celulaId,
a.dia,
a.hora,
SUM(a.quantidade) AS qtdApontada,
a.comando
FROM apontamento a
INNER JOIN comandos c ON c.numero = a.comando
WHERE c.codigo = '4323001' AND a.setor = '001' AND a.quantidade > 0 AND a.dia >= '00010120' and a.dia <= '20250115'
GROUP BY a.celula_id, a.dia, a.hora ,a.comando
ORDER BY a.celula_id,a.dia, a.hora, a.comando
) AS z
INNER JOIN itens_comando i ON i.idcomando = z.comandoId AND i.setor = '001'
GROUP BY z.celulaId, z.dia, z.hora,z.comando
) AS t
GROUP BY t.celulaId, t.dia, t.hora;


DROP TEMPORARY TABLE if EXISTS tmp_carga_intervalo;
CREATE TEMPORARY TABLE tmp_carga_intervalo
SELECT
lc.celulasid AS celulaId,
d.dia,
it.fim AS hora,
SUM(ic.cargaMinutos - it.cargaMinutosDesvio) AS carga,
it.id AS intervaloId
FROM diassemana d
INNER JOIN semanas s ON d.id_semana = s.id
inner join lote_celula lc ON s.celula_id = lc.id
inner join grupos_op go ON go.Codigo = lc.setor AND go.tipoEficienciaPremio = 1
INNER JOIN intervalo_trabalho it ON it.diaId = d.id
INNER JOIN intervalo_trabalho_colaboradores ic ON ic.intervaloTrabalhoId = it.id
INNER JOIN tmp_horarios_produto t ON t.hora = it.fim AND t.dia = d.dia AND t.celulaId = lc.celulasid
INNER JOIN cfrt c ON c.id = ic.colaboradorId AND c.contaEficiencia <> 1
GROUP BY lc.celulasid, d.dia,it.id;

DROP TEMPORARY TABLE if EXISTS tmp_horarios_produto_aux;
CREATE TEMPORARY TABLE tmp_horarios_produto_aux
SELECT
t.codigo,
t.celulaId,
t.dia,
t.hora,
SUM(t.qtdApontada) AS qtdApontada,
SUM(t.tempoApontado) AS tempoApontado
FROM (
SELECT z.*,
SUM(i.tempo),
SUM(i.t1+i.t2+i.t3+i.t4+i.t5+i.t6+i.t7+i.t8+i.t9+i.t10+i.t11+i.t12),
ROUND(SUM(i.tempo) / SUM(i.t1+i.t2+i.t3+i.t4+i.t5+i.t6+i.t7+i.t8+i.t9+i.t10+i.t11+i.t12),2) AS tempoUn,
z.qtdApontada * ROUND(SUM(i.tempo) / SUM(i.t1+i.t2+i.t3+i.t4+i.t5+i.t6+i.t7+i.t8+i.t9+i.t10+i.t11+i.t12),2) AS tempoApontado
FROM (
SELECT
c.id AS comandoId,
c.codigo,
a.celula_id AS celulaId,
a.dia,
a.hora,
SUM(a.quantidade) AS qtdApontada,
a.comando
FROM apontamento a
INNER JOIN comandos c ON c.numero = a.comando
INNER JOIN tmp_horarios_produto t ON t.hora = a.hora AND t.dia = a.dia AND t.celulaId = a.celula_id
WHERE c.codigo <> '4323001' AND a.setor = '001' AND a.quantidade > 0 AND a.dia >= '00010120' and a.dia <= '20250115'
GROUP BY a.celula_id, a.dia, a.hora ,a.comando
ORDER BY a.celula_id,a.dia, a.hora, a.comando
) AS z
INNER JOIN itens_comando i ON i.idcomando = z.comandoId AND i.setor = '001'
GROUP BY z.celulaId, z.dia, z.hora,z.comando
) AS t
GROUP BY t.celulaId, t.dia, t.hora;

SELECT
t.celulaId,
t.dia,
t.hora,
t.carga,
sum(p.tempoApontado) tempoApondadoProduto,
sum(IFNULL(a.tempoApontado,0)) AS tempoApontadoOutros,
ROUND(((sum(p.tempoApontado) + sum(IFNULL(a.tempoApontado,0))) / t.carga) * 100,2) AS efiHorario,
ROUND((sum(p.tempoApontado) / (sum(p.tempoApontado) + sum(IFNULL(a.tempoApontado,0))))*100,2) AS percentualRefEfi
FROM tmp_carga_intervalo t
INNER JOIN tmp_horarios_produto p ON p.celulaId = t.celulaId AND p.dia = t.dia AND t.hora = p.hora
LEFT JOIN tmp_horarios_produto_aux a ON a.celulaId = t.celulaId AND t.dia = a.dia AND t.hora = a.hora
GROUP BY t.celulaId, t.dia, t.hora
ORDER BY t.dia, t.celulaId;