select
  r.contrato,
  r.codigo,
  r.nome,
  sum(cast(r.venda as numeric(13,2))) venda,
  sum(cast(r.venda * 0.03 as numeric(13,2))) v_com,
  sum(cast(r.dev as numeric(13,2))) dev,
  sum(cast(r.dev * 0.03 as numeric(13,2))) d_com,
  sum(cast(r.venda - r.dev as numeric(13,2))) total,
  sum(cast((r.venda - r.dev) * 0.03 as numeric(13,2))) com
from (
  select
    iif(ne.operacao in (1003, 1125, 1145, 1161) and ne.filial = 1, 100, iif(ne.operacao in (1117, 1162) and ne.filial = 1, 200, ne.filial)) filial,
    o.codigo_anterior contrato,
    o.operador codigo,
    o.nome,
    sum(iif(ne.opfinanceiro = '+', ne.total, 0)) venda,
    sum(iif(ne.opfinanceiro = '-', ne.total, 0)) dev
  from
    notas_emitidas ne
    left join operadores o on ne.vendedor = o.operador
  where
    ne.emissao >= :Data_Inicial and ne.emissao < :Data_Final + 1
    and ne.opfinanceiro <> '='
    and ne.cancelada = 'N'
    and (:Vendedor = 0 or ne.vendedor = :Vendedor)
    and (:Grupo = 0 or ne.vendedor in (select operador from operadores_dos_grupos where grupo = :Grupo))
  group by
    1, 2, 3, 4
  
  union all

  select
    n.filial,
    ' ',
    0,
    'Devoluções Recebidas',
    0.00,
    sum(n.total)
  from
    notas_recebidas n
  where
    n.data >= :Data_Inicial and n.data < :Data_Final + 1
    and (:Filial = 0 or n.filial = :Filial)
    and n.tipo = 'F'
    and n.opfinanceiro = '-'
    and n.nf_numero > 0
    and n.cancelada <> 'S'

  group by
    1, 2, 3, 4, 5

  ) r
where
  :Filial = 0 or r.filial = :Filial
group by
  1, 2, 3
order by
  2
