with filtros as (

		select ? as vendedor
		, COALESCE(CAST(COALESCE(NULLIF(?, ''), '1900-01-01 00:00:00') AS TIMESTAMP) , null) as datainicial
		, COALESCE(CAST(COALESCE(NULLIF(?, ''), '3900-01-01 00:00:00') AS TIMESTAMP) , null) as datafinal
 , ? as filial
)
, ban as (

select distinct on (df.pkdocumentofiscal) df.pkdocumentofiscal, vv.fkvendedor as pkvendedor, SUM(round(cast(pv.valor as numeric), 2)) over (partition by df.pkdocumentofiscal) as valor, ban.numero, ban.nome, tp.nome as tipopagamento
from documentofiscal df
left outer join vendavendedor vv on (df.fkvenda = vv.fkvenda)
left outer join parcelavenda pv on (df.fkvenda = pv.fkvenda and pv.status not in (999, 10, 1102, 1101, 7963)
)
left outer join conta cont on (cont.pkconta = pv.fkconta)
left outer join banco ban on (cont.fkbanco = ban.pkbanco)
LEFT OUTER JOIN TipoPagamento AS tp ON (pv.fkTipoPagamento = tp.pkTipoPagamento)


)

, dfv as (

select distinct df.pkdocumentofiscal, sum (CAST(COALESCE(dfi.valorTotalGeral, 0)
- ( CASE WHEN dfi.retemPIS THEN COALESCE(dfi.pisValor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemIssqn THEN COALESCE(dfi.issqnValor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemCSLL THEN COALESCE(dfi.CSLLvalor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemIR THEN COALESCE(dfi.IRvalor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemCpom THEN COALESCE(dfi.CpomVALOR, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemCofins THEN COALESCE(dfi.CofinsVALOR, 0) ELSE 0 END )



as numeric ))
 + COALESCE(dfp.totalicmsstfcpvalor, 0)

as valor
, sum (CAST(COALESCE(dfi.valorTotalGeral, 0)
- ( CASE WHEN dfi.retemPIS THEN COALESCE(dfi.pisValor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemIssqn THEN COALESCE(dfi.issqnValor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemCSLL THEN COALESCE(dfi.CSLLvalor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemIR THEN COALESCE(dfi.IRvalor, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemCpom THEN COALESCE(dfi.CpomVALOR, 0) ELSE 0 END )
- ( CASE WHEN dfi.retemCofins THEN COALESCE(dfi.CofinsVALOR, 0) ELSE 0 END )

as numeric ))
 + COALESCE(dfp.totalicmsstfcpvalor, 0)

 as valorDesconto
from documentofiscal df
left outer join documentofiscalitem dfi on (dfi.fkdocumentofiscal = df.pkdocumentofiscal and dfi.status not in (999, 10))
left outer join documentofiscalproduto dfp on (dfp.pkdocumentofiscalproduto = df.fkdocumentofiscalproduto)

group by df.pkdocumentofiscal, COALESCE(dfp.totalicmsstfcpvalor, 0)


)
, e as (
		select v.pkvendedor, ve.fkpessoa, ve.pkvendedor as pkvendedorequipe
		FROM comissao.comissao c
		left outer join comissao.equipecomissao f on (f.comissao_id = c.id and f.status = 0)
		INNER join vendedor v on (cast(v.pkvendedor as varchar) = c.vendedor_efk)
		INNER join vendedor ve on (cast(ve.pkvendedor as varchar) = f.vendedor_efk)
		where c.status = 0
		AND f.status = 0
)

, totaldf as (
				select distinct rank () over (partition by ban.pkvendedor, df.pkdocumentofiscal order by ban.valor) as r ,
					df.numero as "Numero"
					, COALESCE(pf.nome, pj.razaosocial) as "Cliente"
					, fkDestXMun as idcidade
					, ban.pkvendedor
					, ban.valor AS VALOR

					from vendedor v

					left outer join e ef on (ef.pkvendedor = v.pkvendedor )
					inner join ban ban on ((v.pkvendedor = ban.pkvendedor or ef.pkvendedorequipe = ban.pkvendedor)  )
					left outer join e e on (e.pkvendedorequipe = ban.pkvendedor  and v.pkvendedor <> e.pkvendedor)
					inner join documentofiscal df on (ban.pkdocumentofiscal = df.pkdocumentofiscal)
					LEFT OUTER JOIN dfv dfv ON (dfv.pkdocumentofiscal = df.pkdocumentofiscal)
					LEFT OUTER JOIN Filial fi ON (fi.pkfilial = df.fkfilial)
					LEFT OUTER JOIN Pessoa pe ON (pe.pkpessoa = df.fkDestinatario)
					LEFT OUTER JOIN Filial f ON (f.fkpessoa = pe.pkpessoa)
					LEFT OUTER JOIN pessoafisica pf ON (pe.fkpessoafisica = pf.pkpessoafisica)
					LEFT OUTER JOIN pessoajuridica pj ON (pe.fkpessoajuridica = pj.pkpessoajuridica)
					inner join pessoa pev on (v.fkpessoa = pev.pkpessoa)
					inner join pessoafisica pfv on (pfv.pkpessoafisica = pev.fkpessoafisica)
					inner join pessoa vpev on (e.fkpessoa = vpev.pkpessoa)
					inner join pessoafisica vpfv on (vpfv.pkpessoafisica = vpev.fkpessoafisica)
					inner join notafiscal n on (n.fkdocumentofiscal = df.pkdocumentofiscal)
					LEFT OUTER join documentofiscalitem dfi on (dfi.fkdocumentofiscal = df.pkdocumentofiscal)
					LEFT OUTER join OperacaoImpostoNatureza oi on (oi.pkOperacaoImpostoNatureza = dfi.fkOperacaoImpostoNatureza)
					LEFT OUTER join OperacaoImpostos o on (oi.fkOperacaoImpostos = o.pkOperacaoImpostos)
					, filtros filtros
					where true

					 and df.emissao >= filtros.datainicial
					 AND df.emissao <= filtros.datafinal
					 AND pfv.nome ilike ('%' || filtros.vendedor || '%')
					 AND fi.nome ilike ('%' || filtros.filial || '%')

					 and ((n.situacao = 6294 and o.codigo ilike '1000') or (df.tipoprodutoservico in (8094, 8091)))
					 and df.status not in (999, 10)
					 and f is null

)
, total as (
				select sum(valor) as v
				from totaldf totaldf
)

, vr as (
		select v.pkvendedor, COALESCE(SUM(totaldf.valor), 0) as v
		FROM vendedor v
		left outer join e e on (e.pkvendedor = v.pkvendedor )
		LEFT OUTER JOIN totaldf totaldf ON ((v.pkvendedor = totaldf.pkvendedor or e.pkvendedorequipe = totaldf.pkvendedor))
		group by v.pkvendedor
)


, c as (
		select v.pkvendedor
			, COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.valor, '&nbsp;', '')), '.', ''), ',', '.') , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC)) as valor
			, COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.porcentagem, '&nbsp;', '')), '.', ''), ',', '.') , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC)) as porcentagem
, COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.valor, '&nbsp;', '')), '.', ''), ',', '.') , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC))  <= vr.v as verdadeiro
, CASE WHEN COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.valor, '&nbsp;', '')), '.', ''), ',', '.') , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC))  <= vr.v THEN 0 else RANK() over (partition by v.pkvendedor order by COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.valor, '&nbsp;', '')), '.', ''), ',', '.') , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC))) END as cc
		FROM total total, comissao.comissao c
		left outer join comissao.faixacomissao f on (f.comissao_id = c.id and f.status = 0)
		INNER join vendedor v on (cast(v.pkvendedor as varchar) = c.vendedor_efk)
		inner join vr vr on (vr.pkvendedor = v.pkvendedor)
		where c.status = 0
		AND f.tipo = (CASE WHEN total.v <= COALESCE(CAST(REPLACE(REPLACE(TRIM(c.valorate), '.', ''), ',', '.') AS NUMERIC), CAST( 0 AS NUMERIC)) or COALESCE(CAST(REPLACE(REPLACE(TRIM(c.valorate), '.', ''), ',', '.') AS NUMERIC), CAST( 0 AS NUMERIC)) = 0 or c.valorate is null THEN 1 ELSE 2 END)

)
, cm as (
		select v.pkvendedor
			, COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.valor, '&nbsp;', '')), '.', ''), ',', '.')  , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC)) as valor
			, COALESCE(CAST(COALESCE(NULLIF(REPLACE(REPLACE(TRIM(replace(f.porcentagem, '&nbsp;', '')), '.', ''), ',', '.')  , ''), '0') AS NUMERIC), CAST( 0 AS NUMERIC)) as porcentagem
		FROM comissao.comissao c
		inner join comissao.faixacomissao f on (f.comissao_id = c.id and f.status = 0)
		INNER join vendedor v on (cast(v.pkvendedor as varchar) = c.vendedor_efk)
		where c.status = 0
		AND f.tipo = 3

)




, f as (

select distinct
rank () over (partition by v.pkvendedor, df.pkdocumentofiscal order by c.verdadeiro,  c.cc, c.valor desc) as r ,
'Comissões' as o,
c.valor as valorate,
vr.v as totalregiao,
total.v as total,
df.emissao,
'' as produto,
fi.nome as filial,
pfv.nome
, df.numero as numero
, COALESCE(pf.nome, pj.razaosocial) as cliente
, REPLACE(TO_CHAR(ban.valor, '99999999999D99'), '.', ',') AS valor
, REPLACE(TO_CHAR(c.porcentagem, '99999999999D99'), '.', ',') AS porcentagem
, COALESCE(NULLIF(TRIM(REPLACE(TO_CHAR(coalesce(c.porcentagem, 0) / 100 * coalesce(ban.valor, 9), '99999999999D99'), '.', ',')), ',00'), '0,00')  AS valorcomissao

from vendedor v


left outer join vr vr on (vr.pkvendedor = v.pkvendedor )
left outer join e ef on (ef.pkvendedor = v.pkvendedor )
left outer join c c on (c.pkvendedor = v.pkvendedor)
inner join ban ban on ((v.pkvendedor = ban.pkvendedor or ef.pkvendedorequipe = ban.pkvendedor)  and ban.valor < c.valor )
inner join documentofiscal df on (ban.pkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER JOIN dfv dfv ON (dfv.pkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER JOIN Filial fi ON (fi.pkfilial = df.fkfilial)
LEFT OUTER JOIN Pessoa pe ON (pe.pkpessoa = df.fkDestinatario)
LEFT OUTER JOIN Filial f ON (f.fkpessoa = pe.pkpessoa)
LEFT OUTER JOIN pessoafisica pf ON (pe.fkpessoafisica = pf.pkpessoafisica)
LEFT OUTER JOIN pessoajuridica pj ON (pe.fkpessoajuridica = pj.pkpessoajuridica)
inner join pessoa pev on (v.fkpessoa = pev.pkpessoa)
inner join pessoafisica pfv on (pfv.pkpessoafisica = pev.fkpessoafisica)
inner join notafiscal n on (n.fkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER join documentofiscalitem dfi on (dfi.fkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER join OperacaoImpostoNatureza oi on (oi.pkOperacaoImpostoNatureza = dfi.fkOperacaoImpostoNatureza)
LEFT OUTER join OperacaoImpostos o on (oi.fkOperacaoImpostos = o.pkOperacaoImpostos)
, total total
, filtros filtros
where true

 and df.emissao >= filtros.datainicial
 AND df.emissao <= filtros.datafinal
 AND pfv.nome ilike ('%' || filtros.vendedor || '%')
 AND fi.nome ilike ('%' || filtros.filial || '%')
 and ((n.situacao = 6294 and o.codigo ilike '1000') or (df.tipoprodutoservico in (8094, 8091)))
 and df.status not in (999, 10)
 and f is null

 UNION

select distinct
rank () over (partition by v.pkvendedor, df.pkdocumentofiscal order by c.verdadeiro,  c.cc, c.valor desc) as r ,
'Promoção' as o,
c.valor as valorate,
vr.v as totalregiao,
total.v as total,
df.emissao,
p.nome || ' Qtd ' || dfi.quantidade as produto,
fi.nome as filial,
pfv.nome
, df.numero as numero
, COALESCE(pf.nome, pj.razaosocial) as cliente
, cp.valor AS valor
, REPLACE(TO_CHAR(
COALESCE(CAST(REPLACE(REPLACE(TRIM(replace(cp.porcentagem, '&nbsp;', '')), '.', ''), ',', '.') AS NUMERIC), CAST( 0 AS NUMERIC)) / 100 * dfi.valorunitario * dfi.quantidade
, '99999999999D99'), '.', ',')  AS porcentagem
, REPLACE(TO_CHAR( COALESCE(CAST(REPLACE(REPLACE(TRIM(replace(cp.valor, '&nbsp;', '')), '.', ''), ',', '.') AS NUMERIC), CAST( 0 AS NUMERIC)) * dfi.quantidade , '99999999999D99'), '.', ',') AS valorcomissao

from vendedor v


left outer join vr vr on (vr.pkvendedor = v.pkvendedor )
left outer join e ef on (ef.pkvendedor = v.pkvendedor )
left outer join c c on (c.pkvendedor = v.pkvendedor)
inner join ban ban on ((v.pkvendedor = ban.pkvendedor or ef.pkvendedorequipe = ban.pkvendedor)  and ban.valor < c.valor )
inner join documentofiscal df on (ban.pkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER JOIN dfv dfv ON (dfv.pkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER JOIN Filial fi ON (fi.pkfilial = df.fkfilial)
LEFT OUTER JOIN Pessoa pe ON (pe.pkpessoa = df.fkDestinatario)
LEFT OUTER JOIN Filial f ON (f.fkpessoa = pe.pkpessoa)
LEFT OUTER JOIN pessoafisica pf ON (pe.fkpessoafisica = pf.pkpessoafisica)
LEFT OUTER JOIN pessoajuridica pj ON (pe.fkpessoajuridica = pj.pkpessoajuridica)
inner join pessoa pev on (v.fkpessoa = pev.pkpessoa)
inner join pessoafisica pfv on (pfv.pkpessoafisica = pev.fkpessoafisica)
inner join notafiscal n on (n.fkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER join documentofiscalitem dfi on (dfi.fkdocumentofiscal = df.pkdocumentofiscal)
LEFT OUTER join produto p on (p.pkproduto = dfi.fkproduto)
inner join comissao.promocao cp on (cp.produto_efk = CAST(p.pkproduto as TEXT) AND cp.status = 0)
LEFT OUTER join OperacaoImpostoNatureza oi on (oi.pkOperacaoImpostoNatureza = dfi.fkOperacaoImpostoNatureza)
LEFT OUTER join OperacaoImpostos o on (oi.fkOperacaoImpostos = o.pkOperacaoImpostos)
, total total
, filtros filtros
where true

 and df.emissao >= filtros.datainicial
 AND df.emissao <= filtros.datafinal
 AND pfv.nome ilike ('%' || filtros.vendedor || '%')
 AND fi.nome ilike ('%' || filtros.filial || '%')

 and ((n.situacao = 6294 and o.codigo ilike '1000') or (df.tipoprodutoservico in (8094, 8091)))
 and df.status not in (999, 10)
 and f is null
)
select
o as tipo
 , nome as "Vendedor"
 , filial as "Filial"
, emissao as "Emissão"
, numero as "Nota"
, produto as "Promoção"
, cliente as "Cliente"
, valor as "Valor"
, porcentagem as "% Comissão"
, valorcomissao as "Valor Comissão"

 from f where r = 1
 order by o, nome, numero