-- Vendas por Categoria de Item e Tipo de Recebimento
-- Detalhe
WITH RECURSIVE niveis(id_categoria_item, id_categoria_item_pai) AS (
	SELECT	id_categoria_item,
		id_categoria_item_pai,
		1 AS nivel
	FROM	categoria_item
	
	UNION ALL
	SELECT	ci.id_categoria_item,
		ci.id_categoria_item_pai,
		nv.nivel +1
	FROM	categoria_item AS ci
		INNER JOIN niveis AS nv ON (nv.id_categoria_item = ci.id_categoria_item_pai))
		
SELECT	r.nome_empresa,
	r.categoria_produto,
	r.cod_produto,
	r.denominacao_produto,
	r.unidade,
	SUM(r.quantidade) AS quantidade,
	CASE WHEN (SUM(r.quantidade) != 0) THEN ROUND((SUM(r.total_bruto) / SUM(r.quantidade)), 3)
		ELSE CAST(0 AS DECIMAL(12,3)) END AS venda_bruta_unit,
	SUM(r.total_bruto) AS total_bruto,
	SUM(r.desconto) AS desconto,
	SUM(r.acrescimo) AS acrescimo,
	CASE WHEN (SUM(r.quantidade) != 0) THEN ROUND(COALESCE(((SUM(r.total_bruto) - SUM(r.desconto) + SUM(r.acrescimo)) / SUM(r.quantidade)), 0), 3)
		ELSE CAST(0 AS DECIMAL(12,3)) END AS venda_liq_unit,
	ROUND(COALESCE((SUM(r.total_bruto) - SUM(r.desconto) + SUM(r.acrescimo)), 0), 3) AS venda_liq_total,
	CASE WHEN (SUM(r.quantidade) != 0) THEN ROUND(COALESCE((SUM(r.custo) / SUM(r.quantidade)), 0), 4)
		ELSE CAST(0 AS DECIMAL(12,3)) END AS custo_medio_unit,
	CASE WHEN (SUM(r.quantidade) != 0) THEN ROUND(COALESCE((((SUM(r.total_bruto) - SUM(r.desconto) + SUM(r.acrescimo)) - SUM(r.custo)) / SUM(r.quantidade)), 0), 3)
		ELSE CAST(0 AS DECIMAL(12,3)) END AS lucro_bruto_unit,
	ROUND(COALESCE((SUM(r.total_bruto) - SUM(r.custo) - SUM(r.desconto) + SUM(r.acrescimo)), 0), 3) AS lucro_bruto_total,
	SUM(r.custo) AS custo
FROM	(


	SELECT	se.nome AS nome_empresa,
		ci.denominacao AS categoria_produto,
		i.codigo AS cod_produto,
		i.denominacao AS denominacao_produto,
		um.sigla AS unidade,
		SUM(ivc.quantidade) AS quantidade,
		SUM(ivc.total_bruto) AS total_bruto,
		SUM(ivc.desconto + ivc.desconto_automatico + ivc.desconto_rateado + ivc.desconto_ajuste_rateado + ivc.desconto_automatico_rateado + ivc.desconto_fidelidade_rateado) AS desconto,
		SUM(ivc.acrescimo + ivc.acrescimo_automatico + ivc.acrescimo_rateado + ivc.acrescimo_ajuste_rateado + ivc.acrescimo_automatico_rateado) AS acrescimo,
		COALESCE(SUM(me.valor), 0) AS custo
	FROM	movimento_venda_terminal AS mvt
		INNER JOIN venda_cf AS vc ON (vc.id_movimento_venda_terminal = mvt.id_movimento_venda_terminal)
		INNER JOIN item_venda_cf AS ivc ON (ivc.id_venda_cf = vc.id_venda_cf)
		LEFT JOIN movimento_estoque AS me ON (me.id_movimento_estoque = ivc.id_movimento_estoque)
		INNER JOIN item AS i ON (ivc.id_item = i.id_item)
		INNER JOIN categoria_item AS ci ON (i.id_categoria_item = ci.id_categoria_item)
		INNER JOIN unidade_medida AS um ON (i.id_unidade_medida = um.id_unidade_medida)
		INNER JOIN sis_empresa AS se ON (se.id_empresa = mvt.id_empresa)
	WHERE	mvt.id_empresa = :IdEmpresaObrigatorio
	AND	mvt.data_movimento BETWEEN :DataInicial AND :DataFinal
	AND	DATE(vc.data_cupom) BETWEEN :DataInicial AND :DataFinal
	AND	DATE(ivc.data_venda) BETWEEN :DataInicial AND :DataFinal
	AND	ci.id_categoria_item IN (SELECT id_categoria_item FROM niveis)
	AND	CASE WHEN ('S' = 'S') THEN ('S' = 'S') ELSE (ivc.id_bico_combustivel IS NULL) END
	AND	(ivc.cancelado = 'N' AND vc.cancelada = 'N')
	GROUP BY 1,2,3,4,5

	UNION ALL


	SELECT	se.nome AS nome_empresa,
		ci.denominacao AS categoria_produto,
		i.codigo AS cod_produto,
		i.denominacao AS denominacao_produto,
		um.sigla AS unidade,
		SUM(infs.quantidade) AS quantidade,
		SUM(infs.total_item + infs.desconto) AS total_bruto,
		SUM(infs.desconto) AS desconto,
		0.00 AS acrescimo,
		COALESCE(SUM(me.valor), 0) AS custo
	FROM	nota_fiscal_saida AS nfs
		INNER JOIN item_nfs AS infs ON (infs.id_nota_fiscal_saida = nfs.id_nota_fiscal_saida)
		INNER JOIN sis_referencia_registro AS rr ON (rr.id_chave_registro_a = infs.id_item_nfs AND rr.tipo_referencia = 'ref_1101_0411_movimento_estoque_nfs')
		INNER JOIN movimento_estoque AS me ON (me.id_movimento_estoque = rr.id_chave_registro_b)
		INNER JOIN item AS i ON (i.id_item = infs.id_item)
		INNER JOIN categoria_item AS ci ON (i.id_categoria_item = ci.id_categoria_item)
		INNER JOIN unidade_medida AS um ON (i.id_unidade_medida = um.id_unidade_medida)
		INNER JOIN sis_empresa AS se ON (se.id_empresa = nfs.id_empresa)
	WHERE	nfs.id_empresa = :IdEmpresaObrigatorio
	AND	nfs.emissao BETWEEN :DataInicial AND :DataFinal
	AND	ci.id_categoria_item IN (SELECT id_categoria_item FROM niveis)
	AND	nfs.situacao = 1 
	AND	EXISTS(SELECT 1 FROM cfop WHERE tipo_movimento_estoque != 4 AND gera_titulo_financeiro = 'S' AND id_tipo_movimento_estoque = 7 AND id_cfop = infs.id_cfop)
	AND	CASE WHEN ('S' = 'S') THEN ('S' = 'S') ELSE (infs.id_local_estoque NOT IN (SELECT id_local_estoque FROM local_estoque WHERE tipo = 2)) END
	GROUP BY 1,2,3,4,5

	UNION ALL


	SELECT	se.nome AS nome_empresa,
		ci.denominacao AS categoria_produto,
		i.codigo AS cod_produto,
		i.denominacao AS denominacao_produto,
		um.sigla AS unidade,
		(SUM(infe.quantidade) * -1) AS quantidade,
		(SUM(infe.total_produto + infe.desconto) * -1) AS total_bruto,
		(SUM(infe.desconto) * -1) AS desconto,
		0.00 AS acrescimo,
		(SUM(me.valor) * -1) AS custo
	FROM	nota_fiscal_entrada AS nfe
		INNER JOIN item_nfe AS infe ON (infe.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada)
		INNER JOIN movimento_estoque AS me ON (me.id_movimento_estoque = infe.id_movimento_estoque)
		INNER JOIN item AS i ON (i.id_item = infe.id_item)
		INNER JOIN categoria_item AS ci ON (i.id_categoria_item = ci.id_categoria_item)
		INNER JOIN unidade_medida um ON (i.id_unidade_medida = um.id_unidade_medida)
		INNER JOIN sis_empresa AS se ON (se.id_empresa = nfe.id_empresa)
	WHERE	nfe.id_empresa = :IdEmpresaObrigatorio
	AND	nfe.emissao BETWEEN :DataInicial AND :DataFinal
	AND	(0 = 0 OR i.tipo_item = 0)
	AND	me.data_movimento BETWEEN :DataInicial AND :DataFinal
	AND	ci.id_categoria_item IN (SELECT id_categoria_item FROM niveis)
	AND	nfe.situacao = 1 
	AND	EXISTS(SELECT 1 FROM cfop WHERE tipo_movimento_estoque != 4 AND id_tipo_movimento_estoque = 3 AND id_cfop = infe.id_cfop)
	AND	CASE WHEN ('S' = 'S') THEN ('S' = 'S') ELSE (infe.id_local_estoque NOT IN (SELECT id_local_estoque FROM local_estoque WHERE tipo = 2)) END
	GROUP BY 1,2,3,4,5 ) AS r
GROUP BY r.nome_empresa,
	r.categoria_produto,
	r.cod_produto,
	r.denominacao_produto,
	r.unidade
ORDER BY r.nome_empresa,
	r.categoria_produto,
	r.denominacao_produto