dbo.LN_graficos_imperium_new

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec LN_graficos_imperium_new 'DEUDA_PREVISIONAL', 2046807993, 73, 583706793
exec LN_graficos_imperium_new 'RESP_CIVIL', 2046807993, 73, 583706793
exec LN_graficos_imperium_new 'CUMPLIMIENTO_ULTIMO_MES', 2046807993, 73, 583706793
select * from ln_cliente_nivel where indicador = 360 and cliente = 2046807993

exec LN_graficos_imperium_new 'CUMPLIMIENTO_ANUAL', 2046807993, 73, 583706793

select * from ln_indicadores where clave_indicador in ('DP_301','DP_351','pago_resp_civil' ,'CUMPLIMIENTO_ULTIMO_MES','CUMPLIMIENTO_ANUAL')


320 DP_301
321 DP_351
360 CUMPLIMIENTO_ULTIMO_MES
410 CUMPLIMIENTO_ANUAL
352 pago_resp_civil




*/
CREATE OR ALTER PROCEDURE dbo.LN_graficos_imperium_new 

 @accion varchar(50), --'DEUDA_PREVISIONAL'  -- APORTES        'DP_301' 
            -- CONTRIBUCIONES 'DP_351' 
       -- 'RESP_CIVIL' 
       -- 'CUMPLIMIENTO_ULTIMO_MES'
       -- 'CUMPLIMIENTO_ANUAL'
 @cliente int,
 @servicio_contratado int = 73, 
 @userID int = 0 
 AS
BEGIN

 SET NOCOUNT ON;

 declare @lista    varchar(max) 

 declare @valores   table (orden int, valor_desde decimal(20,2),  valor varchar(100), detalle varchar(max))
 declare @familias  table (familia int, nombre_familia varchar(max))
 declare @titulos   table (titulo varchar(max))
 declare @resultado table (grafico varchar(200), familia int, nombre_familia varchar(max), estado varchar(100), detalle varchar(max), cantidad int) 
 declare @indicador int 
 if (@accion = 'DEUDA_PREVISIONAL' or @accion = 'RESP_CIVIL'  or @accion = 'CUMPLIMIENTO_ULTIMO_MES' or @accion = 'CUMPLIMIENTO_ANUAL')
 begin 
  set @lista = null

  -- 1) Busco cuales son los indicadores y si el usuario tiene permisos 
  if (@accion = 'DEUDA_PREVISIONAL') 
  begin
   insert into @valores values (1,0,'0','No Registra')
   insert into @valores values (2,1,'1','Registra')
   select @indicador = indicador from LN_indicadores where clave_indicador = 'DP_301'
   if not exists (select * from usuarios_restricciones where user_id = @userID and indicador = @indicador and servicio_contratado = @servicio_contratado) 
   begin
    set @lista = 'DP_301'
   end 
   select @indicador = indicador from LN_indicadores where clave_indicador = 'DP_351'
   if not exists (select * from usuarios_restricciones where user_id = @userID and indicador = @indicador and servicio_contratado = @servicio_contratado) 
   begin
    if (@lista is not null)
       set @lista = @lista + ',DP_351'
    else 
       set @lista = 'DP_351'
   end
  end


  if (@accion = 'RESP_CIVIL')
  begin
   select @indicador = indicador from LN_indicadores where clave_indicador = 'pago_resp_civil'
   if not exists (select * from usuarios_restricciones where user_id = @userID and indicador = @indicador and servicio_contratado = @servicio_contratado) 
   begin
    set @lista = 'pago_resp_civil'
   end 
   -- valores posibles:
   insert into @valores values (1,1,'V', 'Verificado y Vigente')
   insert into @valores values (2,2,'R', 'Verificado y No vigente')
   insert into @valores values (3,3,'N', 'No presentado')

  end 
  if (@accion = 'CUMPLIMIENTO_ULTIMO_MES')
  begin
      select @indicador = indicador from LN_indicadores where clave_indicador = 'CUMPLIMIENTO_ULTIMO_MES'
   if not exists (select * from usuarios_restricciones where user_id = @userID and indicador = @indicador and servicio_contratado = @servicio_contratado) 
   begin
    set @lista = 'CUMPLIMIENTO_ULTIMO_MES'
   end
   -- valores posibles
   insert into @valores values (1,1,'V','VERDE')
   insert into @valores values (2,2,'A','AMARILLO')
   insert into @valores values (3,3,'N','NARANJA')
   insert into @valores values (4,4,'R','ROJO')
   insert into @valores values (5,10,'P','EN ESPERA')
  end 
  if (@accion = 'CUMPLIMIENTO_ANUAL')
  begin
   select @indicador = indicador from LN_indicadores where clave_indicador = 'CUMPLIMIENTO_ANUAL'
   if not exists (select * from usuarios_restricciones where user_id = @userID and indicador = @indicador and servicio_contratado = @servicio_contratado) 
   begin
    set @lista = 'CUMPLIMIENTO_ANUAL'
   end
   -- valores posibles (rangos)
   insert into @valores values (1,0,'0','0%')
   insert into @valores values (2,1,'1-25','1-25%') 
   insert into @valores values (3,26,'26-50','26-50%')
   insert into @valores values (4,51,'51-75','51-75%')
   insert into @valores values (5,76,'76-99','76-99%')
   insert into @valores values (6,100,'100','100%')
  end 
  
  if (@lista is null) return -- No tengo nada para mostrar porque no tiene permisos 
  
  -- 2) Busco las familias y si el usuario tiene permisos 
  insert into @familias(familia,nombre_familia) 
  select distinct LN_contraparte_familias.familia, LN_familias.nombre_familia
  from LN_contrapartes 
   INNER JOIN LN_contraparte_familias ON (LN_contraparte_familias.contraparte = LN_contrapartes.contraparte and 
                                          LN_contraparte_familias.activo = 1) 
   INNER JOIN LN_familia_productos    ON (LN_familia_productos.familia = LN_contraparte_familias.familia) 
   INNER JOIN LN_productos            ON  LN_familia_productos.producto = LN_productos.producto
   INNER JOIN LN_indicadores          ON  LN_indicadores.producto = LN_productos.producto
   INNER JOIN LN_familias             ON  LN_familias.familia = LN_contraparte_familias.familia

   WHERE LN_indicadores.clave_indicador in (select Item from dbo.DelimitedSplit8K(@lista,',') ) 
    AND LN_contrapartes.contraparte 
        in (select contraparte from ln_contrapartes where cliente = @cliente and activo = 1 and servicio_contratado = @servicio_contratado) 
    AND LN_familia_productos.activo = 1
    AND NOT EXISTS (select * from usuarios_restricciones where USER_ID = @userID and familia   = LN_contraparte_familias.familia)
   
   if (select COUNT(*) from @familias) = 0 return -- no tengo nada para mostrar porque no tiene permisos

   insert into @titulos (titulo) 
   select case Item
    when 'DP_301' then 'APORTES'
    when 'DP_351' then 'CONTRIBUCIONES'
    when 'pago_resp_civil' then 'RESP_CIVIL'
    when 'CUMPLIMIENTO_ULTIMO_MES' then 'CUMPLIMIENTO_ULTIMO_MES'
    when 'CUMPLIMIENTO_ANUAL' then 'CUMPLIMIENTO_ANUAL'
    end as grafico
   from dbo.DelimitedSplit8K(@lista,',')
  
  -- 3) Busco cantidades por cada familia / estado 
  insert into @resultado (grafico, familia, nombre_familia, estado, detalle, cantidad) 

  SELECT case LN_indicadores.clave_indicador 
    when 'DP_301' then 'APORTES'
    when 'DP_351' then 'CONTRIBUCIONES'
    when 'pago_resp_civil' then 'RESP_CIVIL'
    when 'CUMPLIMIENTO_ULTIMO_MES' then 'CUMPLIMIENTO_ULTIMO_MES'
    when 'CUMPLIMIENTO_ANUAL' then 'CUMPLIMIENTO_ANUAL'
    end 
    as grafico,
   LN_contraparte_familias.familia, LN_familias.nombre_familia, 
   case @accion 
   when 'DEUDA_PREVISIONAL' then convert(varchar,LN_contraparte_indicadores.valor_numerico)
   when 'RESP_CIVIL'        then iif(LN_contraparte_indicadores.valor_texto='I','N',LN_contraparte_indicadores.valor_texto)
   when 'CUMPLIMIENTO_ULTIMO_MES' then LN_contraparte_indicadores.valor_texto
   when 'CUMPLIMIENTO_ANUAL' then 
   case 
   when LN_contraparte_indicadores.valor_numerico = 0 then '0'
   when LN_contraparte_indicadores.valor_numerico > 0 and LN_contraparte_indicadores.valor_numerico <= 25 then '1-25' 
   when LN_contraparte_indicadores.valor_numerico > 25 and LN_contraparte_indicadores.valor_numerico <= 50 then '26-50'
   when LN_contraparte_indicadores.valor_numerico > 50 and LN_contraparte_indicadores.valor_numerico <= 75 then '51-75'
   when LN_contraparte_indicadores.valor_numerico > 75 and LN_contraparte_indicadores.valor_numerico < 100 then '76-99'
   when LN_contraparte_indicadores.valor_numerico = 100 then '100'
   else null 
   end 
   else null 
      end 
   as estado, 

   case @accion 
   when 'DEUDA_PREVISIONAL' then case LN_contraparte_indicadores.valor_numerico
          when 1 then 'Registra'
          when 0 then 'No registra'
          else null 
          end 
   when 'RESP_CIVIL' then case LN_contraparte_indicadores.valor_texto
          when 'I' then 'No presentado'
          when 'N' then 'No presentado'
          when 'V' then 'Verificado y Vigente'
          when 'R' then 'Verificado y No vigente'
          else null 
          end 
   when 'CUMPLIMIENTO_ULTIMO_MES' then convert(varchar,LN_contraparte_indicadores.valor_memo)
   when 'CUMPLIMIENTO_ANUAL' then 
   case 
   when LN_contraparte_indicadores.valor_numerico = 0 then '0'
   when LN_contraparte_indicadores.valor_numerico > 0 and LN_contraparte_indicadores.valor_numerico <= 25 then '1-25' 
   when LN_contraparte_indicadores.valor_numerico > 25 and LN_contraparte_indicadores.valor_numerico <= 50 then '26-50'
   when LN_contraparte_indicadores.valor_numerico > 50 and LN_contraparte_indicadores.valor_numerico <= 75 then '51-75'
   when LN_contraparte_indicadores.valor_numerico > 75 and LN_contraparte_indicadores.valor_numerico < 100 then '76-99'
   when LN_contraparte_indicadores.valor_numerico = 100 then '100'
   else null 
   end 
   else null  
  end as detalle,
   
   count(*) as cantidad
    FROM LN_contraparte_indicadores 
   INNER JOIN LN_contrapartes         ON  LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
   INNER JOIN LN_contraparte_familias ON (LN_contraparte_familias.contraparte = LN_contrapartes.contraparte and 
                                          LN_contraparte_familias.activo = 1) 
   INNER JOIN LN_gestiones            ON (LN_gestiones.contraparte = LN_contraparte_indicadores.contraparte and 
                  LN_gestiones.gestion     = LN_contraparte_indicadores.gestion) 
   INNER JOIN LN_indicadores          ON (LN_indicadores.indicador = LN_contraparte_indicadores.indicador)
   INNER JOIN LN_productos            ON  LN_indicadores.producto = LN_productos.producto
   INNER JOIN LN_familia_productos    ON (LN_familia_productos.producto = LN_productos.producto and 
                                          LN_familia_productos.familia = LN_contraparte_familias.familia) 
   INNER JOIN LN_familias             ON  LN_familias.familia = LN_contraparte_familias.familia

  WHERE LN_indicadores.clave_indicador in (select Item from dbo.DelimitedSplit8K(@lista,',') ) 
            -- Ya esta filtrado los indicadores que el usuario puede ver 
    AND LN_contraparte_indicadores.visible = 1 
    AND LN_contraparte_indicadores.contraparte 
        in (select contraparte from ln_contrapartes where cliente = @cliente and activo = 1 and servicio_contratado = @servicio_contratado) 

    AND LN_familia_productos.activo = 1

    AND LN_gestiones.desde <= getdate()
    AND LN_gestiones.hasta >= getdate()
    AND LN_gestiones.vigente = 1 
    AND LN_contraparte_familias.familia in (select familia from @familias) 
              -- ya esta filtrado que sean las familias que puede ver el usuario 
    
    group by LN_indicadores.clave_indicador , LN_contraparte_familias.familia, LN_familias.nombre_familia,   
     case @accion 
   when 'DEUDA_PREVISIONAL' then convert(varchar,LN_contraparte_indicadores.valor_numerico)
   when 'RESP_CIVIL'        then iif(LN_contraparte_indicadores.valor_texto='I','N',LN_contraparte_indicadores.valor_texto)
   when 'CUMPLIMIENTO_ULTIMO_MES' then LN_contraparte_indicadores.valor_texto
   when 'CUMPLIMIENTO_ANUAL' then 
   case 
   when LN_contraparte_indicadores.valor_numerico = 0 then '0'
   when LN_contraparte_indicadores.valor_numerico > 0 and LN_contraparte_indicadores.valor_numerico <= 25 then '1-25' 
   when LN_contraparte_indicadores.valor_numerico > 25 and LN_contraparte_indicadores.valor_numerico <= 50 then '26-50'
   when LN_contraparte_indicadores.valor_numerico > 50 and LN_contraparte_indicadores.valor_numerico <= 75 then '51-75'
   when LN_contraparte_indicadores.valor_numerico > 75 and LN_contraparte_indicadores.valor_numerico < 100 then '76-99'
   when LN_contraparte_indicadores.valor_numerico = 100 then '100'
   else null 
   end 
   else null 
      end ,

   case @accion 
   when 'DEUDA_PREVISIONAL' then case LN_contraparte_indicadores.valor_numerico
          when 1 then 'Registra'
          when 0 then 'No registra'
          else null 
          end 
   when 'RESP_CIVIL' then case LN_contraparte_indicadores.valor_texto
          when 'I' then 'No presentado'
          when 'N' then 'No presentado'
          when 'V' then 'Verificado y Vigente'
          when 'R' then 'Verificado y No vigente'
          else null 
          end 
   when 'CUMPLIMIENTO_ULTIMO_MES' then convert(varchar,LN_contraparte_indicadores.valor_memo)
   when 'CUMPLIMIENTO_ANUAL' then 
   case 
   when LN_contraparte_indicadores.valor_numerico = 0 then '0'
   when LN_contraparte_indicadores.valor_numerico > 0 and LN_contraparte_indicadores.valor_numerico <= 25 then '1-25' 
   when LN_contraparte_indicadores.valor_numerico > 25 and LN_contraparte_indicadores.valor_numerico <= 50 then '26-50'
   when LN_contraparte_indicadores.valor_numerico > 50 and LN_contraparte_indicadores.valor_numerico <= 75 then '51-75'
   when LN_contraparte_indicadores.valor_numerico > 75 and LN_contraparte_indicadores.valor_numerico < 100 then '76-99'
   when LN_contraparte_indicadores.valor_numerico = 100 then '100'
   else null 
   end 
   else null  
  end
   order by LN_indicadores.clave_indicador, LN_familias.nombre_familia,  
  case @accion 
   when 'DEUDA_PREVISIONAL' then convert(varchar,LN_contraparte_indicadores.valor_numerico)
   when 'RESP_CIVIL'        then iif(LN_contraparte_indicadores.valor_texto='I','N',LN_contraparte_indicadores.valor_texto)
   when 'CUMPLIMIENTO_ULTIMO_MES' then LN_contraparte_indicadores.valor_texto
   when 'CUMPLIMIENTO_ANUAL' then 
   case 
   when LN_contraparte_indicadores.valor_numerico = 0 then '0'
   when LN_contraparte_indicadores.valor_numerico > 0 and LN_contraparte_indicadores.valor_numerico <= 25 then '1-25' 
   when LN_contraparte_indicadores.valor_numerico > 25 and LN_contraparte_indicadores.valor_numerico <= 50 then '26-50'
   when LN_contraparte_indicadores.valor_numerico > 50 and LN_contraparte_indicadores.valor_numerico <= 75 then '51-75'
   when LN_contraparte_indicadores.valor_numerico > 75 and LN_contraparte_indicadores.valor_numerico < 100 then '76-99'
   when LN_contraparte_indicadores.valor_numerico = 100 then '100'
   else null 
   end 
   else null 
      end 
   
  select titulos.*, family.*, valores.*, ln_cliente_nivel.color_fondo as color, isnull(resultado.cantidad ,0) as cantidad
  from @titulos titulos
  left join @familias family  on 1=1
  left join @valores  valores on 1=1
  left join @resultado resultado on resultado.familia = family.familia and resultado.estado = valores.valor and resultado.grafico = titulos.titulo 
  left join ln_cliente_nivel on ln_cliente_nivel.cliente = @cliente and indicador = @indicador and desde <= valores.valor_desde and hasta > valores.valor_desde 

  order by titulos.titulo, family.familia, valores.orden
  

 end
 
END
GO