dbo.PANEL_CONTROL_EXCEL_11072024

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec [PANEL_CONTROL_EXCEL_] 'TITULOS',5546348,47,null,0,0
select * from PANEL_CONTROL_EXCEL_TITULOS WHERE cliente = 5546348
exec [PANEL_CONTROL_EXCEL_] 'DATOS',null,null,167,null,1
select * from PANEL_CONTROL_EXCEL_DATOS WHERE contraparte = 167


*/
CREATE OR ALTER PROCEDURE dbo.PANEL_CONTROL_EXCEL_11072024 
@p_accion varchar(100) = 'TITULOS',
@p_cliente int = 5546348, 
@p_servicio int = 47, 
@p_contraparte int = null, 
@p_es_subcontratista int = 0, 
@p_proceso_completo int = 0

AS
BEGIN

SET NOCOUNT ON;
 

 declare @producto int, 
         @nombre_producto varchar(500), 
         @indicador int, 
         @etiqueta_indicador varchar(500), 
         @tipo_dato char(1), 
   @clave_indicador varchar(500), 
   @tiene_dias_gestion char(1) = 'N', 
   @nro_columna int 
 declare @columnas_excel table (nro int identity, producto int, nombre_producto varchar(500), indicador int, etiqueta varchar(500), tipo_dato char(1), clave_indicador varchar(500))
    declare @titulo_score varchar(500) = null


 declare @datos table (servicio_contratado int, contraparte int, indicador int, clave_indicador varchar(100), valor varchar(max), valor_sin_formato varchar(max), texto varchar(max), valor_memo varchar(max))
 declare @d_servicio_contratado int, 
      @d_contraparte int, 
   @d_indicador int,
   @d_clave_indicador varchar(500), 
   @d_valor varchar(max), 
   @d_valor_sin_formato varchar(max), 
   @d_texto varchar(max), 
   @d_valor_memo varchar(max)

 declare @personal table (contraparte int, gestion int, doc_id int, seccion int, campo int, tipo_campo varchar(1), nombre_campo varchar(200), etiqueta varchar(300),fila int, col int, valor_numerico float, valor_texto varchar(max), valor_fecha smalldatetime, valor_fecha_srt varchar(200))


if @p_accion = 'TITULOS'
begin
 SELECT top 1 @titulo_score = titulo from ln_cliente_nivel where titulo is not null and cliente = @p_cliente

 declare @cant_ps int = (select count(distinct isnull(pais_servicio,'')) 
   FROM LN_contrapartes
   inner join LN_gestiones on LN_gestiones.contraparte = LN_contrapartes.contraparte
   INNER JOIN LN_contraparte_familias ON LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   INNER JOIN LN_familias ON LN_contraparte_familias.familia = LN_familias.familia and LN_familias.cliente = ln_contrapartes.cliente
   where LN_contrapartes.cliente = @p_cliente
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   AND LN_familias.visible = 1
   and LN_gestiones.desde <= GETDATE() and LN_gestiones.hasta >= GETDATE()
   and LN_contrapartes.servicio_contratado = @p_servicio)

 if (@cant_ps > 1 )
 begin
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','PAIS DE SERVICIO','T','0_PAIS_SERVICIO')
 end
 insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','PAIS','T','0_PAIS')
 insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','FAMILIA','T','0_FAMILIA')
 insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','RAZON SOCIAL','T','0_RAZON_SOCIAL')
 insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','IDENTIFICADOR','T','0_IDENTIFICADOR')
 insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','IDENTIFICACION','T','0_IDENTIFICACION')
 if (@tiene_dias_gestion = 'S') 
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','DIAS GESTION','T','0_DIAS_GESTION')
 if (@p_cliente=5546348)  
 begin
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','DATOS COMERCALES','T','0_DATOS_COMERCIALES')
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0','EMAIL CONTACTO','T','0_EMAIL_CONTACTO')

  declare @etiqueta_personal varchar(max) = null 
  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_PLANTILLA'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores en Plantilla (Rel. Dependencia)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_PLANTILLA')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_PROFESIONALES'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores profesionales (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_PROFESIONALES')
  
  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_ADMINISTRATIVOS'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_ADMINISTRATIVOS')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_NO_CALIFICADOS'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_NO_CALIFICADOS')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_PLANTILLA_TEMP'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_PLANTILLA_TEMP')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_PROFESIONALES_TEMP'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_PROFESIONALES_TEMP')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_ADMINISTRATIVOS_TEMP'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_ADMINISTRATIVOS_TEMP')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'TRABAJADORES_NO_CALIFICADOS_TEMP'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_TRABAJADORES_NO_CALIFICADOS_TEMP')

  set @etiqueta_personal = null 
  set @etiqueta_personal = (select distinct top 1 etiqueta from LN_campos where campo in (select campo from LN_tipo_doc_campos where campo_id = 'NA_TRABAJADORES'))
  if @etiqueta_personal is null set @etiqueta_personal = 'Total de trabajadores administrativos (Rel. Dep.)'
  insert into @columnas_excel (producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador) values ('0',null,'0',@etiqueta_personal,'T','0_NA_TRABAJADORES')

 end

 declare C_Cursor CURSOR FOR 

 SELECT producto, nombre_producto, indicador, etiqueta_indicador, tipo_dato, clave_indicador FROM
   
   (SELECT LN_productos.producto, LN_productos.nombre_producto, LN_cliente_productos.activo,
    LN_cliente_productos.visibilidad_default, LN_productos.visible,
    country.country_code, country.country_name,
    LN_productos.etiqueta, LN_productos.class_icon, 
    ISNULL(LN_productos.balance, 0) AS balance,
    ISNULL(LN_productos.dossier, 0) AS dossier, 
    ISNULL(LN_productos.suscribe_indicadores, 0) AS suscribe_indicadores, 
    LN_productos.producto_id, LN_indicadores.indicador, LN_indicadores.nombre_indicador,
    LN_indicadores.etiqueta as etiqueta_indicador, LN_indicadores.tipo_dato,
    LN_indicadores.rango_inferior, LN_indicadores.rango_superior, LN_indicadores.opciones,
    LN_indicadores.clave_indicador,
    LN_cliente_productos.orden_producto, LN_indicadores.orden,
    SUSC.indicador AS indicador_susc,
    
    show_panel = CASE 
      
      WHEN ISNULL(LN_productos.suscribe_indicadores, 0) > 0 THEN iif(SUSC.indicador IS NOT NULL, 1, 0)
     ELSE 1 END
    
   FROM LN_productos 
   INNER JOIN LN_cliente_productos ON LN_productos.producto = LN_cliente_productos.producto
   INNER JOIN LN_familia_productos ON LN_cliente_productos.producto = LN_familia_productos.producto
   INNER JOIN LN_familias ON (LN_familia_productos.familia = LN_familias.familia
          AND LN_cliente_productos.cliente = LN_familias.cliente)

   INNER join LN_indicadores ON LN_productos.producto = LN_indicadores.producto

   LEFT OUTER JOIN (SELECT cliente, indicador FROM LN_cliente_indicadores
       WHERE cliente = @p_cliente) SUSC
    ON (LN_cliente_productos.cliente = SUSC.cliente AND LN_indicadores.indicador = SUSC.indicador)


   LEFT OUTER JOIN country ON LN_productos.country_code = country.country_code


   WHERE LN_cliente_productos.cliente = @p_cliente
   AND LN_productos.visible = 1
   AND LN_cliente_productos.activo = 1
   AND LN_familia_productos.activo = 1
   AND LN_familias.visible = 1
   AND ISNULL(LN_familias.subfamilia, 0) = @p_es_subcontratista
   AND LN_indicadores.visible = 1
   AND (@p_servicio is null or LN_productos.servicio_contratado in (0,@p_servicio))
   AND (@p_servicio is null or ln_familias.servicio_contratado in (0,@p_servicio))


   GROUP BY LN_productos.producto, LN_productos.nombre_producto, LN_cliente_productos.activo,
    LN_cliente_productos.visibilidad_default, LN_productos.visible,
    country.country_code, country.country_name,
    LN_productos.etiqueta, LN_productos.class_icon, LN_productos.balance, LN_productos.dossier, LN_productos.suscribe_indicadores, 
    LN_productos.producto_id, LN_indicadores.indicador, LN_indicadores.nombre_indicador,
    LN_indicadores.etiqueta, LN_indicadores.tipo_dato,
    LN_indicadores.rango_inferior, LN_indicadores.rango_superior, LN_indicadores.opciones,
    LN_indicadores.clave_indicador,
    LN_cliente_productos.orden_producto, LN_indicadores.orden,
    SUSC.indicador 

     ) DATA


  WHERE DATA.show_panel = 1
  ORDER BY DATA.orden_producto, DATA.orden

  OPEN C_Cursor
  fetch next from C_Cursor into @producto, @nombre_producto, @indicador, @etiqueta_indicador, @tipo_dato, @clave_indicador
  WHILE @@FETCH_STATUS=0 
  BEGIN
   insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, @indicador, @etiqueta_indicador, @tipo_dato, @clave_indicador)

  
   if (@clave_indicador = 'INICIO_GESTION') 
   begin
    set @tiene_dias_gestion = 'S'
   end

   if (@indicador = 37)
   begin
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9990', 'Datos Informe Exterior', 'T', @clave_indicador)
   end

   if (@clave_indicador = 'CTRL_ETICO') 
   begin
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9993', @etiqueta_indicador + ' Cruce con Base de Personas no autorizadas', 'I', @clave_indicador)
   end
   if (@clave_indicador = 'CTRL_CALIDAD') 
   begin
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9994', @etiqueta_indicador + ' Cruce con Base de Proveedores no autorizados', 'I', @clave_indicador)
   end
   if (@clave_indicador = 'SCORE_FINAL' and  @titulo_score is not null) 
   begin
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9995', @titulo_score, 'T', @clave_indicador)
   end
   if (@clave_indicador = 'CUMPL_DOC') 
   begin
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9996', 'Verificados', 'I', 'V')
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9997', 'Pendientes de Verificar', 'I', 'I')
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9998', 'Rechazados', 'I', 'R')
    insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values (@producto, @nombre_producto, '9999', 'Pendientes de Ingreso', 'I', 'N')
   end
 

   fetch next from C_Cursor into @producto, @nombre_producto, @indicador, @etiqueta_indicador, @tipo_dato, @clave_indicador
  END 
  CLOSE C_Cursor
  DEALLOCATE C_Cursor 
  
  if (@p_servicio = 47) 
  begin
   insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values ('0',null,'0','Detalle Rechazos','T','0_Detalle_Rechazos')
  end
 
  if (@p_cliente = 1226057645 and @p_servicio = 47) 
  begin
   insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values ('0',null,'0','PACTO MUNDIAL','T','0_PACTO_MUNDIAL')
  end
  if (@p_cliente = 2046807993 and @p_servicio = 73) 
  begin
   insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values ('0',null,'0','Meses Deuda Aportes','T','0_Meses_Deuda_Aportes')
   insert into @columnas_excel(producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador)  values ('0',null,'0','Meses Deuda Contribuciones','T','0_Meses_Deuda_Contribuciones')
  end

  if (select COUNT(*) from PANEL_CONTROL_EXCEL_TITULOS where servicio = @p_servicio and cliente = @p_cliente and es_subcontratista = @p_es_subcontratista) > 0
  begin
   update PANEL_CONTROL_EXCEL_TITULOS set visible = 0 where servicio = @p_servicio and cliente = @p_cliente and es_subcontratista = @p_es_subcontratista and visible = 1
  end 

  /*select * from @columnas_excel order by nro  
   return
   */
  declare PC_titulos CURSOR FOR 
   select nro, producto, nombre_producto, indicador, etiqueta, tipo_dato, clave_indicador 
     from @columnas_excel order by nro 
  open PC_titulos 
  FETCH NEXT FROM PC_titulos into  @nro_columna, @producto, @nombre_producto, 
                                   @indicador, @etiqueta_indicador, @tipo_dato, @clave_indicador 
         
  WHILE @@FETCH_STATUS = 0 BEGIN
   if (select count(*) from PANEL_CONTROL_EXCEL_TITULOS 
       where servicio = @p_servicio and cliente = @p_cliente and indicador = @indicador and clave_indicador = @clave_indicador and es_subcontratista = @p_es_subcontratista) -- porque indicador = 0, para todas las primeras columnas
    > 0
   begin
    update PANEL_CONTROL_EXCEL_TITULOS 
       set nro_columna = @nro_columna, 
           producto = @producto, 
        nombre_producto = @nombre_producto,
        etiqueta = @etiqueta_indicador, 
        tipo_dato = @tipo_dato,
        modified = GETDATE(),
        visible = 1
    where servicio = @p_servicio and cliente = @p_cliente and indicador = @indicador and clave_indicador = @clave_indicador and es_subcontratista = @p_es_subcontratista
   end
   else
   begin
    insert into PANEL_CONTROL_EXCEL_TITULOS    (servicio,
               cliente,
               es_subcontratista, 
               nro_columna,
               producto,
               nombre_producto,
               indicador,
               etiqueta,
               tipo_dato,
               clave_indicador)
             values (@p_servicio, @p_cliente, @p_es_subcontratista, @nro_columna, 
               @producto, @nombre_producto, 
               @indicador, @etiqueta_indicador, @tipo_dato, @clave_indicador)
   end
   FETCH NEXT FROM PC_titulos into  @nro_columna, @producto, @nombre_producto, 
            @indicador, @etiqueta_indicador, @tipo_dato, @clave_indicador 
  END 
  CLOSE PC_titulos
  DEALLOCATE PC_titulos
  /*
   select * from PANEL_CONTROL_EXCEL_TITULOS
   where servicio = @p_servicio and cliente = @p_cliente and visible = 1 and es_subcontratista = @p_es_subcontratista
   order by nro_columna 
   */
end

if (@p_accion = 'DATOS')
begin
  declare @pais_servicio varchar(10) = null
  declare @familia int = null

  select @p_cliente = cliente, @p_servicio = servicio_contratado, @p_es_subcontratista = subcontratista, @pais_servicio = pais_servicio
    from ln_contrapartes where contraparte = @p_contraparte 
  select @familia = familia from ln_contraparte_familias where contraparte = @p_contraparte

  -- Si no tiene el registro de titulos, para el cliente, lo genera
  if (select COUNT(*) from PANEL_CONTROL_EXCEL_TITULOS where servicio = @p_servicio and cliente = @p_cliente and es_subcontratista = @p_es_subcontratista) = 0
  begin
   exec [PANEL_CONTROL_EXCEL_] 'TITULOS',@p_cliente,@p_servicio,null,@p_es_subcontratista
  end 

  if (@p_proceso_completo = 1) 
  begin
   insert into @datos

   SELECT LN_Contrapartes.servicio_contratado, LN_contrapartes.contraparte, LN_contraparte_indicadores.indicador,
     LN_indicadores.clave_indicador,

   case        
    when LN_indicadores.tipo_dato = 'S' then LN_contraparte_indicadores.valor_texto
    when LN_indicadores.tipo_dato = 'M' then LN_contraparte_indicadores.valor_memo
    when LN_indicadores.tipo_dato = 'I' then iif(LN_contraparte_indicadores.valor_numerico is not null, 
               REPLACE(replace(convert(varchar,convert(Money, ROUND(LN_contraparte_indicadores.valor_numerico,0)),1),'.00',''), ',', '.'), 
               null)
    when LN_indicadores.tipo_dato = 'F' then iif(LN_contraparte_indicadores.valor_numerico is not null, 
               format(LN_contraparte_indicadores.valor_numerico, '#############.##'),
               null)
    when LN_indicadores.tipo_dato = 'D' then iif(LN_contraparte_indicadores.valor_fecha is not null, convert(varchar, LN_contraparte_indicadores.valor_fecha, 103), NULL)
    when LN_indicadores.tipo_dato = 'B' then iif(LN_contraparte_indicadores.valor_numerico > 0, 'SI', 'NO')
    else null
   end as valor,

   case        
    when LN_indicadores.tipo_dato = 'S' then LN_contraparte_indicadores.valor_texto
    when LN_indicadores.tipo_dato = 'M' then LN_contraparte_indicadores.valor_memo
    when LN_indicadores.tipo_dato = 'I' then convert(varchar,convert(bigint,LN_contraparte_indicadores.valor_numerico))
    when LN_indicadores.tipo_dato = 'F' then convert(varchar,convert(bigint,LN_contraparte_indicadores.valor_numerico))
    when LN_indicadores.tipo_dato = 'D' then iif(LN_contraparte_indicadores.valor_fecha is not null, convert(varchar, LN_contraparte_indicadores.valor_fecha, 103), NULL)
    when LN_indicadores.tipo_dato = 'B' then iif(LN_contraparte_indicadores.valor_numerico > 0, 'SI', 'NO')
    else null
   end as valor_sin_formato,
 
   CASE
    WHEN LN_indicadores.clave_indicador = 'SCORE_FINAL' THEN
         (select LN_cliente_nivel.texto_largo
         from LN_cliente_nivel inner join LN_indicadores ON LN_cliente_nivel.indicador = LN_indicadores.indicador
         where LN_cliente_nivel.cliente = @p_cliente 
         and LN_cliente_nivel.desde <= LN_contraparte_indicadores.valor_numerico 
         and LN_cliente_nivel.hasta > LN_contraparte_indicadores.valor_numerico
         and LN_indicadores.clave_indicador = 'SCORE_FINAL')
    WHEN LN_indicadores.clave_indicador = 'SCORE_VRM' THEN
      (select LN_cliente_nivel.texto_largo
         from LN_cliente_nivel inner join LN_indicadores ON LN_cliente_nivel.indicador = LN_indicadores.indicador
         where LN_cliente_nivel.cliente = @p_cliente 
         and LN_cliente_nivel.desde <= LN_contraparte_indicadores.valor_numerico 
         and LN_cliente_nivel.hasta > LN_contraparte_indicadores.valor_numerico
         and LN_indicadores.clave_indicador = 'SCORE_VRM')
   ELSE NULL
   END AS texto, 

   ln_contraparte_indicadores.valor_memo


   FROM LN_contrapartes 
   INNER JOIN LN_gestiones ON LN_contrapartes.contraparte = LN_gestiones.contraparte

   INNER JOIN LN_contraparte_indicadores ON (LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
                and LN_contraparte_indicadores.gestion = LN_gestiones.gestion) 
   INNER JOIN LN_indicadores ON LN_contraparte_indicadores.indicador = LN_indicadores.indicador
   inner join ln_productos on ln_productos.producto = ln_indicadores.producto 

   INNER JOIN LN_contraparte_familias ON LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   INNER JOIN LN_familia_productos ON (LN_contraparte_familias.familia = LN_familia_productos.familia
            AND LN_indicadores.producto = LN_familia_productos.producto)


   LEFT OUTER JOIN
    (SELECT LN_docs.contraparte, COUNT(*) AS ingresados
    FROM LN_docs 
    INNER JOIN LN_gestiones ON LN_docs.gestion = LN_gestiones.gestion

    INNER JOIN
     (select LN_docs.contraparte, LN_docs.tipo_doc, max(doc_version) as ultima
     from LN_docs
     inner join LN_gestiones on (LN_docs.gestion = LN_gestiones.gestion 
            AND LN_docs.contraparte = LN_gestiones.contraparte)
     where LN_gestiones.desde <= GETDATE() AND LN_gestiones.hasta >= GETDATE()
     group by LN_docs.contraparte, LN_docs.tipo_doc) ultimas_versiones 

    ON (LN_docs.tipo_doc = ultimas_versiones.tipo_doc 
     AND ln_docs.contraparte = ultimas_versiones.contraparte 
     AND LN_docs.doc_version = ultimas_versiones.ultima)


    WHERE LN_gestiones.desde <= GETDATE() AND LN_gestiones.hasta >= GETDATE()
    AND LN_docs.estado_doc = 'I'
    GROUP BY LN_docs.contraparte) PENDIENTES_VERIFICAR ON LN_contrapartes.contraparte = PENDIENTES_VERIFICAR.contraparte

   LEFT OUTER JOIN
    (select LN_excepciones.contraparte, LN_excepciones.tipo_excepcion, LN_excepciones.gestion
    from LN_contrapartes inner join LN_excepciones
    on LN_contrapartes.contraparte = LN_excepciones.contraparte
    where LN_contrapartes.cliente = @p_cliente
    and LN_excepciones.estado = 1
    and LN_excepciones.vigencia >= GETDATE()) excepciones ON 
            (LN_contrapartes.contraparte = excepciones.contraparte
             --and excepciones.gestion = ln_gestiones.gestion
             ) 
   WHERE LN_contrapartes.cliente = @p_cliente
   AND LN_gestiones.desde <= GETDATE() AND LN_gestiones.hasta >= GETDATE() and LN_gestiones.vigente = 1
   AND LN_indicadores.visible = 1
   AND LN_contrapartes.activo = 1
   --and LN_contrapartes.servicio_contratado = isnull(@p_servicio,LN_contrapartes.servicio_contratado)
   and LN_contrapartes.contraparte    =  isnull(@p_contraparte,LN_contrapartes.contraparte)
   --and LN_contrapartes.subcontratista = isnull(@p_es_subcontratista,subcontratista)
   AND LN_contraparte_familias.activo = 1
   AND LN_familia_productos.activo = 1
   and LN_contraparte_indicadores.visible = 1
   and ln_productos.servicio_contratado in (0,ln_contrapartes.servicio_contratado)
  end
      
  -- Datos generales
  
  declare @ln_pais_servicio varchar(max) = null 
  declare @ln_pais_contraparte varchar(max) = null 
  declare @ln_familia varchar(max) = null 
  declare @ln_nombre_persona varchar(max) = null 
  declare @ln_identificador varchar(max) = null 
  declare @ln_identificacion varchar(max) = null 
  declare @ln_inicio_gestion varchar(max) = null 
  declare @ln_dias_gestion varchar(max) = null 
  declare @ln_detalle_killer varchar(max) = null 
  declare @ln_nombre_contacto varchar(max) = null 
  declare @ln_email_contacto varchar(max) = null 
  declare @ln_telefono_contacto varchar(max) = null 
  declare @ln_datos_comerciales varchar(max) = null 


  SELECT 
   @ln_pais_servicio = isnull(pais_serv.country_name,''),
   @ln_pais_contraparte = country.country_name,
   @ln_familia = LN_familias.nombre_familia,
   @ln_nombre_persona = padrones.nombre_persona,
   @ln_identificador = identificadores.abreviatura,
   @ln_identificacion = padrones.identificacion,
   @ln_inicio_gestion = ln_gestiones.desde,
   @ln_dias_gestion = datediff(dd,ln_gestiones.desde,convert(date,getdate())),
   @ln_detalle_killer = dd.detalle,
   @ln_nombre_contacto = LN_contrapartes.nombre_contacto, 
   @ln_email_contacto = LN_contrapartes.email_contacto, 
   @ln_telefono_contacto = LN_contrapartes.telefono_contacto, 
   @ln_datos_comerciales = replace(convert(varchar(max),LN_contrapartes.datos_comerciales),'<br>',' - ')

   FROM LN_contrapartes
   inner join LN_gestiones on LN_gestiones.contraparte = LN_contrapartes.contraparte 
                                                     
   INNER JOIN padrones ON LN_contrapartes.id_padron = padrones.id_padron
   INNER JOIN identificadores ON padrones.identificador = identificadores.identificador
   INNER JOIN country ON identificadores.country_code = country.country_code
   LEFT OUTER JOIN country pais_serv ON ln_contrapartes.pais_servicio = pais_serv.country_code
   INNER JOIN LN_contraparte_familias ON LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   INNER JOIN LN_familias ON LN_contraparte_familias.familia = LN_familias.familia and LN_familias.cliente = ln_contrapartes.cliente
   INNER JOIN clientes ON LN_contrapartes.cliente = clientes.cliente
   LEFT OUTER JOIN users ON LN_contraparte_familias.created_by = users.user_id

   
   left join (SELECT contraparte, gestion, detalle=STUFF(
        (SELECT distinct '; ' + detalle_killer AS [text()]
        FROM ln_score_detalle_killer XT 
        WHERE XT.contraparte = T.contraparte and XT.visible = 1 and XT.gestion = T.gestion
        FOR XML PATH('')), 1, 2, '') 
      FROM ln_score_detalle_killer T where T.visible = 1
      GROUP BY contraparte,gestion) dd on (dd.contraparte = ln_contrapartes.contraparte and dd.gestion = LN_gestiones.gestion)

   LEFT OUTER JOIN users as users1 ON LN_contraparte_familias.modified_by = users1.user_id
   where LN_contrapartes.cliente = @p_cliente
   and LN_contrapartes.activo = 1
   AND LN_familias.visible = 1
   AND LN_contraparte_familias.activo = 1
   and LN_gestiones.desde <= GETDATE() and LN_gestiones.hasta >= GETDATE() and ln_gestiones.vigente = 1
   and LN_contrapartes.contraparte = @p_contraparte 
  --
 
  -- Borro los datos que tenemos en la tabla PANEL_CONTROL_EXCEL_DATOS
  if (@p_proceso_completo = 1)
  begin
   if (select COUNT(*) from PANEL_CONTROL_EXCEL_DATOS where contraparte = @p_contraparte) > 0
    update PANEL_CONTROL_EXCEL_DATOS set visible = 0 where contraparte =  @p_contraparte

   declare C1_titulos CURSOR FOR 
    select  nro_columna, 
            indicador,
      tipo_dato,
      clave_indicador
     from PANEL_CONTROL_EXCEL_TITULOS   
    where cliente = @p_cliente and visible = 1 and servicio = @p_servicio and es_subcontratista = @p_es_subcontratista 
    order by nro_columna

  end
  else 
  begin
   if (select COUNT(*) from PANEL_CONTROL_EXCEL_DATOS where contraparte = @p_contraparte and (indicador = 0 or indicador between 9990 and 9999)) > 0
    update PANEL_CONTROL_EXCEL_DATOS set visible = 0 
     where contraparte =  @p_contraparte 
       and (indicador = 0 or indicador between 9990 and 9999)
    declare C1_titulos CURSOR FOR 
    select  nro_columna, 
            indicador,
      tipo_dato,
      clave_indicador
     from PANEL_CONTROL_EXCEL_TITULOS   
    where cliente = @p_cliente and visible = 1 and servicio = @p_servicio and es_subcontratista = @p_es_subcontratista 
          and (indicador = 0 or indicador between 9990 and 9999)
    order by nro_columna
  end 
  
  -- Busco las columnas a completar, para la contraparte, de acuerdo a las columnas indicadas en PANEL_CONTROL_JSON_TITULOS 
  -- y completo las columnas del indicador = 0, que son los datos de la contraparte 
  -- y completo las columnad de los indicadores que no son indicadores reales 
  open C1_titulos 
  FETCH NEXT FROM C1_titulos into  @nro_columna, @indicador,
          @tipo_dato,
          @clave_indicador
  WHILE @@FETCH_STATUS = 0 BEGIN
   if (select COUNT(*) from PANEL_CONTROL_EXCEL_DATOS 
        where contraparte = @p_contraparte
       and indicador = @indicador
       and clave_indicador = @clave_indicador) > 0
   begin
    update PANEL_CONTROL_EXCEL_DATOS 
    set servicio = @p_servicio, 
        cliente = @p_cliente,
     pais_servicio = @pais_servicio, familia = @familia,
     visible = 1,
     modified = GETDATE()
    where contraparte = @p_contraparte
       and indicador = @indicador
       and clave_indicador = @clave_indicador
   end 
   else
   begin
    insert into PANEL_CONTROL_EXCEL_DATOS (servicio, cliente, es_subcontratista, contraparte, pais_servicio, familia, indicador, clave_indicador, visible)
    values (@p_servicio, @p_cliente, @p_es_subcontratista, @p_contraparte, @pais_servicio, @familia, @indicador, @clave_indicador, 1) 
   end
   IF (@indicador = 0) 
   BEGIN
     set @d_valor = null
     if @clave_indicador = '0_PAIS_SERVICIO'  begin set @d_valor = @ln_pais_servicio    end
     if @clave_indicador = '0_PAIS'     begin set @d_valor = @ln_pais_contraparte   end
     if @clave_indicador = '0_FAMILIA'    begin set @d_valor = @ln_familia     end
     if @clave_indicador = '0_RAZON_SOCIAL'   begin set @d_valor = @ln_nombre_persona    end
     if @clave_indicador = '0_IDENTIFICADOR'  begin set @d_valor = @ln_identificador    end
     if @clave_indicador = '0_IDENTIFICACION' begin set @d_valor = @ln_identificacion    end
     if @clave_indicador = '0_NOMBRE_CONTACTO' begin set @d_valor = @ln_nombre_contacto   end
     if @clave_indicador = '0_EMAIL_CONTACTO' begin set @d_valor = @ln_email_contacto    end
     if @clave_indicador = '0_TELEFONO_CONTACTO' begin set @d_valor = @ln_telefono_contacto   end
     if @clave_indicador = '0_Detalle_Rechazos' begin set @d_valor = @ln_detalle_killer    end
     if @clave_indicador = '0_DATOS_COMERCIALES' begin set @d_valor = @ln_datos_comerciales   end
     if @clave_indicador = '0_PACTO_MUNDIAL'     begin 
      declare @valores table (contraparte int, gestion int, doc_id int, seccion int, campo int, tipo_campo varchar(1), nombre_campo varchar(200), etiqueta varchar(300),fila int, col int, valor_numerico float, valor_texto varchar(max), valor_fecha smalldatetime, valor_fecha_srt varchar(200))
      insert into @valores exec ln_docs_valores @p_contraparte,  'PACTO_MUNDIAL'  
       select @d_valor =  case
           when valor_numerico = 1 then 'SI'
           when valor_numerico = 0 then 'NO'
           else ''
           end
           from @valores
     end

     if @clave_indicador = '0_Meses_Deuda_Aportes' begin 
      select @d_valor = STUFF((
      SELECT '-' + SUBSTRING(convert(varchar,periodo),5,2) + '/' + SUBSTRING(convert(varchar,periodo),1,4)  
      from deuda_previsional
      inner join padrones on (identificacion = cuit and habilitado = 1)
      where cuit = @ln_identificacion and codigo = 301 and periodo is not null 
      and visible = 1 
      order by periodo 
      FOR XML PATH('')
      ), 1, 1, '') 
     end

     if @clave_indicador = '0_Meses_Deuda_Contribuciones' begin 
      select @d_valor = STUFF((
      SELECT '-' + SUBSTRING(convert(varchar,periodo),5,2) + '/' + SUBSTRING(convert(varchar,periodo),1,4)  
      from deuda_previsional
      inner join padrones on (identificacion = cuit and habilitado = 1)
      where cuit = @ln_identificacion and codigo = 351 and periodo is not null 
      and visible = 1 
      order by periodo 
      FOR XML PATH('')
      ), 1, 1, '')  
     end

     declare @clave_indicador2 varchar(100) = ''
     if @clave_indicador like '0_TRABAJADORES_%' or @clave_indicador like '0_NA_TRABAJADORES'  
     begin
      delete from @personal
      set @clave_indicador2 = replace(@clave_indicador,'0_','')
      insert into @personal exec ln_docs_valores @p_contraparte,  @clave_indicador2
      set @d_valor = (select top 1 iif(valor_texto is null, convert(varchar,isnull(valor_numerico,'0')), 'X') from @personal)
     end
     update PANEL_CONTROL_EXCEL_DATOS set 
       valor = @d_valor,
       valor_sin_formato = null,
       texto = null,
       visible = 1
       where contraparte = @p_contraparte and indicador = @indicador and clave_indicador = @clave_indicador 
    END

    IF (@indicador = 9990) 
    BEGIN
     if exists (select valor_memo from @datos where contraparte = @p_contraparte and indicador = 37)
     begin
      set @d_valor = (select top 1 valor_memo from @datos where contraparte = @p_contraparte and indicador = 37)
      update PANEL_CONTROL_EXCEL_DATOS set 
        valor = @d_valor,
        valor_sin_formato = null,
        texto = null, 
        visible = 1
        where contraparte = @p_contraparte and indicador = 9990 
     end
    END

    IF (@indicador = 9995) 
    BEGIN
     if exists (select texto from @datos where contraparte = @p_contraparte and indicador = 4)
     begin
      set @d_valor = (select top 1 isnull(texto,'') from @datos where contraparte = @p_contraparte and indicador = 4)
      
      update PANEL_CONTROL_EXCEL_DATOS set 
        valor = @d_valor,
        valor_sin_formato = null,
        texto = null, 
        visible = 1
        where contraparte = @p_contraparte and indicador = 9995
     end
    END

    declare @datos_cruce varchar(max) = '' 
    declare @cuit varchar(20), @nombre varchar(500), @cargo varchar(100), @relacion varchar(100), @empleado_cuit varchar(20), @empleado_nombre varchar(500)

    IF (@indicador = 9993) 
    BEGIN
     declare @control_etica table (cuit varchar(20), nombre varchar(500), cargo varchar(100), relacion varchar(100), empleado_cuit varchar(20), empleado_nombre varchar(500))
     insert into @control_etica exec LN_control_etica @p_contraparte 
     declare C_etica CURSOR FOR 
      select cuit, nombre, cargo, relacion, empleado_cuit, empleado_nombre
        from @control_etica
     OPEN C_etica 
     FETCH NEXT FROM C_etica INTO  @cuit, @nombre, @cargo, @relacion, @empleado_cuit, @empleado_nombre 

     WHILE @@FETCH_STATUS = 0 BEGIN
      if @datos_cruce = '' 
       set @datos_cruce =  isnull(@cuit,'') + ' ' + isnull(@nombre,'') + ' - ' + isnull(@cargo,'') 
      else 
       set @datos_cruce = @datos_cruce + '<br>' + isnull(@cuit,'') + ' ' + isnull(@nombre,'') + ' - ' + isnull(@cargo,'') 

      FETCH NEXT FROM C_etica INTO  @cuit, @nombre, @cargo, @relacion, @empleado_cuit, @empleado_nombre 
     END 
     CLOSE C_etica
     DEALLOCATE C_etica

     SET @d_valor = @datos_cruce 
     if @d_valor = '' set @d_valor = null 

     update PANEL_CONTROL_EXCEL_DATOS set 
      valor = @d_valor,
      valor_sin_formato = null,
      texto = null,
      visible = 1
      where contraparte = @p_contraparte and indicador = @indicador and clave_indicador = @clave_indicador 
    END

    IF (@indicador = 9994) 
    BEGIN
     declare @control_calidad table (cuit varchar(20), nombre varchar(500), cargo varchar(100))
     insert into @control_calidad exec LN_control_calidad @p_contraparte 
     declare C_calidad CURSOR FOR 
      select cuit, nombre, cargo 
        from @control_calidad
     OPEN C_calidad 
     FETCH NEXT FROM C_calidad INTO  @cuit, @nombre, @cargo 

     WHILE @@FETCH_STATUS = 0 BEGIN
      if @datos_cruce = '' 
       set @datos_cruce =  isnull(@cuit,'') + ' ' + isnull(@nombre,'') + ' - ' + isnull(@cargo,'') 
      else 
       set @datos_cruce = @datos_cruce + '<br>' + isnull(@cuit,'') + ' ' + isnull(@nombre,'') + ' - ' + isnull(@cargo,'') 

      FETCH NEXT FROM C_calidad INTO  @cuit, @nombre, @cargo 
     END 
     CLOSE C_calidad
     DEALLOCATE C_calidad

     SET @d_valor = @datos_cruce 
     if @d_valor = '' set @d_valor = null 

     update PANEL_CONTROL_EXCEL_DATOS set 
      valor = @d_valor,
      valor_sin_formato = null,
      texto = null,
      visible = 1
      where contraparte = @p_contraparte and indicador = @indicador and clave_indicador = @clave_indicador 
    END

    IF (@indicador in (9999,9998,9997,9996))
    BEGIN
      declare @estado_doc char(1) = ''
      set @d_valor = null 
      if @indicador = 9996 set @estado_doc = 'V'
      if @indicador = 9997 set @estado_doc = 'I'
      if @indicador = 9998 set @estado_doc = 'R'
      if @indicador = 9999 set @estado_doc = 'N'

      declare C_documentos CURSOR FOR 

       SELECT DISTINCT nombre_tipo_doc
       FROM ln_docs 
       INNER JOIN LN_tipos_doc ON LN_docs.tipo_doc = LN_tipos_doc.tipo_doc

       INNER JOIN LN_gestiones ON (ln_docs.contraparte = LN_gestiones.contraparte AND LN_docs.gestion = LN_gestiones.gestion)

       INNER JOIN LN_contraparte_familias ON LN_docs.contraparte = LN_contraparte_familias.contraparte
       INNER JOIN LN_familias ON LN_contraparte_familias.familia = LN_familias.familia

       INNER JOIN LN_set_tipo_doc ON (LN_familias.set_doc = LN_set_tipo_doc.set_doc
                 AND LN_tipos_doc.tipo_doc = LN_set_tipo_doc.tipo_doc
                 AND LN_set_tipo_doc.activo = 1)
       WHERE LN_docs.contraparte = @p_contraparte 
         and ln_gestiones.vigente = 1 and ln_gestiones.desde <= getdate() and ln_gestiones.hasta >= getdate()
         and estado_doc = @estado_doc 
       order by nombre_tipo_doc 

      declare @documentos varchar(max) = '';
      declare @documento  varchar(max) = '';

      OPEN C_documentos 
      FETCH NEXT FROM C_documentos INTO @documento
      WHILE @@FETCH_STATUS = 0 BEGIN
       if @documentos = '' 
        set @documentos = @documento
       else 
        set @documentos = @documentos + '<br>' +  @documento

       FETCH NEXT FROM C_documentos INTO @documento

      END 
      CLOSE C_documentos
      DEALLOCATE C_documentos
      SET @d_valor = @documentos 
      if @d_valor = '' set @d_valor = null 

      update PANEL_CONTROL_EXCEL_DATOS set 
       valor = @d_valor,
       valor_sin_formato = null,
       texto = null,
       visible = 1
       where contraparte = @p_contraparte and indicador = @indicador and clave_indicador = @clave_indicador 
   END
   FETCH NEXT FROM C1_titulos into  @nro_columna, @indicador,
           @tipo_dato,
           @clave_indicador

  END
  CLOSE C1_titulos
  DEALLOCATE C1_titulos


  -- Busca los valores de las columnas de los distintos indicadores

  if (@p_proceso_completo = 1)
  begin
   declare C_cursor CURSOR FOR 
     select  contraparte,
       indicador,
       clave_indicador,
       valor,
       valor_sin_formato,
       texto,
       valor_memo
      from @datos
   open C_Cursor 
   FETCH NEXT FROM C_Cursor INTO @d_contraparte,
           @d_indicador,
           @d_clave_indicador,
           @d_valor,
           @d_valor_sin_formato,
           @d_texto,
           @d_valor_memo

   WHILE @@FETCH_STATUS = 0 BEGIN

     if(@indicador = 361 or @indicador = 360) 
     begin
      set @d_valor = @d_valor_memo
     end

     if (@indicador = 6 and @d_valor is null) 
     BEGIN
      if exists (select valor from @datos where contraparte = @p_contraparte and indicador = 37)
      begin
       select @d_valor = isnull(valor,''), 
           @d_valor_sin_formato = valor_sin_formato,
           @d_texto = texto 
       from @datos where contraparte = @p_contraparte and indicador = 37 
      end
     END 
     
     if (@d_valor = '-100') set @d_valor = 'N/A'

     update PANEL_CONTROL_EXCEL_DATOS set 
      valor = @d_valor,
      valor_sin_formato = @d_valor_sin_formato,
      texto = @d_texto, 
      visible = 1
      where contraparte = @d_contraparte and indicador = @d_indicador and clave_indicador = @d_clave_indicador
       
     if (@indicador = 37 and exists (select valor from @datos where contraparte = @p_contraparte and indicador = 6))
     begin
      update PANEL_CONTROL_EXCEL_DATOS set visible = 0 
      where contraparte = @d_contraparte and indicador = @d_indicador and clave_indicador = @d_clave_indicador
     end 
     
     FETCH NEXT FROM C_Cursor INTO @d_contraparte,
             @d_indicador,
             @d_clave_indicador,
             @d_valor,
             @d_valor_sin_formato,
             @d_texto,
             @d_valor_memo
   END

   CLOSE C_Cursor
   DEALLOCATE C_Cursor
  end
   /* 
    select T.nro_columna,T.clave_indicador, T.nombre_producto, T.etiqueta,
           D.valor, D.valor_sin_formato, D.texto
    from PANEL_CONTROL_EXCEL_DATOS D
    inner join PANEL_CONTROL_EXCEL_TITULOS T  on (T.clave_indicador = D.clave_indicador and T.indicador = D.indicador and T.cliente = D.cliente and T.servicio = D.servicio and T.es_subcontratista = D.es_subcontratista)
    where D.contraparte = @p_contraparte and D.visible = 1 and T.visible = 1 
    order by T.nro_columna 
    */
end


 

END 

--select * from PANEL_CONTROL_EXCEL_TITULOS where cliente = 5546348

--LN_panel_control_excel_datos_comerciales 55456348,107,47
GO