WITH detalhamento AS (
	-- Notas COM Conhecimento de Frete
	SELECT 	pe.nome AS nome_empresa,
		pe.inscricao_estadual AS inscricao_estadual,
		pe.cnpj_cpf AS cnpj,
		CASE WHEN i.tipo_item = 2 THEN 'SERVIÇO'  
			WHEN (i.id_item IN (SELECT le.id_item_tanque FROM local_estoque AS le WHERE le.id_empresa = nfe.id_empresa AND le.tipo = 2)) THEN 'COMBUSTÍVEL'
			ELSE 'PRODUTO' END AS tipo_item,
		lpad(CAST(EXTRACT (DAY FROM nfe.entrada) AS VARCHAR), 2, '0')	AS dia,
		'NF'	AS esp,
		nfe.serie AS ser_sub,
		nfe.numero AS numero,
		nfe.entrada AS datadoc,
		forn.cnpj_cpf AS codigo_emitente,
		uf.sigla AS uf_orig,
		SUM(infe.total_produto + infe.icms_substituicao + infe.rateio_seguro + infe.rateio_despesa_acessoria + infe.ipi  + infe.fcp_st_retido - (CASE WHEN infe.deduzir_icms_desoneracao = 'S' THEN infe.icms_desoneracao ELSE 0 END)) AS valor_contabil,
		cfop.cfop AS cfop,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN (infe.base_icms - infe.base_icms_reduzida) ELSE 0 END) AS isenta_naotributada,
		SUM(infe.ipi) AS ipi,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN infe.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
		(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN infe.aliquota_icms ELSE 0 END)	AS aliquota_icms,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN infe.icms ELSE 0 END) AS imposto_creditado,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN (infe.total_produto + rateio_seguro + rateio_despesa_acessoria - infe.base_icms_reduzida) ELSE (infe.total_produto + rateio_seguro + rateio_despesa_acessoria) END) AS outras,
		SUM(infe.icms_substituicao) AS icms_substituicao,
		SUM(infe.base_icms_substituicao) AS base_icms_substituicao
	FROM nota_fiscal_entrada AS nfe
		JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', nfe.id_empresa, 0, 'S')))
		JOIN pessoa AS forn	ON (nfe.id_fornecedor=forn.id_pessoa)
		JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
		JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
		JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=nfe.id_modelo_documento_fiscal)
		JOIN item_nfe AS infe ON (nfe.id_nota_fiscal_entrada=infe.id_nota_fiscal_entrada)
		JOIN item AS i ON (infe.id_item = i.id_item)
		JOIN cfop AS cfop ON (cfop.id_cfop=infe.id_cfop)
	WHERE nfe.id_nota_fiscal_entrada IN (SELECT id_chave_registro_b FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
		AND nfe.id_empresa = :IdEmpresaObrigatorio
		AND CAST(EXTRACT('YEAR' FROM DATE(nfe.entrada)) AS INTEGER) = :Ano
		AND nfe.situacao=1
		
	GROUP BY
		1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 17

	UNION ALL

	-- Notas SEM Conhecimento de Frete
	SELECT 	pe.nome AS nome_empresa,
		pe.inscricao_estadual AS inscricao_estadual,
		pe.cnpj_cpf AS cnpj,
		CASE WHEN i.tipo_item = 2 THEN 'SERVIÇO'  
			WHEN (i.id_item IN (SELECT le.id_item_tanque FROM local_estoque AS le WHERE le.id_empresa = nfe.id_empresa AND le.tipo = 2)) THEN 'COMBUSTÍVEL'
			ELSE 'PRODUTO' END AS tipo_item,
		lpad(CAST(EXTRACT (DAY FROM nfe.entrada) AS VARCHAR), 2, '0')	AS dia,
		'NF'	AS esp,
		nfe.serie AS ser_sub,
		nfe.numero AS numero,
		nfe.entrada AS datadoc,
		forn.cnpj_cpf AS codigo_emitente,
		uf.sigla AS uf_orig,
		SUM(infe.total_produto + infe.icms_substituicao + infe.rateio_frete + infe.rateio_seguro + infe.rateio_despesa_acessoria + infe.ipi  + infe.fcp_st_retido - (CASE WHEN infe.deduzir_icms_desoneracao = 'S' THEN infe.icms_desoneracao ELSE 0 END)) AS valor_contabil,
		cfop.cfop AS cfop,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN (infe.base_icms - infe.base_icms_reduzida) ELSE 0 END) AS isenta_naotributada,
		SUM(infe.ipi) AS ipi,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN infe.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
		(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN infe.aliquota_icms ELSE 0 END)	AS aliquota_icms,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN infe.icms ELSE 0 END) AS imposto_creditado,
		SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN (infe.total_produto + rateio_seguro + rateio_despesa_acessoria + rateio_frete - infe.base_icms_reduzida) ELSE (infe.total_produto + rateio_seguro + rateio_despesa_acessoria + rateio_frete) END) AS outras,
		SUM(infe.icms_substituicao) AS icms_substituicao,
		SUM(infe.base_icms_substituicao) AS base_icms_substituicao
	FROM nota_fiscal_entrada AS nfe
		JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', nfe.id_empresa, 0, 'S')))
		JOIN pessoa AS forn	ON (nfe.id_fornecedor=forn.id_pessoa)
		JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
		JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
		JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=nfe.id_modelo_documento_fiscal)
		JOIN item_nfe AS infe	ON (nfe.id_nota_fiscal_entrada=infe.id_nota_fiscal_entrada)
		JOIN item AS i ON (infe.id_item = i.id_item)
		JOIN cfop AS cfop ON (cfop.id_cfop=infe.id_cfop)
	WHERE nfe.id_nota_fiscal_entrada NOT IN (SELECT id_chave_registro_b FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
		AND nfe.id_empresa = :IdEmpresaObrigatorio
		AND CAST(EXTRACT('YEAR' FROM DATE(nfe.entrada)) AS INTEGER) = :Ano
		AND nfe.situacao=1
	
	GROUP BY
		1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 17
							
	UNION ALL

	-- Conhecimento de Frete
	SELECT 	pe.nome AS nome_empresa,
		pe.inscricao_estadual AS inscricao_estadual,
		pe.cnpj_cpf AS cnpj,
		'CONHECIMENTO DE FRETE' AS tipo_item, 
		lpad(CAST(EXTRACT (DAY FROM nfe.entrada) AS VARCHAR), 2, '0')	AS dia,
		'CT'	AS esp,
		nfe.serie AS ser_sub,
		CAST(ct.numero AS INTEGER) AS numero,
		nfe.entrada AS datadoc,
		forn.cnpj_cpf AS codigo_emitente,
		uf.sigla AS uf_orig,
		(ct.valor_frete) AS valor_contabil,
		cfop.cfop AS cfop,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN (ct.base_icms - ct.base_icms_reduzida) 
				 WHEN ct.id_cst in (4, 5) AND uf.sigla=ufe.sigla THEN (ct.valor_frete)
				 ELSE 0 END) AS isenta_naotributada,
		CAST(0 AS NUMERIC) AS ipi,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.aliquota ELSE 0 END)	AS aliquota_icms,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.icms ELSE 0 END) AS imposto_creditado,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN 0 
				 WHEN ct.id_cst in (4, 5)  AND uf.sigla=ufe.sigla THEN 0
				 ELSE (valor_frete - ct.base_icms_reduzida) END) AS outras,
		CAST(0 AS NUMERIC) AS icms_substituicao,
		CAST(0 AS NUMERIC) AS base_icms_substituicao
	FROM nota_fiscal_entrada AS nfe
		JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', nfe.id_empresa, 0, 'S')))
		JOIN municipio AS mune	ON (mune.id_municipio=pe.id_municipio)
		JOIN uf AS ufe ON (ufe.id_uf=mune.id_uf)
		JOIN item_nfe AS infe ON (nfe.id_nota_fiscal_entrada=infe.id_nota_fiscal_entrada)
		JOIN sis_referencia_registro AS srr	ON (srr.id_chave_registro_b=infe.id_nota_fiscal_entrada)
		JOIN conhecimento_transporte AS ct	ON (ct.id_conhecimento_transporte=srr.id_chave_registro_a AND ct.entrada_saida = 1)
		JOIN cfop AS cfop ON (cfop.id_cfop=ct.id_cfop)
		JOIN pessoa AS forn	ON (ct.id_transportadora=forn.id_pessoa)
		JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
		JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
		JOIN modelo_documento_fiscal	AS mdf	ON (mdf.id_modelo_documento_fiscal=ct.id_modelo_documento_fiscal)
	WHERE nfe.id_nota_fiscal_entrada IN (SELECT id_chave_registro_b FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
		AND nfe.id_empresa = :IdEmpresaObrigatorio
		AND CAST(EXTRACT('YEAR' FROM DATE(nfe.entrada)) AS INTEGER) = :Ano
		AND ct.situacao = 1

	GROUP BY
		1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19

	UNION ALL

	-- Conhecimento de Frete Sem Nota
	SELECT 	pe.nome AS nome_empresa,
		pe.inscricao_estadual AS inscricao_estadual,
		pe.cnpj_cpf AS cnpj,
		'CONHECIMENTO DE FRETE' AS tipo_item, 
		lpad(CAST(EXTRACT (DAY FROM ct.data_emissao) AS VARCHAR), 2, '0')	AS dia,
		'CT'	AS esp,
		mdf.serie AS ser_sub,
		CAST(ct.numero AS INTEGER) AS numero,
		ct.data_emissao AS datadoc,
		forn.cnpj_cpf AS codigo_emitente,
		uf.sigla AS uf_orig,
		(ct.valor_frete) AS valor_contabil,
		cfop.cfop AS cfop,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN (ct.base_icms - ct.base_icms_reduzida) 
				 WHEN ct.id_cst in (4, 5) AND uf.sigla=ufe.sigla THEN (ct.valor_frete)
				 ELSE 0 END) AS isenta_naotributada,
		CAST(0 AS NUMERIC) AS ipi,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.aliquota ELSE 0 END)	AS aliquota_icms,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.icms ELSE 0 END) AS imposto_creditado,
		(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN 0 
			  WHEN ct.id_cst in (4, 5)  AND uf.sigla=ufe.sigla THEN 0
			  ELSE (valor_frete - ct.base_icms_reduzida) END) AS outras,
		CAST(0 AS NUMERIC) AS icms_substituicao,
		CAST(0 AS NUMERIC) AS base_icms_substituicao
	FROM conhecimento_transporte AS ct
		JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', ct.id_empresa, 0, 'S')))
		JOIN municipio AS mune ON (mune.id_municipio=pe.id_municipio)
		JOIN uf AS ufe ON (ufe.id_uf=mune.id_uf)
		JOIN cfop AS cfop ON (cfop.id_cfop=ct.id_cfop)
		JOIN pessoa AS forn	ON (ct.id_transportadora=forn.id_pessoa)
		JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
		JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
		JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=ct.id_modelo_documento_fiscal)
	WHERE ct.id_conhecimento_transporte NOT IN (SELECT id_chave_registro_a FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
		AND ct.id_empresa = :IdEmpresaObrigatorio
		AND CAST(EXTRACT('YEAR' FROM DATE(ct.data_emissao)) AS INTEGER) = :Ano
		AND ct.situacao = 1
		AND ct.entrada_saida = 1

	GROUP BY
		1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
)

SELECT 	
	de.nome_empresa AS nome_empresa,
	de.inscricao_estadual AS inscricao_estadual,
	de.cnpj AS cnpj,
	de.tipo_item AS tipo_item,
	de.cfop AS cfop,
	SUM(de.isenta_naotributada) AS isenta_naotributada,
	SUM(de.valor_contabil) AS valor_contabil,
	SUM(de.base_calculo_icms) AS base_calculo_icms,
	SUM(de.imposto_creditado) AS imposto_creditado,
	SUM(de.outras) AS outras,
	SUM(de.icms_substituicao) AS icms_substituicao,
	SUM(de.base_icms_substituicao) AS base_icms_substituicao
FROM detalhamento AS de
GROUP BY
	1, 2, 3, 4, 5
ORDER BY
	de.cfop;