with
    saldofin_deptos_ignorar as (
        select d.iddepartamento from glb.departamento d where substr(d.classificacao,1,3) in ('110')
    ),
    realizado as (
  select
      tb.*,
    gcl.idgrupo
  from(
  select
     cast( to_char(ib.datamovimento,'yyyymm') as integer) as anomes
    ,cast( to_char(ib.datamovimento,'dd') as integer) as dia
    ,substr(d.classificacao,1,3) as linha
    ,gf.uf
    ,pg.idsituacaoproduto
    ,sum( case when ib.idvendedor <> 810 then ib.totalpresente * case when ib.idoperacaoproduto = 101040 then -1 else 1 end else 0 end ) as sf
    ,sum( case when ib.idvendedor <> 810 then ib.totalfuturo * case when ib.idoperacaoproduto = 101040 then -1 else 1 end else 0 end ) as cf
    ,sum( case when ib.idvendedor <> 810 then ib.totalcustomedio * case when ib.idoperacaoproduto = 101040 then -1 else 1 end else 0 end ) as cm
    ,sum( case when ib.idvendedor <> 810 then ib.totaldesejadopresente * case when ib.idoperacaoproduto = 101040 then -1 else 1 end else 0 end ) as desejado
    ,sum( case when ib.idvendedor <> 810 then (ib.totalfuturo - ib.totalpresente) * case when ib.idoperacaoproduto = 101040 then -1 else 1 end else 0 end ) as juros
    ,sum( case when ib.idvendedor <> 810 then ib.totalpis * case when ib.idoperacaoproduto = 101040 and :anomes >= 201211 then -1 else 1 end else 0 end ) as pis
    ,sum( case when ib.idvendedor <> 810 then ib.totalcofins * case when ib.idoperacaoproduto = 101040 and :anomes >= 201211 then -1 else 1 end else 0 end ) as cofins
    ,sum( case when ib.idvendedor <> 810 then (ib.totalbasecalculoicms * case when ib.idoperacaoproduto = 101040 and :anomes >= 201211 then -1 else 1 end * (case when not ib.aliquotaicms is null then ib.aliquotaicms else 0 end) /100) else 0 end ) as icms
  from rst.itembase ib
  left join glb.produto p on p.idproduto = ib.idproduto
  left join glb.produtograde pg on pg.idproduto = ib.idproduto and pg.idgradex = ib.idgradex and pg.idgradey = ib.idgradey
  left join glb.departamento d on d.iddepartamento = p.iddepartamento
  left join gazin.gzgrupofilial gf on gf.idfilial = ib.idfilial
  where ib.idfilial = :idfilial
    and ib.datamovimento between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
    and ib.idoperacaoproduto in (102010,101040)
    and not ib.idvendedor = 6781
  group by 1,2,3,4,5
  )tb
  left join gazin.ccg_gerente_grupo_comissao_linha gcl on gcl.linhas = linha
  where gcl.idgrupo in (1,2,3,4,5)
)
,metarecebimento as (
  select
    *
  from dblink( 'hostaddr=10.0.253.11 port=6432 dbname=ccg user=ccg password=pst3811','select idfilial, coalesce(metavencido,0) as metavencido from avg.filial_meta_mensal where coalesce(metavencido,0)>0 and  idgrupo= 0 and anomes='||:anomes ) as bla( idfilial integer, metavencido public.moeda )
)
,metavenda as ( select gazin.ccg_get_meta_venda_filial(:idfilial,:anomes) as metavenda)

,vendedor as (select gazin.ccg_get_vendedor_filial(:idfilial,:anomes) as vend)

,metalinha as (
    select
      gc.idgrupo as grupo
      ,gc.descricao as idgrupo
      ,round((gcp.participacao / 100),4) as indice
      ,round(gcp.participacao, 4) as participacao
      ,gc.peso
    from gazin.ccg_gerente_grupo_comissao_participacao gcp
    left join gazin.ccg_gerentes_grupo_comissao gc on gcp.idgrupo = gc.idgrupo
    where gcp.idfilial = :idfilial and gcp.idgrupo in (1,2,3,4,5) and gcp.anomes = cast(:anomes as integer)
)

,metadia as (
    select
      'Do dia ' ||to_char(md.dodia,'00') ||' ao '||to_char(md.atedia,'00') as idgrupo
      ,dodia
      ,atedia
      ,md.participacao /100 as indice
      ,md.participacao
      ,md.peso
    from gazin.ccg_gerentes_meta_dias md
    where md.anomes = :anomes
)

,excecao as (
    select
        *
    from gazin.gerente_excessao_metas_mensal ge
    where ge.idfilial = :idfilial
    and ge.anomes = :anomes
)

,vencido AS (
    select
      sum(case when vencido then valorcontabil else 0 end) as valorvencido30dias
      ,sum(case when vencido then 0 else valorcontabil end) as valoravencer30dias
      ,sum(case when vencido then valorcontabil else 0 end) + sum(case when vencido then 0 else valorcontabil end) as valortotal
    from (
      select
        tp.idfilial,
        tp.idprocessotitulo,
        (date(date(  date(:anomes||'01') +interval '1 month') -1) - tp.datavencimento) >= 0 as vencido,
        trunc((date(date(  date(:anomes||'01') +interval '1 month') -1) - tp.datavencimento) / 30) as dias,
        sum(tp.saldocapital + coalesce(tp.saldojuro,0)) as valorcontabil
      from rst.tituloparcela tp
           /*left join rst.titulo t on t.idtipotitulo = tp.idtipotitulo
                                     and t.idfilial = tp.idfilial
                                     and t.idtitulo = tp.idtitulo*/
      where
          tp.idfilial = :idfilial
        and tp.idtipotitulo = 1
        and ( date(  date(:anomes||'01') +interval '1 month') -1 < tp.databaixa or tp.databaixa is null )
        and date(  date(:anomes||'01') +interval '1 month') -1 >= tp.datainicial
      group by 1,2,3,4
    ) tb
    inner join gazin.ccg_gerente_processos_fiado c on  tb.idprocessotitulo= c.idprocesso and c.anomes = :anomes -- '201408'
)
,perdas AS (

    SELECT tp.idfilial,
       to_char(tp.databaixa, 'yyyymm') as anomes,
       SUM(tp.valorpago) AS perdido180
    FROM   rst.tituloparcela tp
    WHERE tp.databaixa between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
      and tp.idtipotitulo = 1
      and tp.idfilial = :idfilial
      AND tp.idprocessobaixa IN (SELECT pro.idprocesso FROM glb.processo pro WHERE pro.idoperacao = 302040 and pro.idprocesso not in (3099))
    GROUP  BY 1,2

)
,recperda AS (
    SELECT tp.idfilial,
       to_char(tp.databaixa, 'yyyymm') as anomes,
       SUM(tp.valorpago) AS recperdido
    FROM   rst.tituloparcela tp
    inner join glb.processoparametro p on p.idprocesso=tp.idprocessotitulo and p.idparametro = 200 and p.valor = '7'
    /*LEFT JOIN rst.titulo t  ON t.idtipotitulo = tp.idtipotitulo
                        AND t.idfilial = tp.idfilial
                        AND t.idtitulo = tp.idtitulo*/
    WHERE tp.databaixa between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
      and tp.idfilial = :idfilial
      and tp.idtipotitulo = 1
      --and t.idtipocarteira = 7
      and tp.idprocessobaixa <> 3099
      and not tp.idprocessobaixa in (SELECT pro.idprocesso FROM glb.processo pro WHERE pro.idoperacao = 302060)
      GROUP  BY 1,2
)
,saldofin as (
                  select
                       coalesce(sum(tb.saldo * tb.custom), 0) as saldofin
                  from(
                    select
                      tb.idfilial as idfilial,
                      cast(split_part(tb.saldos,';',1) as numeric) as saldo,
                      cast(split_part(tb.saldos,';',2) as numeric) as custom
                    from (
                      select
                          tb.*
                          ,( select
                               round(tb.saldo,6)||';'||round(tb.custom,4)
                             from ( SELECT
                                      case when ib.quantidade>0 then ib.totalcustomedio/ib.quantidade else 0 end as custom
                                     ,ib.saldo
                                    FROM rst.itembase ib
                                    WHERE ib.idproduto = tb.idproduto
                                      and ib.idgradex = tb.idgradex
                                      and ib.idgradey = tb.idgradey
                                      and ib.idfilial = tb.idfilial
                                      and ib.idlocalsaldo =1
                                      and ib.datamovimento <= tb.posicao
                                      and ib.idoperacaoproduto > 0
                                    order by ib.idfilial, ib.idproduto, ib.idgradex, ib.idgradey, ib.idlocalsaldo, datamovimento desc, idmovimento desc
                                    limit 1
                                 ) tb
                           ) as saldos
                          from (
                            select
                              tb.*
                              ,(
                                  select
                                      case when :anomes < 201504 or current_date <= date(:anomes||'15') then date(date(:anomes||'01')+interval '1 month') - 1
                                          else date(:anomes||'15') end
                              ) as posicao
                              --,date(date(:anomes||'01')+interval '1 month') - 1 as posicao
                            from (
                              select
                                pg.idfilial,
                                pg.idproduto,
                                pg.idgradex,
                                pg.idgradey
                              from rst.produtogradefilial pg
                              left join glb.produto p on p.idproduto = pg.idproduto
                              left join glb.produtocomprador pc on pc.idproduto = pg.idproduto
                              where pg.idfilial = :idfilial
                                and not p.iddepartamento in (select * from saldofin_deptos_ignorar where :anomes >= 201408 )
                              group by pg.idfilial,pg.idproduto,pg.idgradex,pg.idgradey
                              ) tb
                          ) tb
                      ) tb
                  ) tb
                  order by 1
)
,linhas AS (
  select
      'Linhas' as grupo,
      variavel,
      1 as ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      case when pt = (select count(1) from gazin.ccg_gerentes_grupo_comissao where idgrupo <> 99) then comissao + (fixo * peso / 100) else comissao end as comissao
  from(
      select
          tb.*
          ,case when (pontos = 1) then (fixo * peso /100) else 0 end as comissao
          ,sum(pontos) over () as pt
      from (
          select
              tb.*
              ,case when variavel = 'Sem Comissão' then 0 else (select f.faixasalario from gazin.ccg_gerente_faixa_salario f where valortotal between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) end as fixo
          from(
              select
               cast(tb.idgrupo as varchar) as variavel
              ,participacao as participacaometa
              ,case when sum(sf) over (partition by 0) >0 then round((sf / sum(sf) over (partition by 0)) *100,2) else 0 end as participacaorealizada
              ,coalesce(valormeta,0) as valormeta
              ,round(sf,2) as valorrealizado
              ,round(case when tb.valormeta>0 then sf/tb.valormeta * 100 else 0 end,2) as percrealizado
              ,round(case when case when tb.valormeta>0 then sf/tb.valormeta * 100 else 0 end >=100 then 1 else 0 end,2) as pontos
              ,peso
              ,sum(sf) over () as valortotal
              ,case when sum(valormeta) over () > 0 then sum(sf) over () / sum(valormeta) over () * 100 else 0 end perctotal
              from (
                select
                  m.idgrupo
                  ,m.peso
                  ,sum(m.participacao) as participacao
                  ,sum( (select metavenda from metavenda) * m.indice)  as valormeta
                  ,sum(coalesce( ( select sum(sf) from realizado where realizado.idgrupo = m.grupo ) ,0)) as sf
                from metalinha m
                group by 1,2
              ) tb
          )tb
      )tb
  )tb
)
,dias AS (
  select
      'Dias' as grupo,
      variavel,
      1 as ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao
  from(
      select
          tb.*
          ,case when (pontos = 1 or perctotal >= 100) then (fixo * peso /100) else 0 end as comissao
      from(
          select
              tb.*
              ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where valortotal between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
          from(
              select
                 tb.idgrupo as variavel
                ,participacao as participacaometa
                ,round((  case when sum(sf) over (partition by 0) >0 then  sf / sum(sf) over (partition by 0) else 0 end  ) *100,2) as participacaorealizada
                ,coalesce(valormeta,0) as valormeta
                ,round(sf,2) as valorrealizado
                ,round(case when tb.valormeta>0 then sf/tb.valormeta * 100 else 0 end,2) as percrealizado
                ,round(case when case when tb.valormeta>0 then sf/tb.valormeta * 100 else 0 end >=100 then 1 else 0 end,2) as pontos
                ,peso
                ,sum(sf) over () as valortotal
                ,case when sum(valormeta) over () > 0 then sum(sf) over () / sum(valormeta) over () * 100 else 0 end perctotal
              from (
                select
                  idgrupo
                  ,peso
                  ,sum(participacao) as participacao
                  ,sum( (select metavenda from metavenda) * metadia.indice)  as valormeta
                  ,sum(coalesce( ( select sum(sf) from realizado where dia between metadia.dodia and metadia.atedia ) ,0)) as sf
                from metadia
                group by 1,2
              ) tb
          )tb
      )tb
  )tb

  union

  select
      'Dias' as grupo,
      'Bônus ' || round(bo.percmeta,2) ||'%' as variavel,
      2 as ordem,
      0 as participacaometa,
      0 as participacaorealizada,
      0 as valormeta,
      0 as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      coalesce((
          select
            comissao * gb.percbonus / 100
        from(
        select
            max(b.idbonus) as idbonus
        from gazin.ccg_gerente_bonus b
        where b.anomes = :anomes
        and perctotal >= b.percmeta
        ) ttb
        left join gazin.ccg_gerente_bonus gb on gb.idbonus = ttb.idbonus
        where gb.idbonus = bo.idbonus
      ),0) as comissao
  from(
      select
          perctotal,
          sum(case when (pontos = 1 or perctotal >= 100) then (fixo * peso /100) else 0 end) as comissao
      from(
          select
              tb.*
              ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where valortotal between f.dofaturamento and f.atefaturamento and f.anomes = 201504) as fixo
          from(
              select
                 tb.idgrupo as variavel
                ,round(case when case when tb.valormeta>0 then sf/tb.valormeta * 100 else 0 end >=100 then 1 else 0 end,2) as pontos
                ,peso
                ,sum(sf) over () as valortotal
                ,case when sum(valormeta) over () > 0 then sum(sf) over () / sum(valormeta) over () * 100 else 0 end perctotal
              from (
                select
                  idgrupo
                  ,peso
                  ,sum(participacao) as participacao
                  ,sum( (select metavenda from metavenda) * metadia.indice)  as valormeta
                  ,sum(coalesce( ( select sum(sf) from realizado where dia between metadia.dodia and metadia.atedia ) ,0)) as sf
                from metadia
                group by 1,2
              ) tb
          )tb
      )tb
      group by 1
  )tb
  left join gazin.ccg_gerente_bonus bo on bo.anomes = :anomes
)




,vencidoccg as (
  select
    *
  from dblink( 'hostaddr=10.0.253.11 port=6432 dbname=ccg user=ccg password=pst3811'
  ,'select
    idfilial,
    anomes,
    vencido30dias,
    valoravencer30dias,
    valormeta,
    valorrealizado,
    valortotal,
    percrealizado,
    comissao
  from gerente.fiado
  where anomes='||:anomes
    ||' and idfilial ='||:idfilial  ) as bla( idfilial INTEGER,
  anomes INTEGER,
  vencido30dias public.moeda,
  valoravencer30dias public.moeda,
  valormeta public.moeda,
  valorrealizado public.moeda,
  valortotal public.moeda,
  percrealizado public.moeda,
  comissao public.moeda )


)

,perdasreal as (
  select
      case when carteira<>0 then round(((perdido - rec) / carteira) * 100,2) else 0 end as percreal
  from(
    select
      sum(perdido) as perdido,
      sum(rec) as rec,
      sum(carteira) as carteira
    from(
      select
        sum(perdido180) as perdido,
        0 as rec,
        0 as carteira
      from perdas

      union

      select
        0 as perdido,
        sum(recperdido) as rec,
        0 as carteira
      from recperda

      union

      select
        0 as perdido,
        0 as rec,
        sum(valortotal) as carteira
      from vencido
    )tb
  )tb
)

,carteira AS (

  select
      'Carteira' as grupo
      ,'Fiado' as variavel
      ,1 as ordem
      ,0 as participacaometa
      ,round(tb.sf, 2) as participacaorealizada
      ,coalesce(valormeta,0) as valormeta
      ,round(percvencido,2) as valorrealizado
      ,round(case when tb.valormeta>0 then (1 - ((percvencido-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado
      --,round(coalesce(percvencido,0),2) as percrealizado
      ,round(case when percvencido <= valormeta then 1 else 0 end,2) as pontos
      ,case when excecao = 1 then (fixo * peso /100) else case when percvencido <= valormeta then case when peso > 0 then (fixo * peso /100) else 0 end else 0 end end as comissao
  from(
      select
          coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 0 and gmi.idtipometa = 0)
            ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 0 and gm.idtipometa = 0)) as valormeta
          ,coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 0 and gmi.idtipometa = 0)
            ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 0 and gm.idtipometa = 0)) as peso
          ,sum(case when (valorvencido30dias+valoravencer30dias)<>0 then valorvencido30dias/(valorvencido30dias+valoravencer30dias) *100 else 0 end) as percvencido
          ,sum(valortotal) as sf
          ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                  (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
          ,(
            select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 0 limit 1
          ) as excecao
      from vencido
  )tb
  where :anomes < 201407

  union

select
        'Carteira' as grupo
        ,'Fiado' as variavel
        ,1 as ordem
        ,0 as participacaometa
        ,round(tb.sf, 2) as participacaorealizada
        ,coalesce(valormeta,0) as valormeta
        ,round(percvencido,2) as valorrealizado
        ,round(case when tb.valormeta>0 then (1 - ((percvencido-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado
        ,round(case when percvencido <= valormeta then 1 else 0 end,2) as pontos
        ,case when excecao = 1 then (fixo * peso /100) else case when percvencido <= valormeta then case when peso > 0 then (fixo * peso /100) else 0 end else 0 end end as comissao
    from(
        select
             (select metavencido from metarecebimento m where m.idfilial =:idfilial ) as valormeta
            ,coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 0 and gmi.idtipometa = 0)
            ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 0 and gm.idtipometa = 0)) as peso
            ,vencido as percvencido --sum(case when (valorvencido30dias+valoravencer30dias)<>0 then valorvencido30dias/(valorvencido30dias+valoravencer30dias) *100 else 0 end) as percvencido
            ,0 as sf --sum(valortotal) as sf
            ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
            ,( select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 0 limit 1 ) as excecao
        from (
            select
              tb.*
            from (
               select
                sum(case when vencido then valorcontabil else 0 end) as vencido
              from (
                select
                  tp.idfilial,
                  (date(date(  date(:anomes||'01') +interval '1 month') -1) - tp.datavencimento) >= 0 as vencido,
                  --trunc((date(date(  date(:anomes||'01') +interval '1 month') -1) - tp.datavencimento) / 30) as dias,
                  sum(tp.saldocapital + coalesce(tp.saldojuro,0)) as valorcontabil
                from rst.tituloparcela tp
                     left join rst.titulo t on t.idtipotitulo = tp.idtipotitulo
                                               and t.idfilial = tp.idfilial
                                               and t.idtitulo = tp.idtitulo
                where ( date(  date(:anomes||'01') +interval '1 month') -1 < tp.databaixa or tp.databaixa is null )
                    and tp.idfilial = :idfilial
                  and tp.idtipotitulo = 1
                  and t.idprocesso in (select idprocesso from gazin.ccg_gerente_processos_fiado where anomes = :anomes)
                  and date(  date(:anomes||'01') +interval '1 month') -1 >= tp.datainicial
                  and not exists(select 1 from gazin.ccg_historico_recebimento where idfilial = :idfilial and anomes = :anomes  and anomes <> cast( to_char(current_date,'yyyymm') as integer)  )
                  and exists(select metavencido from metarecebimento m where m.idfilial =:idfilial and coalesce(metavencido,0) >0 limit 1)
                group by 1,2
                ) tb
              ) tb
              union
              select
                sum(valorvencido) as vencido
              from gazin.ccg_historico_recebimento r
              where r.anomes = :anomes
                and idfilial = :idfilial
                and exists(select metavencido from metarecebimento m where m.idfilial =:idfilial  and coalesce(metavencido,0) >0  limit 1)
                and exists(select 1 from gazin.ccg_historico_recebimento where idfilial = :idfilial and anomes = :anomes  and anomes <> cast( to_char(current_date,'yyyymm') as integer)  )
        ) tb
        where not vencido is null
    )tb
    where :anomes >= 201407


union
  /* recebimento perdar a partir de 01/02/2016*/
  select
      'Carteira' as grupo
      ,'Rec. Perdido' as variavel
      ,4 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,coalesce(valormeta,0) as valormeta
      ,round(tb.sf,2) as valorrealizado
      ,round(case when tb.valormeta>0 then (1 - ((tb.valormeta-sf)/tb.valormeta)) * 100 else 0 end,2) as percrealizado
      --,round(coalesce(percentualvenda,0),2) as percrealizado
      ,round(case when tb.sf >= valormeta then 1 else 0 end,2) as pontos
      ,case when tb.sf >= valormeta then case when peso > 0 then (fixo * peso /100) else 0 end else 0 end as comissao
  from(
      select
          tb.*
          ,case when valorvenda <> 0 then sf/valorvenda else 0 end * 100 as percentualvenda
      from(
          select
              coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 2 and gmi.idtipometa = 1)
                       ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 2 and gm.idtipometa = 1)) as valormeta
              ,coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 2 and gmi.idtipometa = 1)
                       ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 2 and gm.idtipometa = 1)) as peso
              ,(select sum(sf) from realizado) as valorvenda
              ,sum(recperdido) as sf
              ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                  (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo

          ,(
            select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 3 limit 1
          ) as excecao
          from recperda
      )tb
  )tb

  union

  select
      'Carteira' as grupo
      ,'Juros' as variavel
      ,2 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,round(coalesce(case when valormeta <> 0 then metavenda * valormeta / 100 else 0 end,0),2) as valormeta
      ,round(tb.juros,2) as valorrealizado
      ,round(case when
                  case when valormeta <> 0
                    then metavenda * valormeta / 100 else 0 end >0
                then (1 - ((case when valormeta <> 0 then metavenda * valormeta / 100 else 0 end - juros)/case when valormeta <> 0 then metavenda * valormeta / 100 else 0 end)) * 100
      else 0 end,2) as percrealizado
      ,round(case when tb.juros >= case when valormeta <> 0 then metavenda * valormeta / 100 else 0 end then 1 else 0 end,2) as pontos
      ,case when excecao = 1 then (fixo * peso /100) else case when tb.juros >= case when valormeta <> 0 then metavenda * valormeta / 100 else 0 end then case when peso > 0 then (fixo * peso /100) else 0 end else 0 end end as comissao
  from(
    select
          round(coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 3 and gmi.idtipometa = 0)
                    ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 3 and gm.idtipometa = 0)),2) as valormeta
          ,coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 3 and gmi.idtipometa = 0)
                    ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 3 and gm.idtipometa = 0)) as peso
          ,case when sum(sf)<>0 then (  (sum(cf)-( sum(sf)) ) / sum(sf) ) * 100 else 0 end as percjuros
          ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                  (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
          ,round((select metavenda from metavenda),2) as metavenda
          ,sum(juros) as juros
          ,sum(sf) as sf
          ,(
            select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 3 limit 1
          ) as excecao
    from realizado
  )tb

  union

  select
      'Carteira' as grupo,
    'Perdas Real' as variavel,
    3 as ordem,
    (select sum(recperdido) from recperda) as participacaometa,
    (select sum(perdido180) as perdido from perdas) as participacaorealizada,
    valormeta,
    valorrealizado,
    round(case when tb.valormeta>0 then (1 - ((tb.valorrealizado-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado,
    round(case when valorrealizado <= valormeta then 1 else 0 end,2) as pontos,
    case when excecao = 1 then (fixo * peso /100) else case when valorrealizado <= valormeta then case when peso > 0 then (fixo * peso /100) else 0 end else 0 end end as comissao
  from(
    select
      round(coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 6 and gmi.idtipometa = 0)
                    ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 6 and gm.idtipometa = 0)),2) as valormeta,
      coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 6 and gmi.idtipometa = 0)
                    ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 6 and gm.idtipometa = 0)) as peso,
      coalesce(percreal,0) as valorrealizado,
      (select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                  (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
      ,(
        select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 6 limit 1
      ) as excecao
    from perdasreal
      where :anomes<201602
  )tb

  union

  select
      'Carteira' as grupo
      ,'Desconto em Produtos' as variavel
      ,4 as ordem
      ,0 as participacaometa
      ,round(tb.desconto,2) as participacaorealizada
      ,tb.valormeta
      ,round(tb.percdesconto,2) as valorrealizado
      ,round(case when tb.valormeta>0 then (1 - ((tb.percdesconto-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado
      ,round(case when percdesconto <= valormeta then 1 else 0 end,2) as pontos
      ,case when excecao = 1 then (fixo * peso /100) else case when percdesconto <= valormeta then case when peso > 0 then (fixo * peso /100) else 0 end else 0 end end as comissao
  from(
      select
          peso
          ,fixo
          ,coalesce(valormeta,0) as valormeta
          ,desejado-sf as desconto
          ,excecao
          ,case when sf <> 0 then ((desejado-sf)/sf) else 0 end *100 as percdesconto
      from(
          select
           coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 5 and gmi.idtipometa = 0)
                      ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 5 and gm.idtipometa = 0)) as valormeta
           ,coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 5 and gmi.idtipometa = 0)
                      ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 5 and gm.idtipometa = 0)) as peso
           ,sum(desejado) as desejado
           ,sum(sf) as sf
           ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                  (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
           ,(
             select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 5 limit 1
           ) as excecao
          from realizado
      )tb
  )tb
)
,semgiro as (
     select
        sum(saldofinsg) as valorsemgiro
    from (

      select
        round(case when (tb.posicao - case when tb.ultmov <>'' then date(tb.ultmov) else tb.posicao end ) > dsg.dias then tb.saldo * tb.custom else 0 end,2)  as saldofinsg
      from (

        select
          tb.idfilial as idfilial,
          tb.idproduto,
          tb.idgradex,
          tb.idgradey,
          tb.posicao,
          cast(split_part(tb.saldos,';',1) as numeric) as saldo,
          cast(split_part(tb.saldos,';',2) as numeric) as custo,
          cast(split_part(tb.saldos,';',3) as numeric) as custoent,
          cast(split_part(tb.saldos,';',4) as numeric) as custom,
          cast(split_part(tb.saldos,';',5) as numeric) as customgr,
          case when split_part(tb.saldos,';',6)='' then null else split_part(tb.saldos,';',6) end as ultmov
        from (

        select
            tb.*
            ,(
            select
              round(tb.saldo,6)||';'||round(tb.custo,4)||';'||round(tb.custoent,4)||';'||round(tb.custom,4)||';'||round(tb.customgr,4)||';'||to_char(ultimomovimento,'yyyy-mm-dd')
              from (
                SELECT
                     case when ib.quantidade>0 then ib.totalprecocusto/ib.quantidade else 0 end as custo,
                     case when ib.quantidade>0 then ib.totalcustoentrada/ib.quantidade else 0 end as custoent,
                     case when ib.quantidade>0 then ib.totalcustomedio/ib.quantidade else 0 end as custom ,
                     case when ib.quantidade>0 then ib.totalcustomediogr/ib.quantidade else 0 end as customgr,
                     ib.datamovimento as ultimomovimento,
                     ib.saldo
                  FROM
                     rst.itembase ib
                     left join glb.produto      pd on (ib.idproduto = pd.idproduto)
                     left join glb.produtograde pg on (pg.idproduto = pd.idproduto and pg.idgradex = ib.idgradex and pg.idgradey = ib.idgradey)
                     left join glb.departamento dp on (dp.iddepartamento = pd.iddepartamento)

                  WHERE ib.idfilial = tb.idfilial
                    and ib.datamovimento <= tb.posicao
                    and ib.idoperacaoproduto > 0
                    and ib.idlocalsaldo =1
                    and ib.idproduto = tb.idproduto
                    and ib.idgradex = tb.idgradex
                    and ib.idgradey = tb.idgradey
                  order by ib.idfilial, ib.idproduto, ib.idgradex, ib.idgradey, ib.idlocalsaldo, datamovimento desc, idmovimento desc
                limit 1
              ) tb
            ) as saldos

            from (
              select
              pg.idfilial,
              pg.idproduto,
              pg.idgradex,
              pg.idgradey,
              date(date(:anomes||'01')+interval '1 month') - 1 as posicao
              from glb.produtograde pd
              inner join rst.produtogradefilial pg on (pg.idproduto = pd.idproduto and pg.idgradex = pd.idgradex and pg.idgradey = pd.idgradey)
              where pd.idsituacaoproduto =1
                and pg.idfilial in (:idfilial)
              group by pg.idfilial,pg.idproduto,pg.idgradex,pg.idgradey
            ) tb

        ) tb

      ) tb

      left join glb.produto p on p.idproduto = tb.idproduto
      left join glb.marca m on m.idmarca = p.idmarca
      left join glb.departamento d on d.iddepartamento = p.iddepartamento
      LEFT JOIN gazin.compras_departamento_dias_sem_giro dsg ON dsg.linha = substr(d.classificacao,1,3)

    ) tb
)
,foralinha as (

    select

          sum( cast(split_part(tb.saldos,';',4) as numeric) * cast(split_part(tb.saldos,';',1) as numeric) ) as custom
        from (

        select
            tb.*
            ,(
            select
              round(tb.saldo,6)||';'||round(tb.custo,4)||';'||round(tb.custoent,4)||';'||round(tb.custom,4)||';'||round(tb.customgr,4)||';'||to_char(ultimomovimento,'yyyy-mm-dd')
              from (
                SELECT
                     case when ib.quantidade>0 then ib.totalprecocusto/ib.quantidade else 0 end as custo,
                     case when ib.quantidade>0 then ib.totalcustoentrada/ib.quantidade else 0 end as custoent,
                     case when ib.quantidade>0 then ib.totalcustomedio/ib.quantidade else 0 end as custom ,
                     case when ib.quantidade>0 then ib.totalcustomediogr/ib.quantidade else 0 end as customgr,
                     ib.datamovimento as ultimomovimento,
                     ib.saldo
                  FROM
                     rst.itembase ib
                     left join glb.produto      pd on (ib.idproduto = pd.idproduto)
                     left join glb.produtograde pg on (pg.idproduto = pd.idproduto and pg.idgradex = ib.idgradex and pg.idgradey = ib.idgradey)
                     left join glb.departamento dp on (dp.iddepartamento = pd.iddepartamento)

                  WHERE ib.idfilial = tb.idfilial
                    and ib.datamovimento <= tb.posicao
                    and ib.idoperacaoproduto > 0
                    and ib.idlocalsaldo =1
                    and ib.idproduto = tb.idproduto
                    and ib.idgradex = tb.idgradex
                    and ib.idgradey = tb.idgradey
                  order by ib.idfilial, ib.idproduto, ib.idgradex, ib.idgradey, ib.idlocalsaldo, datamovimento desc, idmovimento desc
                limit 1
              ) tb
            ) as saldos

            from (
              select
              pg.idfilial,
              pg.idproduto,
              pg.idgradex,
              pg.idgradey,
              date(date(:anomes||'01')+interval '1 month') - 1 as posicao
              from glb.produtograde pd
              inner join rst.produtogradefilial pg on (pg.idproduto = pd.idproduto and pg.idgradex = pd.idgradex and pg.idgradey = pd.idgradey)
              where pg.idfilial in (:idfilial)
                and pd.idsituacaoproduto in (2,3)
                and case when pd.ultimaalteracao is not null then pd.ultimaalteracao <= date(date(:anomes||'01')+interval '1 month') - 1 else true end
              group by pg.idfilial,pg.idproduto,pg.idgradex,pg.idgradey
            ) tb

        ) tb
)
,auditoria as (
   select gazin.ccg_get_auditoria_estoque(:idfilial, cast(:anomes as varchar), 1) as nota
)
,estoque AS (

  select
      'Estoque' as grupo
      ,'Total Estoque' as variavel
      ,1 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,valormeta
      ,valorrealizado
      ,case when valorrealizado > 0 then percrealizado else 0 end as percrealizado
      ,pontos
      ,case when excecao = 1 then fixo / 100 * peso else case when pontos = 1 and valorrealizado > 0 then fixo / 100 * peso else 0 end end as comissao
  from(
      select
          coalesce(valormeta,0) as valormeta
          ,round(saldofin,2) as valorrealizado
          ,round(case when tb.valormeta>0 then (1 - ((saldofin-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado
          ,peso
          ,fixo
          ,excecao
          ,round(case when case when tb.valormeta>0 then saldofin/tb.valormeta * 100 else 0 end <=100 then 1 else 0 end,2) as pontos
      from(
          select
             coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 4 and gmi.idtipometa = 1)
             ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 4 and gm.idtipometa = 1)) as valormeta,
             coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 4 and gmi.idtipometa = 1)
              ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 4 and gm.idtipometa = 1)) as peso,
             saldofin,
             (select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                      (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
             ,(
                select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 4 limit 1
             ) as excecao
          from saldofin
      )tb
  )tb

  union

  select
      'Estoque' as grupo
    ,'Produtos S/ Giro' as variavel
    ,2 as ordem
    ,0 as participacaometa
    ,0 as participacaorealizada
    ,valormeta
    ,valorrealizado
    ,case when valorrealizado > 0 then percrealizado else 0 end as percrealizado
    ,pontos
    ,case when excecao = 1 then fixo / 100 * peso else case when pontos = 1 and valorrealizado > 0 then fixo / 100 * peso else 0 end end as comissao
  FROM(
    select
      coalesce(valormeta,0) as valormeta,
      coalesce(valorsemgiro,0) as valorrealizado,
      round(case when tb.valormeta>0 then (1 - ((valorsemgiro-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado,
      peso,
      fixo,
      excecao,
      round(case when case when tb.valormeta>0 then (1 - ((valorsemgiro-tb.valormeta)/tb.valormeta)) * 100 else 0 end >=100 then 1 else 0 end,2) as pontos
    from(
      select
        coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 9 and gmi.idtipometa = 1)
             ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 9 and gm.idtipometa = 1)) as valormeta,
        coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 9 and gmi.idtipometa = 1)
              ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 9 and gm.idtipometa = 1)) as peso,
        valorsemgiro,
        (select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                      (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
        ,(
           select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 9 limit 1
        ) as excecao
      from semgiro
    )tb
  )tb

  union

  select
      'Estoque' as grupo
    ,'Produtos Fora de Linha' as variavel
    ,3 as ordem
    ,0 as participacaometa
    ,0 as participacaorealizada
    ,valormeta
    ,valorrealizado
    ,case when valorrealizado > 0 then percrealizado else 0 end as percrealizado
    ,pontos
    ,case when excecao = 1 then fixo / 100 * peso else case when pontos = 1 and valorrealizado <= valormeta then fixo / 100 * peso else 0 end end as comissao
  FROM(
    select
      coalesce(valormeta,0) as valormeta,
      coalesce(custom,0) as valorrealizado,
      round(case when tb.valormeta>0 then (1 - ((custom-tb.valormeta)/tb.valormeta)) * 100 else 0 end,2) as percrealizado,
      peso,
      fixo,
      excecao,
      round(case when case when tb.valormeta>0 then (1 - ((custom-tb.valormeta)/tb.valormeta)) * 100 else 0 end >=100 then 1 else 0 end,2) as pontos
    from(
      select
        coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 8 and gmi.idtipometa = 1)
             ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 8 and gm.idtipometa = 1)) as valormeta,
        coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 8 and gmi.idtipometa = 1)
              ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 8 and gm.idtipometa = 1)) as peso,
        custom,
        (select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                      (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
        ,(
           select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 8 limit 1
        ) as excecao
      from foralinha
    )tb
  )tb

  union

  select
      'Estoque' as grupo
    ,'Auditoria' as variavel
    ,4 as ordem
    ,0 as participacaometa
    ,0 as participacaorealizada
    ,valormeta
    ,valorrealizado
    ,case when valorrealizado > 0 then percrealizado else 0 end as percrealizado
    ,pontos
    ,case when excecao = 1 then fixo / 100 * peso else case when percrealizado >= 100 then fixo / 100 * peso else 0 end end as comissao
  FROM(
    select
      coalesce(valormeta,0) as valormeta,
      coalesce(nota,0) as valorrealizado,
      round(case when tb.valormeta>0 then (1 - ((tb.valormeta-nota)/tb.valormeta)) * 100 else 0 end,2) as percrealizado,
      peso,
      fixo,
      excecao,
      round(case when nota >= 8 then 1 else 0 end,2) as pontos
    from(
      select
        coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 7 and gmi.idtipometa = 1)
             ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 7 and gm.idtipometa = 1)) as valormeta,
        coalesce((select gmi.peso from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 7 and gmi.idtipometa = 1)
              ,(select gm.peso from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 7 and gm.idtipometa = 1)) as peso,
        nota,
        (select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                      (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo,
        (
           select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 7 limit 1
        ) as excecao
      from auditoria
    )tb
  )tb
)
,reembolsomargem as (
    select
        gr.valor
    from gazin.ccg_gerente_reembolsomargem gr
    where gr.anomes = :anomes
    and gr.uf = (
        select
            ci.uf
        from glb.filial fl
            left join glb.endereco ed on ed.idcnpj_cpf = fl.idcnpj_cpf and ed.idtipoendereco = 1
            left join glb.cidade ci on ci.idcidade = ed.idcidade
        where fl.idfilial = :idfilial)
)
,margem AS (
  select
      'Margem' as grupo
      ,'Faturamento Bruto' as variavel
      ,1 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,round(coalesce(case when metajuros <> 0 then metavenda * metajuros / 100 else 0 end + metavenda,0),2) as valormeta
      ,round(faturamento,2) as valorrealizado
      ,round(case when case when metajuros <> 0 then metavenda * metajuros / 100 else 0 end + metavenda <> 0 then faturamento/(case when metajuros <> 0 then metavenda * metajuros / 100 else 0 end + metavenda) * 100 else 0 end,2) as percrealizado
      ,0 as pontos
      ,0 as comissao
    FROM(
      select
          coalesce((select metavenda from metavenda),0) as metavenda
          ,round((select gmi.valormeta as valormeta from gazin.ccg_gerente_metas_individual gmi
                      where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 3 and gmi.idtipometa = 0),2) as metajuros
          ,sum(sf) as sf
          ,sum(juros + sf) as faturamento
      from
          realizado
    )tb

    UNION

      SELECT
       'Margem' as grupo
       ,'Faturamento S/ Financeiro' as variavel
       ,2 as ordem
       ,0 as participacaometa
       ,0 as participacaorealizada
       ,coalesce(metavenda,0) as valormeta
       ,round(vendasf,2) as valorrealizado
       ,round(case when tb.metavenda>0 then vendasf/tb.metavenda * 100 else 0 end,2) as percrealizado
       ,round(case when case when tb.metavenda>0 then vendasf/tb.metavenda * 100 else 0 end >=100 then 1 else 0 end,2) as pontos
       ,0 as comissao
      FROM(
        SELECT
            (select metavenda from metavenda) as metavenda
            ,sum(sf) as vendasf
        from
            realizado
      )tb

    union

    SELECT
     'Margem' as grupo
     ,'Custo Mercadoria Vendida' as variavel
     ,3 as ordem
     ,0 as participacaometa
     ,0 as participacaorealizada
     ,0 as valormeta
     ,round(cm,2) * -1 as valorrealizado
     ,0 as percrealizado
     ,0 as pontos
      ,0 as comissao
    FROM(
      SELECT
        sum(case when idgrupo = 4 then cm+(cm * /*(case when uf in ('MS', 'PR') and anomes>= 201311 then 45 * 1.0 else 70 * 1.0 end/100)*/
            (select COALESCE(valor,0) from reembolsomargem) / 100
        ) else cm end) as cm
      from
          realizado
    )tb

    UNION

    select
      'Margem' as grupo
      ,'Impostos' as variavel
      ,4 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,0 as valormeta
      ,round(imposto ,2) * -1 as valorrealizado
      ,0 as percrealizado
      ,0 as pontos
      ,0 as comissao
    FROM(
      select
          sum(pis+cofins+icms) as imposto
      from
          realizado
    )tb

      UNION

    select
      'Margem' as grupo
      ,'Gratificações C/ Encargos' as variavel
      ,5 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,0 as valormeta
      ,round(sum(valorrealizado),2) * -1 as valorrealizado
      ,0 as percrealizado
      ,0 as pontos
      ,0 as comissao
    FROM(
      select
          case
                when idgrupo = 1 then ((sf * 1.50 /100)* 1.65)
                when idgrupo = 2 then ((sf * 4.00 /100)* 1.65)
                when idgrupo = 3 then ((sf * 5.00 /100)* 1.65)
                when idgrupo = 4 then ((sf * 5.00 /100)* 1.65)
                when idgrupo = 5 then ((sf * 1.50 /100)* 1.65)
          end as valorrealizado
      from
          realizado r
          --left join gazin.ccg_gerente_grupo_comissao_linha gcl on gcl.linhas = linha --and gcl.anomes = r.anomes
    )tb

    union

    select
      'Margem' as grupo
      ,'Vendas Fora de Linha' as variavel
      ,6 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,valormeta
      ,coalesce(valorrealizado,0) as valorrealizado
      ,round(case when tb.valormeta>0 then (1 - ((tb.valormeta-valorrealizado)/tb.valormeta)) * 100 else 0 end,2) as percrealizado
      ,0 as pontos
      ,0 as comissao
    from(
    select
      coalesce((select gmi.valormeta from gazin.ccg_gerente_metas_individual gmi where gmi.idfilial = :idfilial and gmi.anomes = cast(:anomes as integer) and gmi.idmeta = 10 and gmi.idtipometa = 1)
             ,(select gm.valormeta from gazin.ccg_gerente_metas gm where gm.anomes = cast(:anomes as integer) and gm.idmeta = 10 and gm.idtipometa = 1)) as valormeta
      ,round(sum(cf),2) as valorrealizado
    FROM
       realizado r
    where r.idsituacaoproduto = 3
    )tb

    union

    select
      'Margem' as grupo
      ,'*' as variavel
      ,7 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,0 as valormeta
      ,0 as valorrealizado
      ,0 as percrealizado
      ,0 as pontos
      ,0 as comissao

    union

  select
      'Margem' as grupo
      ,descricao as variavel
      ,8 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,coalesce(metagrupo, 0) as valormeta
      ,round(margem,2) as valorrealizado
      ,round( case when cf>0 then margem/cf else 0 end *100,2) as percrealizado
      ,round(case when cf>0 and (margem/cf*100) >= metagrupo then 1 else 0 end,2) as pontos
      ,0 as comissao
  from(
    select
        descricao
        ,metagrupo
        ,cf
        ,cftotal
        ,(cf - impostos - cm - encargos) as margem
        ,cftotal-cmtotal-pistotal-cofinstotal-icmstotal-sum(encargos) over () as margemtotal
    from(
      select
        descricao
        ,metagrupo
        ,sf
        ,cf
        ,pis
        ,cofins
        ,icms
        ,case when idgrupo = 4 then cm+(cm * /*(case when uf in ('MS', 'PR') and anomes>= 201311 then 45 * 1.0 else 70 * 1.0 end/100) */
            COALESCE((select valor from reembolsomargem),0) / 100
        ) else cm end cm
        ,(pis+cofins+icms) as impostos
        ,case
              when idgrupo = 1 then ((sf * 1.50 /100)* 1.65)
              when idgrupo = 2 then ((sf * 4.00 /100)* 1.65)
              when idgrupo = 3 then ((sf * 5.00 /100)* 1.65)
              when idgrupo = 4 then ((sf * 5.00 /100)* 1.65)
              when idgrupo = 5 then ((sf * 1.50 /100)* 1.65)
        end as encargos
        ,sum(sf) over () as sftotal
        ,sum(cf) over () as cftotal
        ,sum(case when idgrupo = 4 then cm+(cm * /*(case when uf in ('MS', 'PR') and anomes>= 201311 then 45 else 70 end/100)*/
            COALESCE((select valor from reembolsomargem),0) / 10
        ) else cm end) over () as cmtotal
        ,sum(pis) over () as pistotal
        ,sum(cofins) over () as cofinstotal
        ,sum(icms) over () as icmstotal
      from(
        select
            r.idgrupo
            ,ggc.descricao
            ,r.anomes
            ,r.uf
            ,(select gmm.valormeta from gazin.ccg_gerente_metamargem_individual gmm where gmm.idfilial = :idfilial and gmm.anomes = :anomes and gmm.idgrupo = r.idgrupo) as metagrupo
            ,sum(r.sf) as sf
            ,sum(r.cf) as cf
            ,sum(r.cm) as cm
            ,sum(r.pis) as pis
            ,sum(r.cofins) as cofins
            ,sum(r.icms) as icms
        from realizado r
             --left join gazin.ccg_gerente_grupo_comissao_linha gcl on gcl.linhas = linha --and gcl.anomes = r.anomes
             left join gazin.ccg_gerentes_grupo_comissao ggc on ggc.idgrupo = r.idgrupo
        where r.idgrupo is not null
        group by 1,2,3,4,5
      )tb
    )tb
    order by 1
  )tb

union


  select
      'Margem' as grupo
      ,'Margem' as variavel
      ,9 as ordem
      ,0 as participacaometa
      ,0 as participacaorealizada
      ,coalesce(meta, 0) as valormeta
      ,round(margem,2) as valorrealizado
      ,round(  case when cf >0 then margem/cf*100 else 0 end ,2) as percrealizado
      ,round(case when cf>0 and (margem/cf*100) >= meta then 1 else 0 end,2) as pontos
      ,case when excecao = 1 then (fixo * peso / 100) else case when cf >0 and (margem/cf*100) >= meta then (fixo * peso /100) else 0 end end as comissao
  from(
    select
        meta
        ,excecao
        ,cf
        ,(cf + foralinha - impostos - cm - encargos) as margem
        ,peso
        ,fixo
    from(
      select
        meta
        ,peso
        ,excecao
        ,(select f.faixasalario from gazin.ccg_gerente_faixa_salario f where
                    (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes) as fixo
        ,sum(sf) as sf
        ,sum(cf) as cf
        ,sum(case when idgrupo = 4 then cm+(cm * /*(case when uf in ('MS', 'PR') and anomes>= 201311 then 45 * 1.0 else 70 * 1.0 end/100)*/
            (select valor from reembolsomargem) / 100
        ) else cm end) as cm
        ,sum(pis)+sum(cofins)+sum(icms) as impostos
        ,sum(case
              when idgrupo = 1 then ((sf * 1.50 /100)* 1.65)
              when idgrupo = 2 then ((sf * 4.00 /100)* 1.65)
              when idgrupo = 3 then ((sf * 5.00 /100)* 1.65)
              when idgrupo = 4 then ((sf * 5.00 /100)* 1.65)
              when idgrupo = 5 then ((sf * 1.50 /100)* 1.65)
        end) as encargos,
        coalesce((select sum(cf) from realizado where idsituacaoproduto = 3),0) as foralinha
      from(
        select
            r.idgrupo
            ,r.anomes
            ,r.uf
            ,(select gmm.valormeta from gazin.ccg_gerente_metamargem_geral gmm where gmm.idfilial = :idfilial and gmm.anomes = :anomes) as meta
            ,(select gmm.peso from gazin.ccg_gerente_metamargem_geral gmm where gmm.idfilial = :idfilial and gmm.anomes = :anomes) as peso
            ,sum(r.sf) as sf
            ,sum(r.cf) as cf
            ,sum(r.cm) as cm
            ,sum(r.pis) as pis
            ,sum(r.cofins) as cofins
            ,sum(r.icms) as icms
            ,(
                select 1 from excecao where idfilial = :idfilial and anomes = :anomes and idmeta = 11 limit 1
            ) as excecao
        from realizado r
             --left join gazin.ccg_gerentes_grupo_comissao gc on gc.idgrupo = linha --and gcl.anomes = r.anomes
        group by 1,2,3,4,5
      )tb
      group by 1,2,3,4
    )tb
    order by 1
  )tb
)
,grupocomissao AS (
    select
        scg.*
        ,gs.descricao as descgruposervico
        ,sgc.descricao as descgrupocomissao
        ,s.descricao as descservico
    from
        gazin.ccg_servico_comissao_grupo scg
        left join gazin.ccg_grupo_servico gs on gs.idgruposervico = scg.idgruposervico
        left join gazin.ccg_servico_grupo_comissao sgc on sgc.idgrupo = scg.idgrupo
        left join glb.servico s on s.idservico = scg.idservico
    where scg.anomes = :anomes
)
,aproveitamento AS (
    select
        s.ididentificador
        ,gc.idgruposervico
        ,deaproveitamento
          ,ateaproveitamento
          ,faixacomissao
        ,faixagarantia
    FROM gazin.ccg_servico_aproveitamento s
         left join grupocomissao gc on gc.ididentificador = s.ididentificador
)
,vendas AS (
        select
 tb.idfilial
,tb.idvendedor
,to_char(tb.datamovimento, 'yyyymm') as anomes
,tb.idpromocao
,tb.idtipopessoa
,tb.linha
,tb.sublinha
,tb.quantidade
,tb.totalpresente
,tb.totalreembolso
,tb.reembolsopromocao
,tb.totalfuturo
,tb.totalcustomedio
,tb.impostos
,tb.comissao
,tb.totalprazos
,tb.valortitulos
,case when tb.totalpresente <>0 then 100-( tb.totalcustomedio / tb.totalpresente *100 )  else 0 end as rentabilidade_sf
,case when tb.totalfuturo <>0 then 100-( tb.totalcustomedio / tb.totalfuturo *100 )  else 0 end as rentabilidade_cf
,tb.totalfuturo - tb.totalpresente as financeiro
,case when tb.totalpresente <>0 then ( (tb.totalfuturo - tb.totalpresente) /tb.totalpresente*100  )  else 0 end as perc_financeiro
,case when tb.totalprazos<>0 then tb.valortitulos/ tb.totalprazos else 0 end as prazomedio
,0 as idproduto
,tb.totaldesejadopresente
,elegivelgarantia
,idsituacaoproduto
from (
select

 tb.idfilial
,tb.idvendedor
,tb.datamovimento
,tb.idpromocao
,tb.idtipopessoa
,tb.linha
,tb.sublinha
,tb.elegivelgarantia
,tb.idsituacaoproduto
,sum(tb.quantidade) as quantidade
,sum(tb.totalpresente) as totalpresente
,sum(tb.totalreembolso) as totalreembolso
,sum(tb.reembolsopromocao) as reembolsopromocao
,sum(tb.totalfuturo) as totalfuturo
,sum(tb.totalcustomedio) as totalcustomedio
,sum(tb.impostos) as impostos
,sum(tb.comissao) as comissao
,sum(cast( split_part( tb.prazomedio,';',1 ) as numeric)) as totalprazos
,sum(cast( split_part( tb.prazomedio,';',2 ) as numeric)) as valortitulos
,sum(tb.totaldesejadopresente) as totaldesejadopresente
from (

select
 ib.idfilial
,coalesce(ib.idvendedor,0) as idvendedor
,ib.datamovimento
,(coalesce(ib.idpromocao,0)) as idpromocao
,coalesce(pes.idtipopessoa,0) as idtipopessoa
,substr(d.classificacao,1,3) as linha
,substr(d.classificacao,1,5) as sublinha
--and not COALESCE(i.idprocessomestre,1) in (9657 ,9658, 9659 /*, 9695,9696,9689*/ )  -- conta como venda
,case when ib.idprocessomestre in ( 9657 ,9658, 9659,9678, 9679, 9685, 9695,9696,9689 ) then 0 else 1 end as elegivelgarantia  /* vendas recuperado   9657 ,9658, 9659,9678, 9679, 9685,9695,9689,9696*/
,pg.idsituacaoproduto
--,ib.idproduto
,ib.quantidade * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as quantidade
,ib.totalpresente * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as totalpresente
,ib.totalreembolso * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as totalreembolso
,ib.reembolsopromocao * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as reembolsopromocao
,ib.totalfuturo * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as totalfuturo
,ib.totalcustomedio * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as totalcustomedio

,(ib.totalcofins + ib.totalpis) * case when ib.idoperacaoproduto = 101040 then -1 else 1 end +
                  (ib.totalbasecalculoicms * ib.aliquotaicms/100) * case when ib.idoperacaoproduto = 101040 then -1 else 1 end +
                  (ib.totalitem * ib.aliquotaipi/100) * case when ib.idoperacaoproduto = 101040 then -1 else 1 end
 AS impostos



,case when ib.totalpresente<>0 and ((ib.idpromocao is null) or (ib.percentualcomissao = -1) or (coalesce(v.idgrupo,0)<>0)) then
           public.calcular_percentualcomissaovendas(case when v.idgrupo > 0
                                                         then v.idgrupo
                                                         else gf.idgrupo
                                                    end,
                                                    ib.datamovimento,
                                                    d.classificacao,
                                                    ib.idproduto,
                                                    n.idprocessomestre,
                                                    round( (100-( case when ib.idoperacaoproduto = 102010
                                                                       then ib.totalcustomedio
                                                                       else ib.totalcustoentrada
                                                                  end  / ib.totalpresente * 100)),2 ) )
      else ib.percentualcomissao end/100 * ib.totalpresente * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as comissao

 ,(

                              select
                              sum(  ( ( case when (select sum( ib2.totalitem ) from rst.itembase ib2 where ib2.idfilial = ib.idfilial and ib2.idpedidovenda = ib.idpedidovenda )<> 0 then ib.totalitem / (select sum( ib2.totalitem ) from rst.itembase ib2 where ib2.idfilial = ib.idfilial and ib2.idpedidovenda = ib.idpedidovenda )  else 0 end          ) * tp.valorcontabil )  )

                              ||';'||sum(  ( case when (select sum( ib2.totalitem ) from rst.itembase ib2 where ib2.idfilial = ib.idfilial and ib2.idpedidovenda = ib.idpedidovenda ) <>0 then  ib.totalitem/  (select sum( ib2.totalitem ) from rst.itembase ib2 where ib2.idfilial = ib.idfilial and ib2.idpedidovenda = ib.idpedidovenda )   else 0 end                 * tp.valorcontabil)* (tp.datavencimento - t.datainclusao)  )


                              from rst.notatitulo nt
                              left join rst.tituloparcela tp on tp.idtipotitulo = nt.idtipotitulo
                                                            and tp.idfilial = nt.idfilial
                                                            and tp.idtitulo = nt.idtitulo

                              left join rst.titulo t on t.idtipotitulo = nt.idtipotitulo
                                                     and t.idfilial = nt.idfilial
                                                     and t.idtitulo = nt.idtitulo

                              where nt.idtipotitulo =1
                                and nt.idfilial = ib.idfilial
                                and nt.idregistronota = ib.idregistronota
                                and t.idtipocarteira <> 7
                                and tp.idparcialtitulo = 0
                               )  as prazomedio
,ib.totaldesejadopresente * case when ib.idoperacaoproduto = 101040 then -1 else 1 end as totaldesejadopresente

from rst.itembase ib
left join glb.pessoa pes on pes.idcnpj_cpf = ib.idcnpj_cpf
left join glb.vendedor v on v.idvendedor = ib.idvendedor
LEFT JOIN glb.grupofilial gf on ib.idfilialorigem=gf.idfilial and gf.idtipogrupo= 10
left join rst.nota n on n.idfilial = ib.idfilial and n.idregistronota = ib.idregistronota
left join glb.produto p on p.idproduto = ib.idproduto
left join glb.produtograde pg on pg.idproduto = ib.idproduto and pg.idgradex = ib.idgradex and pg.idgradey = ib.idgradey
left join glb.departamento d on d.iddepartamento = p.iddepartamento
where ib.idfilial = :idfilial
  and ib.datamovimento between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
  and ib.idoperacaoproduto in ( 102010,101040 )

  ) tb
  group by 1,2,3,4,5,6,7,8,9
) tb
order by 1,2,3,4,5
),
elegivelgarantia AS (
    select
      v.idfilial
      ,idvendedor
      ,anomes
      ,sum(v.totalpresente) as elegivel
    from vendas v
    where v.anomes = :anomes
          and v.idfilial = :idfilial
          and not v.linha between '150' and '150999999'
          and not v.linha between '119' and '119999999'
          and v.idtipopessoa = 1
          and coalesce(v.elegivelgarantia,0) = 1
    group by 1,2,3
),
vendaservico AS (
    select
       its.idfilial
      ,its.idvendedor
      ,to_char(its.datamovimento, 'yyyymm') as anomes
      ,s.idgruposervico
      ,its.idservico
      ,sum( case when its.idoperacaoservico in (201020) then -1 else case when its.idoperacaoservico = 201025 then 0 else 1 end end * ( its.percentualcomissao/100 * its.totalpresente)) as comissao
      ,sum( case when its.idoperacaoservico in (201020) then -1 else case when its.idoperacaoservico = 201025 then 0 else 1 end  end ) as servico_vendido
      ,sum( its.totalpresente * case when its.idoperacaoservico in (201020)
                                     then -1
                                     else case when its.idoperacaoservico = 201025
                                               then 0
                                               else 1
                                           end
                               end ) as servico_presente
      ,sum( its.totalfuturo   * case when its.idoperacaoservico in (201020) then -1 else case when its.idoperacaoservico = 201025 then 0 else 1 end  end)   as servico_futuro

      ,sum( its.totalpresente   * case when its.idoperacaoservico = 201025 then 1 else 0 end )   as perdas

    from rst.itemservico its
    left join glb.servico s on s.idservico = its.idservico
    left join rst.itembase i on (i.idfilial=its.idfilial and i.iditembase=its.iditembase)
    where its.idfilial = :idfilial
      and its.datamovimento between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
      and its.idoperacaoservico in ( 201020,201025,202010,202020 )
      and ( s.idgruposervico != 1 or not its.idprocessomestre in ( 9657 ,9658, 9659,9678, 9679, 9685, 9695,9696,9689 ) )
    group by 1,2 ,3 ,4 , 5
),
elegivelprotecao AS (
    select
  tb.idfilial
 --,tb.idvendedor
 ,to_char(tb.datamovimento, 'yyyymm') as anomes
 ,2 as idgruposervico
 ,sum(elegivel) - sum(elegivelcancelado) as elegivel

 from (

    select
      pv.idfilial
      ,pv.idvendedor
      ,t.datainclusao as datamovimento
      ,pv.idpedidovenda
      ,1 as elegivel
      ,0 as elegivelcancelado
      from rst.pedidovenda pv
      left join rst.titulopedidovenda nt on nt.idfilial = pv.idfilial
                               and nt.idpedidovenda = pv.idpedidovenda
      left join rst.titulo t on t.idtipotitulo = nt.idtipotitulo
                            and t.idfilial = nt.idfilial
                            and t.idtitulo = nt.idtitulo
      where pv.idfilial = :idfilial
          and (t.datainclusao between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1 )
        and exists( select 1
                  from glb.servico s
                  where s.idgruposervico =2
                  and public.validar_venda_servico( s.idservico,
                          t.idcnpj_cpf,
                          t.totalvalorcontabil,
                          t.quantidadeparcelas,
                          t.datainclusao,
                          pv.idprocesso ))

    group by 1,2,3, 4

    union

    select
          pv.idfilial
          ,pv.idvendedor
          ,pv.datacancelamento as datamovimento
          ,pv.idpedidovenda
          ,0 as elegivel
          ,1 as elegivelcancelado

          from rst.pedidovenda pv
          left join rst.titulopedidovenda nt on nt.idfilial = pv.idfilial
                                   and nt.idpedidovenda = pv.idpedidovenda
          left join rst.titulo t on t.idtipotitulo = nt.idtipotitulo
                                and t.idfilial = nt.idfilial
                                and t.idtitulo = nt.idtitulo
          left join glb.servico s on true and s.idgruposervico = 2
          where pv.idfilial = :idfilial
              and pv.idsituacaopedidovenda = 5
            and (pv.datacancelamento between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1  )
            and exists( select 1
                      from glb.servico s
                      where s.idgruposervico = 2
                      and public.validar_venda_servico( s.idservico,
                              t.idcnpj_cpf,
                              t.totalvalorcontabil,
                              t.quantidadeparcelas,
                              pv.databaixa,
                              pv.idprocesso ))
        group by 1,2,3,4

) tb


group by 1,2

order by 1,2
)
,seguros AS (
    select
        sum(valor) as valor
    from gazin.segurosparanatec s
    where s.idfilial = :idfilial
          and s.datamovimento between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
)
,consorcio AS (
    select
        tiposervico
        ,sum(valor) as valor
    from
    (select
         s.tiposervico
         ,sum(s.valor) as valor
        from gazin.gzvendasparanatec s
        where s.idfilial = :idfilial
              and s.datamovimento between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
        group by 1

    union

    select
        idtipoconsorcio as tiposervico,
        0 as valor
    from gazin.gztipoconsorcio

    )tb
    group by 1
)
,garantia AS (
    select
        gc.descgrupocomissao as grupo
        ,gc.descgruposervico as variavel
        ,1 as ordem
        ,tb.idgruposervico
        ,meta
        ,qtd
        ,vendas
        ,elegivel
        ,aprov as percrealizado
        ,faixacomissao
        ,round(case when faixacomissao <> 0 then vendas * faixacomissao/100 else 0 end,2) comissao
        ,case when aprov >= meta then 1 else 0 end pontos
    from(
        select
            tb.idgruposervico
            ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,tb.idgruposervico)) as meta
            ,qtd
            ,vendas
            ,elegivel
            ,round(case when elegivel <> 0 then (vendas*1.0/elegivel*1.0) *100 else 0 end,2) as aprov

        from(
            select
            --    cast(idgruposervico as integer) as
                idgruposervico,
                sum(qtd) as qtd,
                sum(vendas) as vendas,
                sum(elegivel) as elegivel
            from(
                select
                    idvendedor,
                    idgruposervico,
                    sum(qtd) as qtd,
                    sum(vendas) as vendas,
                    sum(elegivel) as elegivel
                from(
                    select
                        idvendedor,
                        1 as idgruposervico,
                        0 as qtd,
                        0 as vendas,
                        sum(elegivel) as elegivel
                    from elegivelgarantia
                    group by 1,2

                    union

                    select
                        idvendedor,
                        vs.idgruposervico,
                        sum(vs.servico_vendido) as qtd,
                        sum(vs.servico_presente),
                        0 as elegivel
                    from vendaservico vs
                    where vs.idgruposervico = 1
                    group by 1,2
                )tb
                group by 1,2
            )tb
            --where vendas > 0
            group by 1
          )tb
    )tb
    left join aproveitamento a on a.idgruposervico = tb.idgruposervico and tb.aprov between deaproveitamento and ateaproveitamento
    left join grupocomissao gc on gc.idgruposervico = tb.idgruposervico
)
,protecao as (
select
    gc.descgrupocomissao as grupo
    ,cast('Proteção' as varchar) as variavel
    ,2 as ordem
    ,tb.idgruposervico
    ,meta
    ,qtd
    ,vendas
    ,elegivel
    ,aprov
    ,faixacomissao
    ,aprov as percrealizado
    ,aprovgarantia
    ,round(faixacomissao*qtd,2) comissao
    ,case when qtd >= meta then 1 else 0 end pontos
from(
    select
        idgruposervico
        ,meta
        ,qtd
        ,vendas
        ,elegivel
        ,round(case when elegivel <> 0 then qtd/elegivel *100 else 0 end,2) as aprov
        ,(select faixacomissao from garantia) as aprovgarantia
    from(
        select
            vs.idgruposervico
            ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,vs.idgruposervico)) as meta
            ,sum(vs.servico_vendido) as qtd
            ,sum(vs.servico_presente) as vendas
            ,round((select sum(elegivel) from elegivelprotecao),2) as elegivel
        from
            vendaservico vs
        where vs.idgruposervico in (2)
        group by 1,2
    )tb
)tb
left join aproveitamento a on a.idgruposervico = tb.idgruposervico and case when meta <> 0 then aprov * 100 / meta else 0 end between deaproveitamento and ateaproveitamento and a.faixagarantia = aprovgarantia
left join grupocomissao gc on gc.idgruposervico = tb.idgruposervico
)
,independentes AS (
    select
        grupo
        ,cast('Premiado - 1,99' as varchar) as variavel
        ,1 as ordem
        ,tb.idservico
        ,meta
        ,qtd
        ,vendas
        ,faixacomissao
        ,percrealizado
        ,basecomissao
        ,round(case when faixacomissao <> 0 then basecomissao * faixacomissao/100 else 0 end,2) comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            gc.descgrupocomissao as grupo
            ,tb.idservico
            ,meta
            --,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,tb.idservico)) as meta
            ,qtd
            ,vendas
            ,case when meta <> 0 then qtd * 100 / meta else 0 end as percrealizado
            ,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador
                    and (case when meta <> 0 then qtd * 100 / meta else 0 end between deaproveitamento and ateaproveitamento) ) as faixacomissao
            --,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador) as faixacomissao
            ,(garantia+protecao) as basecomissao
        from(
            select
                234 as idservico
                ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,14,234)) *
                    (select vend from vendedor) as meta
            ,coalesce((select sum(comissao) from garantia),0) as garantia
            ,coalesce((select sum(comissao) from protecao),0) as protecao
                ,sum(vs.servico_vendido) as qtd
                ,sum(vs.servico_presente) as vendas
            from
                vendaservico vs
            where vs.idservico in (114,234)
            group by 1
        )tb
        left join grupocomissao gc on gc.idservico = tb.idservico
    )tb

    union

    select
        grupo
        ,cast('Premiado - 4,99' as varchar) as variavel
        ,2 as ordem
        ,tb.idservico
        ,meta
        ,qtd
        ,vendas
        ,faixacomissao
        ,percrealizado
        ,basecomissao
        ,round(case when faixacomissao <> 0 then basecomissao * faixacomissao/100 else 0 end,2) comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            gc.descgrupocomissao as grupo
            ,tb.idservico
            ,meta
            --,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,tb.idservico)) as meta
            ,qtd
            ,vendas
            ,case when meta <> 0 then qtd * 100 / meta else 0 end as percrealizado
            ,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador
                    and (case when meta <> 0 then qtd * 100 / meta else 0 end between deaproveitamento and ateaproveitamento) ) as faixacomissao
            --,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador) as faixacomissao
            ,(garantia+protecao) as basecomissao
        from(
            select
                199 as idservico
                ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,14,235)) *
                    (select vend from vendedor) as meta
            ,coalesce((select sum(comissao) from garantia),0) as garantia
            ,coalesce((select sum(comissao) from protecao),0) as protecao
                ,sum(vs.servico_vendido) as qtd
                ,sum(vs.servico_presente) as vendas
            from
                vendaservico vs
            where vs.idservico in (199,235)
            group by 1
        )tb
        left join grupocomissao gc on gc.idservico = tb.idservico
    )tb

    union

    select
        grupo
        ,cast(variavel as varchar) as variavel
        ,3 as ordem
        ,tb.idgruposervico
        ,meta
        ,qtd
        ,vendas
        ,faixacomissao
        ,percrealizado
        ,basecomissao
        ,round(case when faixacomissao <> 0 then basecomissao * faixacomissao/100 else 0 end,2) comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            gc.descgrupocomissao as grupo
            ,'Residencial' as variavel
            ,tb.idgruposervico
            ,meta
            --,(select gazin.ccg_get_meta_gruposervico_filial(:idfilial,:anomes,tb.idgruposervico)) as meta
            ,qtd
            ,vendas
            ,case when meta > 0 then qtd * 100 / meta else 0 end as percrealizado
            ,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador
                    and (case when meta <> 0 then qtd * 100 / meta else 0 end between deaproveitamento and ateaproveitamento) ) as faixacomissao
            ,(garantia+protecao) as basecomissao
            --,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador) as faixacomissao
            --,(garantia+protecao) as basecomissao
        from(
            select
                vs.idgruposervico
                ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,vs.idgruposervico)) *
                    (select vend from vendedor) as meta
                ,coalesce((select sum(comissao) from garantia),0) as garantia
            ,coalesce((select sum(comissao) from protecao),0) as protecao
                ,sum(vs.servico_vendido) as qtd
                ,sum(vs.servico_presente) as vendas
            from
                vendaservico vs
            where vs.idgruposervico in (3)
            group by 1
        )tb
        left join grupocomissao gc on gc.idgruposervico = tb.idgruposervico
    )tb
    where qtd > 0

    union

    select
        grupo
        ,cast(variavel as varchar) as variavel
        ,4 as ordem
        ,tb.idgruposervico
        ,meta
        ,0 as qtd
        ,vendas
        ,faixacomissao
        ,percrealizado
        ,basecomissao
        ,round(case when faixacomissao <> 0 then basecomissao * faixacomissao/100 else 0 end,2) comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            gc.descgrupocomissao as grupo
            ,gc.descgruposervico as variavel
            ,tb.idgruposervico
            ,meta
            ,vendas
            ,case when meta <> 0 then vendas * 100 / meta else 0 end as percrealizado
            ,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador
                    and (case when meta <> 0 then vendas * 100 / meta else 0 end between deaproveitamento and ateaproveitamento) ) as faixacomissao
            ,(garantia+protecao) as basecomissao
        from(
            select
                500 as idgruposervico
                ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,500)) *
                    (select vend from vendedor) as meta
            ,coalesce((select sum(comissao) from garantia),0) as garantia
            ,coalesce((select sum(comissao) from protecao),0) as protecao
            ,sum(vs.valor) as vendas
            from
                seguros vs
            group by 1
        )tb
        left join grupocomissao gc on gc.idgruposervico = tb.idgruposervico
        where meta>0
    )tb


    union


    select
        grupo
        ,variavel
        ,5 as ordem
        ,tb.idgruposervico
        ,meta
        ,0 as qtd
        ,vendas
        ,null as faixacomissao
        ,percrealizado
        ,null as basecomissao
        ,case when vendas >= meta
            then ( ( (0.25/100) * coalesce(vendas,0) ) + 100 )
            else ( (0.25/100) * coalesce(vendas,0) )
          end as comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            'Servicos Independentes' as grupo
            ,'GazinCred' as variavel
            ,tb.idgruposervico
            ,meta
            ,vendas
            ,case when meta <> 0 then vendas * 100 / meta else 0 end as percrealizado
            ,case when (select gazin.ccg_get_vendedor_filial(:idfilial,:anomes)) <> 0
              then
                 case
                   when (((select vend from vendedor)*1.0) / (select gazin.ccg_get_vendedor_filial(:idfilial,:anomes))) < (select vend from vendedor)
                   then null
                   else 100
                 end
              else 0 end as comissao
        from(
            select
              501 as idgruposervico
              ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,501)) * (select vend from vendedor) as meta
              ,sum(vs.valor) as vendas
            from (
                select
                    sum(valorliberado) as valor
                from gazin.gzmovimentoge s
                where s.idfilial = :idfilial
                      and s.dataefetiva between date(:anomes||'01') and date(  date(:anomes||'01') +interval '1 month') -1
                ) vs
        )tb
        where date(:anomes||'01') >= date('2014-07-01')
    )tb

    union

    select
        grupo
        ,cast('Premiado - 36,90' as varchar) as variavel
        ,6 as ordem
        ,tb.idservico
        ,meta
        ,qtd
        ,vendas
        ,faixacomissao
        ,percrealizado
        ,basecomissao
        ,round(case when faixacomissao <> 0 then basecomissao * faixacomissao/100 else 0 end,2) comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            gc.descgrupocomissao as grupo
            ,tb.idservico
            ,meta
            --,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,tb.idservico)) as meta
            ,qtd
            ,vendas
            ,case when meta <> 0 then qtd * 100 / meta else 0 end as percrealizado
            ,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador
                    and (case when meta <> 0 then qtd * 100 / meta else 0 end between deaproveitamento and ateaproveitamento) ) as faixacomissao
            --,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador) as faixacomissao
            ,(garantia+protecao) as basecomissao
        from(
            select
                261 as idservico
                ,(select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,14,261)) *
                    (select vend from vendedor) as meta
            ,coalesce((select sum(comissao) from garantia),0) as garantia
            ,coalesce((select sum(comissao) from protecao),0) as protecao
                ,sum(vs.servico_vendido) as qtd
                ,sum(vs.servico_presente) as vendas
            from
                vendaservico vs
            where vs.idservico in (261)
            group by 1
        )tb
        left join grupocomissao gc on gc.idservico = tb.idservico
    )tb

)
,grupoconsorcio as (
select
        grupo
        ,variavel
        ,ordem
        ,tb.idgruposervico
        ,meta
        ,0 as qtd
        ,vendas
        ,faixacomissao
        ,case when faixacomissao <> 0 then
                case when vendas >= meta then
                    case when tb.idgruposervico = 502 then vendas * (faixacomissao * 2) / 100
                    else vendas * (faixacomissao) / 100 end
                else vendas * faixacomissao / 100 end
         else 0 end as comissao
        ,case when vendas >= meta then 1 else 0 end pontos
    from(
        select
            gc.descgrupocomissao as grupo
            ,gc.descgruposervico as variavel
            ,tb.idgruposervico
            ,meta * vend as meta
            ,vendas
            ,row_number() over (order by tb.idgruposervico asc) as ordem
            ,(select faixacomissao from aproveitamento where ididentificador = gc.ididentificador) as faixacomissao
        from(
            select
                tc.idgruposervico
                ,case when tc.idgruposervico = 502 then
                    coalesce((select gazin.ccg_get_meta_eletro(:idfilial,:anomes,tc.idgruposervico)),0)
                 else 0 end as meta
                ,(select vend from vendedor) as vend
                ,sum(vs.valor) as vendas
            from
                consorcio vs
                left join gazin.gztipoconsorcio tc on tc.idtipoconsorcio = vs.tiposervico
            group by 1
        )tb
        left join grupocomissao gc on gc.idgruposervico = tb.idgruposervico
    )tb
)
,consorciomaisbonus as (
select
    *
from grupoconsorcio

union

select
    'Consorcio' as grupo
    ,'Consorcio Geral' as variavel
    ,ordem
    ,0 as idgruposervico
    ,meta * vend as meta
    ,0 as qtd
    ,valor as vendas
    ,0 as faixacomissao
    ,0 as comissao
    ,0 as pontos
from(
select
    max(ordem) + 1 as ordem,
    (select gazin.ccg_get_meta_servico_filial(:idfilial,:anomes,502)) as meta,
    sum(vendas) as valor,
    (select vend from vendedor) as vend
from grupoconsorcio
)tb

union

select
    'Consorcio' as grupo,
    'Bonus '|| trim(to_char(gcb.valorinicial, '9G999G990D99')) || ' a ' || trim(to_char(gcb.valorfinal, '9G999G990D99')) as variavel,
    row_number() over (order by gcb.ibonusservico)+1+(select max(ordem) from grupoconsorcio)  as ordem,
    0 as idgruposervico,
    0 as meta,
    0 as qtd,
    0 as vendas,
    0 as faixacomissao,
    case when coalesce(gc.vendas,0) > 0 then gcb.valorbonus else 0 end as comissao,
    0 as pontos
from gazin.ccg_gerente_servico_bonus gcb
left join lateral (
    select
        sum(gc.vendas) as vendas
    from grupoconsorcio gc
) gc on gc.vendas between gcb.valorinicial and gcb.valorfinal
where gcb.anomes = :anomes
)

,painel AS (
    select
      'Painel' as grupo
      ,case
        when grupo in ('Dias','Linhas','Carteira','Estoque','Margem')  then '01 - Produtos'
        when grupo in ('Servicos Complementares','Servicos Independentes','Consorcio') then '02 - Serviços'
       else '03 - Resultado'
      end as variavel
      ,case
        when grupo = 'Dias' then 'Curva de Vendas'
        when grupo = 'Linhas' then 'Grupo de Produtos'
        when grupo = 'Carteira' then 'Carteira, Perdas e Descontos'
        when grupo = 'Estoque' then 'Estoque de Produtos'
        when grupo = 'Margem' then 'Margem Contribuição'
        when grupo = 'Salário' then 'Remuneração Fixo S/ Vendas'
      else grupo
      end as agrupador
      ,case
        when grupo in ('Dias','Linhas','Carteira','Estoque','Margem') then 1
        when grupo in ('Servicos Complementares','Servicos Independentes','Consorcio') then 2
        else 3
      end as ordem
      ,case
          when grupo = 'Dias' then 2
          when grupo = 'Linhas' then 3
          when grupo = 'Carteira' then 4
          when grupo = 'Estoque' then 5
          when grupo = 'Margem' then 6
          when grupo = 'Salário' then 1
          when grupo = 'Servicos Complementares' then 8
          when grupo = 'Servicos Independentes' then 9
          when grupo = 'Consorcio' then 10
        end as participacaometa
      ,0 as participacaorealizada
      ,0 as valormeta
      ,0 as valorrealizado
      ,0 as percrealizado
      ,0 as pontos
      ,comissao
  from(
    select
        cast(l.grupo as varchar) as grupo
        ,sum(l.comissao) as comissao
    from linhas l
    group by 1

  union

    select
        cast(d.grupo as varchar) as grupo
        ,sum(d.comissao) as comissao
    from dias d
    group by 1

  union

    select
        cast(e.grupo as varchar) as grupo
        ,sum(e.comissao) as comissao
    from estoque e
    group by 1

  union

    select
        cast(c.grupo as varchar) as grupo
        ,sum(c.comissao) as comissao
    from carteira c
    group by 1

  union

    select
        cast(m.grupo as varchar) as grupo
        ,sum(m.comissao) as comissao
    from margem m
    group by 1

  union

    select
        'Salário' as grupo
        ,f.faixasalario as comissao
      from gazin.ccg_gerente_faixa_salario f
      where
          (select sum(sf) from realizado ) between f.dofaturamento and f.atefaturamento and f.anomes = :anomes
  union

    select
        cast(grupo as varchar) as grupo
        ,sum(comissao) as comissao
    from(
        select
            grupo
            ,sum(comissao) as comissao
        from
            garantia
        group by 1

        union

        select
            cast(grupo as varchar) as grupo
            ,sum(comissao) as comissao
        from
            protecao
        group by 1
    )tb
    group by 1

  union

    select
        cast(grupo as varchar) as grupo
        ,sum(comissao) as comissao
    from
        independentes
    group by 1

    union

    select
        cast(grupo as varchar) as grupo
        ,sum(comissao) as comissao
    from
        consorciomaisbonus
    group by 1


  )tb
 order by 1,2
)
,evolucaoserv AS (
    select
        s.deaproveitamento
        ,s.ateaproveitamento
        ,s.faixacomissao
    from
        gazin.ccg_servico_comissao_grupo scg
        left join gazin.ccg_servico_aproveitamento s on scg.ididentificador = s.ididentificador
    where
        scg.idgruposervico = 1
         and scg.anomes = :anomes
)
,marcacaoserv AS (
  select
      linha
      ,deaproveitamento
      ,ateaproveitamento
      ,faixacomissao
      ,faixagarantia
      ,classe
  from(
      select
          row_number() over (order by deaproveitamento asc) as linha
          ,deaproveitamento
          ,ateaproveitamento
          ,faixacomissao
          ,round(faixagarantia,2) as faixagarantia
          ,(case when faixagarantia = faixacomissao /* deaproveitamento and ateaproveitamento*/ then 'x-amarelo' else '' end) as classe
      from(
      select
          deaproveitamento
          ,ateaproveitamento
          ,faixacomissao
          ,(select faixacomissao from garantia) as faixagarantia
      from
          evolucaoserv
      )tb
      order by 1
  )tb
)
,evolucaovenda as (
    select
        *
    from gazin.ccg_gerente_faixa_salario fs
    where fs.anomes = :anomes
)
,marcacaovenda AS (
  select
      linha
      ,dofaturamento
      ,atefaturamento
      ,faixasalario
      ,venda
      ,classe
  from(
      select
          row_number() over (order by dofaturamento asc) as linha
          ,dofaturamento
          ,atefaturamento
          ,faixasalario
          ,round(venda,2) as venda
          ,(case when venda between dofaturamento and atefaturamento then 'x-amarelo' else '' end) as classe
      from(
      select
          dofaturamento
          ,atefaturamento
          ,faixasalario
          ,(select sum(sf) from realizado) as venda
      from
          evolucaovenda
      )tb
      order by 1
  )tb
)
,evolucaoprotecao AS (
    select
        s.deaproveitamento
        ,s.ateaproveitamento
        ,s.faixacomissao
        ,s.faixagarantia
    from
        gazin.ccg_servico_comissao_grupo scg
        left join gazin.ccg_servico_aproveitamento s on scg.ididentificador = s.ididentificador
    where
        scg.idgruposervico = 2
        and scg.anomes = :anomes
    order by s.faixagarantia,s.deaproveitamento
)
,marcacaoprotecao AS (
    select
      linha
      ,deaproveitamento
      ,ateaproveitamento
      ,faixacomissao
      ,faixagarantia
      ,faixaprotecao
      ,garantia
      ,classe
  from(
      select
          row_number() over (order by faixagarantia,deaproveitamento asc) as linha
          ,deaproveitamento
          ,ateaproveitamento
          ,faixacomissao
          ,faixaprotecao
          ,faixagarantia
          ,round(garantia,2) as garantia
          ,(case when faixaprotecao between deaproveitamento and ateaproveitamento and faixagarantia = garantia then 'x-amarelo' else '' end) as classe
      from(
      select
          deaproveitamento
          ,ateaproveitamento
          ,faixacomissao
          ,faixagarantia
          ,(select faixacomissao from garantia) as garantia
          ,(select case when meta>0 then (1 - ((meta-percrealizado)/meta)) * 100 else 0 end from protecao) as faixaprotecao
      from
          evolucaoprotecao
      order by faixagarantia,deaproveitamento
      )tb
      where garantia = faixagarantia
      order by faixagarantia,deaproveitamento
  )tb
  order by faixagarantia,deaproveitamento
)

select
 tb.*
 ,sum( tb.pontos ) over (partition by grupo) as pontosgrupo
from (

  select
      'Evolucao Protecao' as grupo,
      'Protecao' as variavel,
      cast(linha as varchar) as agrupador,
      1 as ordem,
      deaproveitamento as participacaometa,
      ateaproveitamento as participacaorealizada,
      faixacomissao as valormeta,
      faixagarantia as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      0 as comissao,
      (case
          when linha = (select linha from marcacaoprotecao where classe = 'x-amarelo') - 1 then 'x-vermelho'
          when linha = (select linha from marcacaoprotecao where classe = 'x-amarelo') + 1 then 'x-azul-c'
          when classe = 'x-amarelo' then 'x-amarelo'
       else ''
       end) as marcacao
  from marcacaoprotecao
  --where linha between (select linha from marcacaoprotecao where classe = 'x-amarelo') -3 and (select linha from marcacaoprotecao where classe = 'x-amarelo') +3

  union

  select
      'Evolucao Servico' as grupo,
      'Servico' as variavel,
      '' as agrupador,
      1 as ordem,
      deaproveitamento as participacaometa,
      ateaproveitamento as participacaorealizada,
      faixacomissao as valormeta,
      faixagarantia as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      0 as comissao,
      (case
          when linha = (select linha from marcacaoserv where classe = 'x-amarelo') - 1 then 'x-vermelho'
          when linha = (select linha from marcacaoserv where classe = 'x-amarelo') + 1 then 'x-azul-c'
          when classe = 'x-amarelo' then 'x-amarelo'
       else ''
       end) as marcacao
  from marcacaoserv

  union

  select
      'Evolucao Venda' as grupo,
      'Venda' as variavel,
      '' as agrupador,
      2 as ordem,
      dofaturamento as participacaometa,
      atefaturamento as participacaorealizada,
      faixasalario as valormeta,
      venda as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      0 as comissao,
      (case
          when linha = (select linha from marcacaovenda where classe = 'x-amarelo') - 1 then 'x-vermelho'
          when linha = (select linha from marcacaovenda where classe = 'x-amarelo') + 1 then 'x-azul-c'
          when classe = 'x-amarelo' then 'x-amarelo'
       else ''
       end) as marcacao
  from marcacaovenda
  where linha between (select linha from marcacaovenda where classe = 'x-amarelo') -3 and (select linha from marcacaovenda where classe = 'x-amarelo') +6

  union

  select
      cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      '' as agrupador,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao,
      '' as marcacao
  from(
  select
      cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao
  from linhas

  union

  select
      cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao
  from dias

  union

  select
      cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao
  from carteira

  union

  select
      cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao
  from estoque

  union

  select
      cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao
  from margem
  )tb

  union

  select
        cast(grupo as varchar) as grupo,
      cast(variavel as varchar) as variavel,
      agrupador,
      ordem,
      participacaometa,
      participacaorealizada,
      valormeta,
      valorrealizado,
      percrealizado,
      pontos,
      comissao,
      '' as marcacao
  from
      painel

  union

  select
      'Painel' as grupo,
      '01 - Produtos' as variavel,
      'Produtos Total' as agrupador,
      1 as ordem,
      7 as participacaometa,
      0 as participacaorealizada,
      0 as valormeta,
      0 as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      sum(comissao) as comissao,
      '' as marcacao
  from
      painel
  where ordem = 1

  union

  select
      'Painel' as grupo,
      '02 - Serviços' as variavel,
      'Servicos Total' as agrupador,
      2 as ordem,
      11 as participacaometa,
      0 as participacaorealizada,
      0 as valormeta,
      0 as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      sum(comissao) as comissao,
      '' as marcacao
  from
      painel
  where ordem = 2

  union

  select
      'Painel' as grupo,
      '03 - Resultado' as variavel,
      'Gratificacao Total' as agrupador,
      4 as ordem,
      12 as participacaometa,
      0 as participacaorealizada,
      0 as valormeta,
      0 as valorrealizado,
      0 as percrealizado,
      0 as pontos,
      sum(comissao) as comissao,
      '' as marcacao
  from
      painel

  UNION

  select
      cast(grupo as varchar) as grupo,
    cast(variavel as varchar) as variavel,
    '' as agrupador,
    ordem,
    qtd as participacaometa,
    faixacomissao as participacaorealizada,
    meta as valormeta,
    vendas as valorrealizado,
    percrealizado,
    aprov as pontos,
    comissao,
    '' as marcacao
  from(
    select
        grupo
        ,variavel
        ,ordem
        ,meta
        ,qtd
        ,vendas
        ,percrealizado
        ,faixacomissao
        ,comissao
        ,aprov
    from(
        select
            grupo
            ,variavel
            ,ordem
            ,meta
            ,qtd
            ,vendas
            ,percrealizado
            ,faixacomissao
            ,comissao
            ,round(case when meta>0 then (1 - ((meta-percrealizado)/meta)) * 100 else 0 end,2) as aprov
        from
            garantia

        union

        select
            grupo
            ,variavel
            ,ordem
            ,meta
            ,qtd
            ,vendas
            ,percrealizado
            ,faixacomissao
            ,comissao
            ,round(case when meta>0 then (1 - ((meta-percrealizado)/meta)) * 100 else 0 end,2) as aprov
        from
            protecao
    )tb

    union

    select
        grupo
        ,variavel
        ,ordem
        ,coalesce(meta,0) as meta
        ,coalesce(qtd,0) as qtd
        ,coalesce(vendas,0) as vendas
        ,coalesce(percrealizado,0) as percrealizado
        ,coalesce(faixacomissao,0) as faixacomissao
        ,coalesce(comissao,0) as comissao
        ,basecomissao as aprov
    from
        independentes

    union

    select
        grupo
        ,variavel
        ,ordem
        ,meta
        ,qtd
        ,vendas
        ,0 as percrealizado
        ,faixacomissao
        ,comissao
        ,0 as aprov
    from
        consorciomaisbonus
  )tb

)tb
order by grupo,ordem,variavel,agrupador,participacaometa