dbo.LN_graficos_imperium_24062022

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec LN_graficos_imperium 'DEUDA_PREVISIONAL', 2046807993, 73, 583706793
exec LN_graficos_imperium 'RESP_CIVIL', 2046807993, 73, 583706793
exec LN_graficos_imperium 'CUMPLIMIENTO_ULTIMO_MES', 2046807993, 73, 583706793
exec LN_graficos_imperium 'CUMPLIMIENTO_ANUAL', 2046807993, 73, 583706793
exec LN_graficos_imperium 'PLANES_PAGO', 2046807993, 73, 583706793

p
select ln_indicadores.indicador, ln_indicadores.nombre_indicador, ln_indicadores.clave_indicador, ln_cliente_nivel.* from ln_cliente_nivel 
inner join ln_indicadores on ln_indicadores.indicador = ln_cliente_nivel.indicador
where cliente =  2046807993 
and  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

select * from ln_cliente_nivel where indicador = 352

*/
CREATE OR ALTER PROCEDURE dbo.LN_graficos_imperium_24062022 

 @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),color_default varchar(10))
 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','63b68c')
   insert into @valores values (2,1,'1','Registra','A72B2A')
   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','63b68c')
   insert into @valores values (2,2,'R', 'Verificado y No vigente','A72B2A')
   insert into @valores values (3,3,'N', 'No presentado','cccccc')

  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','63b68c')
   insert into @valores values (2,2,'A','AMARILLO','FFB81C')
   insert into @valores values (3,3,'N','NARANJA','FF5C1C')
   insert into @valores values (4,4,'R','ROJO','A72B2A')
   insert into @valores values (5,10,'P','EN ESPERA','cccccc')
  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%','A72B2A')
   insert into @valores values (2,1,'1-25','1-25%','FF5C1C') 
   insert into @valores values (3,26,'26-50','26-50%','FFB81C')
   insert into @valores values (4,51,'51-75','51-75%','fbe699')
   insert into @valores values (5,76,'76-99','76-99%','e2efda')
   insert into @valores values (6,100,'100','100%','63b68c')
  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.*, 
  iif(ln_cliente_nivel.color_fondo is null, valores.color_default, 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

 
 IF (@accion = 'PLANES_PAGO') 
 BEGIN

   declare @periodo_fin    varchar(10) = null 
   declare @periodo_fin1   varchar(10) = null 
   declare @periodo_inicio varchar(10) = null 
   declare @dtEnd   date = null 
   declare @dtStart date = null 
   /*
    declare @cliente int = 2046807993
    declare @servicio_contratado int = 73
    */

   declare @periodo varchar(10)
   declare @contratista int 
   declare @doc_id bigint 

   declare @planes_pago table (periodo varchar(10), cantidad_contrapartes int, monto_deuda decimal(20,2), monto_abonado decimal(20,2), monto_adeudado decimal(20,2))
   declare @monto_deuda decimal(20,2)
   declare @monto_abonado decimal(20,2)
   declare @monto_adeudado decimal(20,2)

   select @periodo_fin = max(periodo) 
     from led_docs 
    where tipo_doc = 1031 
      and contratista_1 in (select id_imperium from ln_contrapartes where cliente = @cliente and activo = 1 and servicio_contratado = @servicio_contratado) 
      and contratista = contratista_1 
      and estado_doc = 'V'
   set @periodo_fin1 = substring(@periodo_fin,1,4) + '-'+ substring(@periodo_fin,5,2) + '-' + '01'
   set @dtEnd   = convert(date,@periodo_fin1) 
   if (@dtEnd is null) set @dtEnd = DATEADD(dd,-(DAY(getdate())),getdate())
   set @dtStart = dateadd(month,-11, @dtEnd) 

   set @periodo_inicio = convert(char(4),DATEPART(yyyy,@dtStart))+convert(char(2),right('0'+convert(varchar(2),DATEPART(mm,@dtStart)),2))

   --select @periodo_inicio,  @periodo_fin
   
   DECLARE C_ind CURSOR FOR
    
   select periodo, contratista_1, max(doc_id) 
     from led_docs 
    where tipo_doc = 1031 
      and contratista_1 in (select id_imperium from ln_contrapartes where cliente = @cliente and activo = 1 and servicio_contratado = @servicio_contratado) 
      and contratista = contratista_1 
      and estado_doc = 'V'
      and periodo between @periodo_inicio and @periodo_fin
      group by periodo, contratista_1 
      order by periodo, contratista_1 

   OPEN C_ind
   FETCH NEXT FROM C_ind INTO @periodo, @contratista, @doc_id 
   WHILE @@fetch_status = 0
   BEGIN 
       --select @periodo, @contratista, @doc_id 
    set @monto_deuda = null
    set @monto_abonado = null 
    set @monto_adeudado = null 
    
    select @monto_deuda = sum(monto_total)
      from led_planes_pagos
     where doc_id = @doc_id and estado is not null and estado != 'G'
    
    if (isnull(@monto_deuda,0) != 0)
    begin 
    
     select @monto_abonado = sum(importe_cancelado)
       from led_planes_pagos
      where doc_id = @doc_id and estado is not null and estado != 'G' 
   
     select @monto_adeudado = (sum(monto_total) - sum(importe_cancelado))
       from led_planes_pagos
      where doc_id = @doc_id and estado is not null and estado != 'G'

     if exists (select * from @planes_pago where periodo = @periodo) 
     begin
      update @planes_pago 
         set  cantidad_contrapartes = cantidad_contrapartes + 1, 
        monto_deuda = monto_deuda + isnull(@monto_deuda, 0),
        monto_abonado = monto_abonado + isnull(@monto_abonado,0),
        monto_adeudado = monto_adeudado + isnull(@monto_adeudado,0)
      where periodo = @periodo
     end
     else
     begin
      insert into @planes_pago values (@periodo, 1,isnull(@monto_deuda, 0),isnull(@monto_abonado,0),isnull(@monto_adeudado,0))
     end 
    end
    FETCH NEXT FROM C_ind INTO @periodo, @contratista, @doc_id 

   END 
   CLOSE C_ind
   DEALLOCATE C_ind

   select  substring(periodo,5,2) + '/'+ substring(periodo,1,4) as periodo_mm_yyyy,
           substring(periodo,1,4) + '-'+ substring(periodo,5,2) as periodo_yyyy_mm,
     case substring(periodo,5,2) 
      when '01' then 'ene' 
      when '02' then 'feb'
      when '03' then 'mar'
      when '04' then 'abr'
      when '05' then 'may'
      when '06' then 'jun'
      when '07' then 'jul'
      when '08' then 'ago'
      when '09' then 'sep'
      when '10' then 'oct'
      when '11' then 'nov'
      when '12' then 'dic'
      else ''
     end + '-' + substring(periodo,3,2) as periodo_mes_anio,

     cantidad_contrapartes as empresas_con_plan, 

     monto_deuda as monto_total_planes,
     monto_abonado as monto_abonado_planes, 
     monto_adeudado as monto_adeudado_planes, 

     monto_deuda/1000000 as monto_total_planes_en_millones,
     monto_abonado/1000000 as monto_abonado_planes_en_millones, 
     monto_adeudado/1000000 as monto_adeudado_planes_en_millones,

     monto_deuda/1000000000 as monto_total_planes_en_miles_millones,
     monto_abonado/1000000000 as monto_abonado_planes_en_miles_millones, 
     monto_adeudado/1000000000 as monto_adeudado_planes_en_miles_millones

     from @planes_pago 
 
 END



END
GO