
if exists ( select 1 from sys.procedures where name = 'p_relat_analise_comiss' )
   drop proc p_relat_analise_comiss
go


create proc p_relat_analise_comiss

   @filiais  varchar( 500 )
   , @cargos  varchar( 500 )
   , @produtos  varchar( 500 ) = ''

as


/*

exec p_relat_analise_comiss @filiais = '1' , @cargos = '9,399'  , @produtos = '1533'

exec p_relat_analise_comiss @filiais = '1,31' , @cargos = '5,9,399'  , @produtos = '1533,1532,1531'

exec p_relat_analise_comiss @filiais = '1,2,3' , @cargos = '5,9,399'  , @produtos = '1533,1532,1531'


04.01.2022 - Gustavo Postigo - desenvolvimento inicial
*/


set nocount on


--declare  @filiais  varchar( 500 ) = '2' , @cargos  varchar( 500 ) = '1'  , @produtos  varchar( 500 ) = '1533'


-------------- lista de parametros em tabelas

if OBJECT_ID( 'tempdb..#filial' ) is not null    drop table #filial
select number as cd_filial
   into #filial
   from dbo.f_ListaEmTabela( @filiais )

if OBJECT_ID( 'tempdb..#cargo' ) is not null    drop table #cargo
select number as cd_cargo
   into #cargo
   from dbo.f_ListaEmTabela( @cargos )

if OBJECT_ID( 'tempdb..#prod' ) is not null    drop table #prod
select number as cd_prod
   into #prod
   from dbo.f_ListaEmTabela( @produtos )

delete from #prod where cd_prod = 0



------------------ montagem da 1a lista de comissoes por nivel

if OBJECT_ID( 'tempdb..#lista1_comis' ) is not null    drop table #lista1_comis

select CONVERT( varchar( 25 ) , 'Produto' ) as ORIG , a.CD_TBL_COMIS  , f.CD_FILIAL , cc.CD_CARGO , a.CD_PROD 
      , case when b.TP_VALOR = 0 then a.PERC_COMIS else 0 end as PERC_COMIS
      , case when b.TP_VALOR = 1 then a.PERC_COMIS else 0 end as VALOR_COMIS
   into #lista1_comis
   --select *
   from EST_PROD_TBL_COMIS_EST_PROD a   with ( nolock )
   join EST_PROD_TBL_COMIS b   with ( nolock )
     on b.CD_EMP       = a.CD_EMP
    and b.CD_TBL_COMIS = a.CD_TBL_COMIS
   join EST_PROD_TBL_COMIS_PRC_FILIAL   f   with ( nolock )
     on f.CD_EMP       = b.CD_EMP
    and f.CD_TBL_COMIS = b.CD_TBL_COMIS
    and f.CD_FILIAL in ( select cd_filial from #filial )
   join FP_CARGO_EST_PROD_TBL_COMIS  cc 
      on cc.CD_EMP = a.CD_EMP 
      and cc.CD_TBL_COMIS = a.CD_TBL_COMIS
      and cc.CD_CARGO in ( select cd_cargo from #cargo )
   where b.CD_EMP = 1
      and b.STS_TBL = 0
      and (    ( select count( 1 ) from #prod ) = 0
            or ( ( select count( 1 ) from #prod ) > 0  
                  and a.CD_PROD in ( select cd_prod from #prod ) 
               )
          )
         
create index xx on #lista1_comis ( CD_FILIAL , CD_PROD , CD_CARGO )
create index ff on #lista1_comis ( CD_FILIAL )
create index pp on #lista1_comis ( CD_PROD )
create index cc on #lista1_comis ( CD_CARGO )
create index tt on #lista1_comis ( CD_TBL_COMIS )


insert into #lista1_comis
select 'Familia' as ORIG , a.CD_TBL_COMIS , f.CD_FILIAL , cc.CD_CARGO , b.CD_PROD 
      , case when d.TP_VALOR = 0 then a.PERC_COMIS else 0 end as PERC_COMIS
      , case when d.TP_VALOR = 1 then a.PERC_COMIS else 0 end as VALOR_COMIS
   from EST_PROD_TBL_COMIS_EST_ARV_MERC_FAMILIA a   with ( nolock )
   join EST_PROD_TBL_COMIS d   with ( nolock )
     on d.CD_EMP       = a.CD_EMP
    and d.CD_TBL_COMIS = a.CD_TBL_COMIS
   join EST_PROD_TBL_COMIS_PRC_FILIAL   f   with ( nolock )
     on f.CD_EMP       = d.CD_EMP
    and f.CD_TBL_COMIS = d.CD_TBL_COMIS
    and f.CD_FILIAL in ( select cd_filial from #filial )
   join EST_PROD_EST_ARV_MERCADOLOGICA b   with ( nolock )
     on b.CD_EMP               = a.CD_EMP
    and b.CD_ARV_MERC_FAMILIA  = a.CD_ARV_MERC_FAMILIA
   join FP_CARGO_EST_PROD_TBL_COMIS  cc 
      on cc.CD_EMP = a.CD_EMP 
      and cc.CD_TBL_COMIS = a.CD_TBL_COMIS
      and cc.CD_CARGO in ( select cd_cargo from #cargo )
   where a.CD_EMP = 1
      and d.STS_TBL = 0
      and (    ( select count( 1 ) from #prod ) = 0
            or ( ( select count( 1 ) from #prod ) > 0  
                  and b.CD_PROD in ( select cd_prod from #prod ) 
               )
          )
      and not exists
            ( select 1
                  from #lista1_comis  x
                  where x.CD_FILIAL = f.CD_FILIAL
                     and x.CD_PROD = b.CD_PROD
                     and x.CD_CARGO = cc.CD_CARGO )


insert into #lista1_comis        
select 'Marca' as ORIG , a.CD_TBL_COMIS , f.CD_FILIAL , cc.CD_CARGO , b.CD_PROD 
      , case when d.TP_VALOR = 0 then a.PERC_COMIS else 0 end as PERC_COMIS
      , case when d.TP_VALOR = 1 then a.PERC_COMIS else 0 end as VALOR_COMIS
   from EST_PROD_TBL_COMIS_EST_MC a   with ( nolock )
   join EST_PROD_TBL_COMIS d
     on d.CD_EMP       = a.CD_EMP
    and d.CD_TBL_COMIS = a.CD_TBL_COMIS
   join EST_PROD_TBL_COMIS_PRC_FILIAL   f   with ( nolock )
     on f.CD_EMP       = d.CD_EMP
    and f.CD_TBL_COMIS = d.CD_TBL_COMIS
    and f.CD_FILIAL in ( select cd_filial from #filial )
   join EST_PROD_EST_ARV_MERCADOLOGICA b   with ( nolock )
     on b.CD_EMP  = a.CD_EMP
    and b.CD_MC = a.CD_MC
   join FP_CARGO_EST_PROD_TBL_COMIS  cc 
      on cc.CD_EMP = a.CD_EMP 
      and cc.CD_TBL_COMIS = a.CD_TBL_COMIS
      and cc.CD_CARGO in ( select cd_cargo from #cargo )
   where a.CD_EMP = 1
      and d.STS_TBL = 0
      and (    ( select count( 1 ) from #prod ) = 0
            or ( ( select count( 1 ) from #prod ) > 0  
                  and b.CD_PROD in ( select cd_prod from #prod ) 
               )
          )
      and not exists
            ( select 1
                  from #lista1_comis  x
                  where x.CD_FILIAL = f.CD_FILIAL
                     and x.CD_PROD = b.CD_PROD
                     and x.CD_CARGO = cc.CD_CARGO )


insert into #lista1_comis
select 'Fabricante' as ORIG , a.CD_TBL_COMIS , f.CD_FILIAL , cc.CD_CARGO , b.CD_PROD 
      , case when d.TP_VALOR = 0 then a.PERC_COMIS else 0 end as PERC_COMIS
      , case when d.TP_VALOR = 1 then a.PERC_COMIS else 0 end as VALOR_COMIS
   from EST_PROD_TBL_COMIS_EST_PROD_FABRIC  a   with ( nolock )
   join EST_PROD_TBL_COMIS d   with ( nolock )
     on d.CD_EMP       = a.CD_EMP
    and d.CD_TBL_COMIS = a.CD_TBL_COMIS
   join EST_PROD_TBL_COMIS_PRC_FILIAL   f   with ( nolock )
     on f.CD_EMP       = d.CD_EMP
    and f.CD_TBL_COMIS = d.CD_TBL_COMIS
    and f.CD_FILIAL in ( select cd_filial from #filial )
   join EST_PROD  b   with ( nolock )
     on b.CD_EMP    = a.CD_EMP
    and b.CD_FABRIC = a.CD_FABRIC
   join FP_CARGO_EST_PROD_TBL_COMIS  cc 
      on cc.CD_EMP = a.CD_EMP 
      and cc.CD_TBL_COMIS = a.CD_TBL_COMIS
      and cc.CD_CARGO in ( select cd_cargo from #cargo )
   where a.CD_EMP = 1
      and d.STS_TBL = 0
      and (    ( select count( 1 ) from #prod ) = 0
            or ( ( select count( 1 ) from #prod ) > 0  
                  and b.CD_PROD in ( select cd_prod from #prod ) 
               )
          )
      and not exists
            ( select 1
                  from #lista1_comis  x
                  where x.CD_FILIAL = f.CD_FILIAL
                     and x.CD_PROD = b.CD_PROD
                     and x.CD_CARGO = cc.CD_CARGO )


insert into #lista1_comis
select 'Linha' as ORIG , a.CD_TBL_COMIS , f.CD_FILIAL , cc.CD_CARGO , b.CD_PROD  
      , case when d.TP_VALOR = 0 then a.PERC_COMIS else 0 end as PERC_COMIS
      , case when d.TP_VALOR = 1 then a.PERC_COMIS else 0 end as VALOR_COMIS
   from EST_PROD_TBL_COMIS_EST_ARV_MERC_linha a   with ( nolock )
   join EST_PROD_TBL_COMIS d   with ( nolock )
     on d.CD_EMP       = a.CD_EMP
    and d.CD_TBL_COMIS = a.CD_TBL_COMIS
   join EST_PROD_TBL_COMIS_PRC_FILIAL   f   with ( nolock )
     on f.CD_EMP       = d.CD_EMP
    and f.CD_TBL_COMIS = d.CD_TBL_COMIS
    and f.CD_FILIAL in ( select cd_filial from #filial )
   join EST_PROD_EST_ARV_MERCADOLOGICA  b   with ( nolock )
     on b.CD_EMP            = a.CD_EMP
    and b.CD_ARV_MERC_LINHA = a.CD_ARV_MERC_linha
   join FP_CARGO_EST_PROD_TBL_COMIS  cc 
      on cc.CD_EMP = a.CD_EMP 
      and cc.CD_TBL_COMIS = a.CD_TBL_COMIS
      and cc.CD_CARGO in ( select cd_cargo from #cargo )
   where a.CD_EMP = 1
      and d.STS_TBL = 0
      and (    ( select count( 1 ) from #prod ) = 0
            or ( ( select count( 1 ) from #prod ) > 0  
                  and b.CD_PROD in ( select cd_prod from #prod ) 
               )
          )
      and not exists
            ( select 1
                  from #lista1_comis  x
                  where x.CD_FILIAL = f.CD_FILIAL
                     and x.CD_PROD = b.CD_PROD
                     and x.CD_CARGO = cc.CD_CARGO )


insert into #lista1_comis
select 'Categoria' as ORIG , a.CD_TBL_COMIS , f.CD_FILIAL , cc.CD_CARGO , b.CD_PROD 
      , case when d.TP_VALOR = 0 then a.PERC_COMIS else 0 end as PERC_COMIS
      , case when d.TP_VALOR = 1 then a.PERC_COMIS else 0 end as VALOR_COMIS
   from EST_PROD_TBL_COMIS_EST_ARV_MERC_CATEGORIA a   with ( nolock )
   join EST_PROD_TBL_COMIS d   with ( nolock )
     on d.CD_EMP       = a.CD_EMP
    and d.CD_TBL_COMIS = a.CD_TBL_COMIS
   join EST_PROD_TBL_COMIS_PRC_FILIAL   f   with ( nolock )
     on f.CD_EMP       = d.CD_EMP
    and f.CD_TBL_COMIS = d.CD_TBL_COMIS
    and f.CD_FILIAL in ( select cd_filial from #filial )
   join EST_PROD_EST_ARV_MERCADOLOGICA b   with ( nolock )
     on b.CD_EMP            = a.CD_EMP
    and b.CD_ARV_MERC_CATEG = a.CD_ARV_MERC_CATEG
   join FP_CARGO_EST_PROD_TBL_COMIS  cc 
      on cc.CD_EMP = a.CD_EMP 
      and cc.CD_TBL_COMIS = a.CD_TBL_COMIS
      and cc.CD_CARGO in ( select cd_cargo from #cargo )
   where a.CD_EMP = 1
      and d.STS_TBL = 0
      and (    ( select count( 1 ) from #prod ) = 0
            or ( ( select count( 1 ) from #prod ) > 0  
                  and b.CD_PROD in ( select cd_prod from #prod ) 
               )
          )
      and not exists
            ( select 1
                  from #lista1_comis  x
                  where x.CD_FILIAL = f.CD_FILIAL
                     and x.CD_PROD = b.CD_PROD
                     and x.CD_CARGO = cc.CD_CARGO )


---------------  montagem da 2a lista (final) com agrupamento da melhor comissao


if OBJECT_ID( 'tempdb..#lista2_comis' ) is not null      drop table #lista2_comis
select l.*
   into #lista2_comis
   from #lista1_comis  l
   join
         (
            select l.CD_FILIAL , l.CD_CARGO , l.CD_PROD , MAX( l.PERC_COMIS + l.VALOR_COMIS ) as V_COMIS
            from #lista1_comis  l
            group by l.CD_FILIAL , l.CD_CARGO , l.CD_PROD
         )  c
      on c.CD_FILIAL = l.CD_FILIAL
      and c.CD_CARGO = l.CD_CARGO
      and c.CD_PROD = l.CD_PROD
      and c.V_COMIS = l.PERC_COMIS + l.VALOR_COMIS
   join
         (
            select l.CD_FILIAL , l.CD_CARGO , l.CD_PROD , l.PERC_COMIS + l.VALOR_COMIS as V_COMIS , MIN( l.CD_TBL_COMIS ) as CD_TBL_COMIS
            from #lista1_comis  l
            group by l.CD_FILIAL , l.CD_CARGO , l.CD_PROD , l.PERC_COMIS + l.VALOR_COMIS
         ) t
      on t.CD_FILIAL = c.CD_FILIAL
      and t.CD_CARGO = c.CD_CARGO
      and t.CD_PROD = c.CD_PROD
      and t.V_COMIS = c.V_COMIS
      and t.CD_TBL_COMIS = l.CD_TBL_COMIS

create index xx on #lista2_comis ( CD_FILIAL , CD_PROD , CD_CARGO )
create index ff on #lista2_comis ( CD_FILIAL )
create index pp on #lista2_comis ( CD_PROD )
create index cc on #lista2_comis ( CD_CARGO )
create index tt on #lista2_comis ( CD_TBL_COMIS )



--------------- retorno do resultado

select 
      l.ORIG , l.CD_FILIAL , f.NM_FANT as FILIAL
      , l.CD_CARGO , c.DS_CARGO
      , l.CD_PROD as CD_PRODUTO , am.DS_PROD as DS_PRODUTO
      , am.CD_ARV_MERC_LINHA as CD_LINHA , am.DS_ARV_MERC_LINHA as DS_LINHA 
      , am.CD_ARV_MERC_CATEG as CD_CATEGORIA , am.DS_ARV_MERC_CATEG as DS_CATEGORIA
      , am.CD_FABRIC as CD_FABRICANTE , am.NM_FABRIC as NM_FABRICANTE
      , am.CD_ARV_MERC_CATEG_SUB as CD_SUBCATEGORIA , am.DS_ARV_MERC_CATEG_SUB as DS_SUBCATEGORIA
      , am.CD_ARV_MERC_DEPTO , am.DS_ARV_MERC_DEPTO
      , am.CD_MC as CD_MARCA , am.DS_MC as DS_MARCA
      , am.CD_ARV_MERC_AREA_ATEND as CD_AREA_ATEND , am.DS_ARV_MERC_AREA_ATEND as DS_AREA_ATEND
      , am.CD_ARV_MERC_SEG as CD_SEGMENTO , am.DS_ARV_MERC_SEG as DS_SEGMENTO
      , am.CD_ARV_MERC_FAMILIA as CD_FAMILIA , am.DS_ARV_MERC_FAMILIA as DS_FAMILIA
      , t.DS_TBL_COMIS 
	   , CONCAT( t.CD_TBL_COMIS , ' - ' , t.DS_TBL_COMIS , ' - ' , l.ORIG ) as TABELA_COMISSAO
	   , l.PERC_COMIS , l.VALOR_COMIS
      , ( select MAX( mc.PERC_COMIS ) as MAIOR_PERC_COMIS
               from #lista2_comis   mc
               where mc.CD_FILIAL = l.CD_FILIAL
                  and mc.CD_CARGO = l.CD_CARGO
                  and mc.CD_PROD = l.CD_PROD
         ) as MAIOR_PERC_COMIS
      , ( select MAX( mc.VALOR_COMIS ) as MAIOR_VALOR_COMIS
               from #lista2_comis   mc
               where mc.CD_FILIAL = l.CD_FILIAL
                  and mc.CD_CARGO = l.CD_CARGO
                  and mc.CD_PROD = l.CD_PROD
         ) as MAIOR_VALOR_COMIS
	   , case when ISNULL( l.PERC_COMIS , 0 ) > 0 then 1 else 0 end as QTDEP
	   , case when ISNULL( l.VALOR_COMIS ,0 ) > 0 then 1 else 0 end as QTDEV
   from #lista2_comis  l
   join EST_PROD   p
      on p.CD_EMP = 1
      and p.CD_PROD = l.CD_PROD
   join PRC_FILIAL   f
      on f.CD_EMP = 1
      and f.CD_FILIAL = l.CD_FILIAL
   join FP_CARGO  c
      on c.CD_EMP = 1
      and c.CD_CARGO = l.CD_CARGO
   join EST_PROD_TBL_COMIS  t
      on t.CD_TBL_COMIS  = l.CD_TBL_COMIS
   join V_EST_PROD_ARV_MERCADOLOGICA   am
      on am.CD_EMP = 1
      and am.CD_PROD  = l.CD_PROD
   order by l.CD_FILIAL , l.CD_CARGO , am.DS_PROD


--fim
go