dbo.IV_panel_control_new

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
/*

exec IV_panel_control 'productos_backend',NULL,98092071,10


exec IV_panel_control 'select',1068109769,912763619,10


select * from users where username = 'naranja'

*/

-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.IV_panel_control_new
 -- Add the parameters for the stored procedure here

 @accion        varchar(50) = null,
 @p_cliente       int = null,
 @p_user_id       int = null,
 @p_id_producto      int = null,



 /** para ingresar_busqueda de un nuevo candidato **/
 @p_id_tipo_informe     int = null,
 @p_id_padron      int = null,
 @p_familia       int = null,
 @p_referencia      varchar(200) = null,
 @p_ref        varchar(20) = null,
 @p_ref_nombre      varchar(100) = null,
 @p_ref_seccion      varchar(100) = null,
 @p_ref_cargo      varchar(100) = null,
 @p_email_candidato     varchar(100) = null,
 @p_telefono_candidato    varchar(100) = null,
 @p_notas_candidato     text = null,

 @p_filial       smallint = null,
 @p_idioma       smallint = null,
 @p_datamanager      int = null,
 @p_asignado       int = null,
 @p_estado_inicial     varchar(1) = null,
 @p_fecha_limite_doc     datetime = null,
 @p_fecha_comprometida    datetime = NULL,

 @p_doc_id       int = NULL,

 @p_asignado_gestion     int = NULL,

 @p_contraparte      int = NULL,
 @p_clave_indicador     varchar(50) = NULL,
 @p_valor_texto      varchar(1) = NULL


 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 DECLARE @ahora datetime = GETDATE()
 DECLARE @usuario varchar(100) = NULL
 DECLARE @preffix varchar(200) = ''

 declare @producto_led int = null
 declare @indicador int = NULL
 declare @gestion int = null

 if (@p_user_id > 0) begin
  select @usuario = isnull(first_name,'') + ' ' + isnull(last_name, '')
  from users where user_id = @p_user_id

  SET @preffix = '['+@usuario+' - '+convert(varchar(10),@ahora,103) + ' ' + substring(convert(varchar,@ahora,121),12,5)+']: '
 end else
  SET @preffix = '['+convert(varchar(10),@ahora,103) + ' ' + substring(convert(varchar,@ahora,121),12,5)+']: '


 select @producto_led = producto from LN_productos where producto_consumos = 10


 /******************************************************************/

 if (@accion = 'columnas' and @p_cliente is not null) begin

  SELECT * FROM
   (

   select NULL as indicador, NULL AS nombre_indicador, 'País' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -10 as orden_producto,
     'country' as clave_indicador, 1 as orden,
     null as producto_id, NULL AS class_icon,
     60 as width, 'center' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu
   
   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Familia' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -9 as orden_producto,
     'familia' as clave_indicador, 1 as orden,
     null as producto_id, NULL AS class_icon,
     75 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu


   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Foto' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -8 as orden_producto,
     'foto_60' as clave_indicador, 1 as orden,
     null as producto_id, NULL AS class_icon,
     60 as width, 'center' as align, 0 as sortable,
     1 as locked, 0 as searchable, 0 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Apellido y Nombres' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -7 as orden_producto,
     'nombre_persona' as clave_indicador, 1 as orden,
     null as producto_id, NULL AS class_icon,
     200 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 1 as menu

   /*UNION
   
    select NULL as indicador, NULL AS nombre_indicador, 'Identificación' as etiqueta_indicador,
    NULL AS etiqueta,
    'S' as tipo_dato, -6 as orden_producto,
    'identificacion'as clave_indicador, 1 as orden,
     NULL as producto_id, NULL AS class_icon,
    80 as width, 'center' as align, 1 as sortable,
    1 as locked, 1 as searchable, 1 as menu*/

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Fecha' as etiqueta_indicador,
     NULL AS etiqueta,
     'D' as tipo_dato, -5 as orden_producto,
     'creado' as clave_indicador, 1 as orden,
     null as producto_id, NULL AS class_icon,
     80 as width, 'center' as align, 1 as sortable,
     1 as locked, 0 as searchable, 1 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Referencia' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -4 as orden_producto,
     'referencia' as clave_indicador, 1 as orden,
     NULL as producto_id, NULL AS class_icon,
     140 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 1 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Sector' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -3 as orden_producto,
     'ref_seccion' as clave_indicador, 1 as orden,
     NULL as producto_id, NULL AS class_icon,
     130 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 1 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Cargo' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -2 as orden_producto,
     'ref_cargo' as clave_indicador, 1 as orden,
     NULL as producto_id, NULL AS class_icon,
     100 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 1 as menu

   /*UNION
   
    select NULL as indicador, NULL AS nombre_indicador, 'Ingresado por' as etiqueta_indicador,
    NULL AS etiqueta,
    'S' as tipo_dato, -1 as orden_producto,
    'ingresado_por' as clave_indicador, 1 as orden,
    NULL as producto_id, NULL AS class_icon,
    150 as width, 'left' as align, 1 as sortable,
    1 as locked, 1 as searchable, 1 as menu*/
   
   /*
    UNION
   
    select NULL as indicador, NULL AS nombre_indicador, 'CV' as etiqueta_indicador,
    NULL AS etiqueta,
    'S' as tipo_dato, 0 as orden_producto,
    'cv' as clave_indicador, 1 as orden,
    NULL as producto_id, 'fa fa-file-text' AS class_icon,
    90 as width, 'center' as align, 1 as sortable,
    1 as locked, 1 as searchable, 0 as menu
    */  

   UNION

   -- producto Dossier Inquiro Vitae
   select LN_cliente_indicadores.indicador, LN_indicadores.nombre_indicador, LN_indicadores.etiqueta as etiqueta_indicador,
     ISNULL(LN_cliente_productos.etiqueta_personalizada, LN_productos.nombre_producto) AS etiqueta, 
     LN_indicadores.tipo_dato, LN_cliente_productos.orden_producto,
     LN_indicadores.clave_indicador, LN_indicadores.orden, 
     LN_productos.producto_id, 

     case
      when LN_indicadores.clave_indicador = 'ACADEMICO' then 'fas fa-graduation-cap'
      when LN_indicadores.clave_indicador = 'PROFESIONAL' then 'fas fa-user-tie'
      when LN_indicadores.clave_indicador = 'COMERCIAL_LEGAL' then 'fas fa-gavel'
      when LN_indicadores.clave_indicador = 'COMPLIANCE' then 'fas fa-address-card'
      when LN_indicadores.clave_indicador = 'IV_CV' then 'fas fa-file-text'
      else LN_productos.class_icon
     end as class_icon,

     115 as width, 'center' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu
   from LN_cliente_indicadores 
   inner join LN_indicadores 
    on LN_cliente_indicadores.indicador = LN_indicadores.indicador
   inner join LN_productos on LN_indicadores.producto = LN_productos.producto
   inner join LN_cliente_productos on LN_productos.producto = LN_cliente_productos.producto
   where LN_cliente_productos.cliente = @p_cliente
   and LN_productos.producto = @producto_led
   and LN_cliente_productos.activo = 1
   and LN_indicadores.visible = 1 

   /*UNION
   
    -- producto Documentos
    select LN_indicadores.indicador, LN_indicadores.nombre_indicador, LN_indicadores.etiqueta as etiqueta_indicador,
    ISNULL(LN_cliente_productos.etiqueta_personalizada, LN_productos.nombre_producto) AS etiqueta, 
    LN_indicadores.tipo_dato, LN_cliente_productos.orden_producto,
    LN_indicadores.clave_indicador, LN_indicadores.orden, 
    LN_productos.producto_id, LN_productos.class_icon,
    90 as width, 'right' as align, 1 as sortable,
    0 as locked, 0 as searchable, 1 as menu
    from LN_indicadores 
    inner join LN_productos on LN_indicadores.producto = LN_productos.producto
    inner join LN_cliente_productos on LN_productos.producto = LN_cliente_productos.producto
    inner join LN_familias on LN_cliente_productos.cliente = LN_familias.cliente
    inner join LN_familia_productos on (LN_familias.familia = LN_familia_productos.familia
    and LN_cliente_productos.producto = LN_familia_productos.producto)
    where LN_cliente_productos.cliente = @p_cliente
    and LN_productos.documentos = 1
    and LN_cliente_productos.activo = 1
    and LN_familia_productos.activo = 1
    and LN_familias.uso_familia = 'I'
    group by LN_indicadores.indicador, LN_indicadores.nombre_indicador, LN_indicadores.etiqueta,
    LN_cliente_productos.etiqueta_personalizada, LN_productos.nombre_producto, 
    LN_indicadores.tipo_dato, LN_cliente_productos.orden_producto,
    LN_indicadores.clave_indicador, LN_indicadores.orden, 
    LN_productos.producto_id, LN_productos.class_icon*/
   ) cols
  ORDER BY cols.orden_producto, cols.orden

  

 end

 if (@accion = 'columnasr' and @p_cliente is not null and @p_user_id is not null) begin

  SELECT * FROM
   (

   select NULL as indicador, NULL AS nombre_indicador, 'País' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -10 as orden_producto,
     'country' as clave_indicador, -5 as orden,
     null as producto_id, NULL AS class_icon,
     60 as width, 'center' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu
   
   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Familia' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -9 as orden_producto,
     'familia' as clave_indicador, -4 as orden,
     null as producto_id, NULL AS class_icon,
     75 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu


   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Foto' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -8 as orden_producto,
     'foto_60' as clave_indicador, -6 as orden,
     null as producto_id, NULL AS class_icon,
     60 as width, 'center' as align, 0 as sortable,
     1 as locked, 0 as searchable, 0 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Apellido y Nombres' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -7 as orden_producto,
     'nombre_persona' as clave_indicador, -5 as orden,
     null as producto_id, NULL AS class_icon,
     200 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu

   /*UNION
   
    select NULL as indicador, NULL AS nombre_indicador, 'Identificación' as etiqueta_indicador,
    NULL AS etiqueta,
    'S' as tipo_dato, -6 as orden_producto,
    'identificacion'as clave_indicador, 1 as orden,
     NULL as producto_id, NULL AS class_icon,
    80 as width, 'center' as align, 1 as sortable,
    1 as locked, 1 as searchable, 1 as menu*/

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Fecha' as etiqueta_indicador,
     NULL AS etiqueta,
     'D' as tipo_dato, -5 as orden_producto,
     'creado' as clave_indicador, -4 as orden,
     null as producto_id, NULL AS class_icon,
     80 as width, 'center' as align, 1 as sortable,
     1 as locked, 0 as searchable,  0 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Referencia' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -4 as orden_producto,
     'referencia' as clave_indicador, -3 as orden,
     NULL as producto_id, NULL AS class_icon,
     140 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Sector' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -3 as orden_producto,
     'ref_seccion' as clave_indicador, -2 as orden,
     NULL as producto_id, NULL AS class_icon,
     130 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu

   UNION

   select NULL as indicador, NULL AS nombre_indicador, 'Cargo' as etiqueta_indicador,
     NULL AS etiqueta,
     'S' as tipo_dato, -2 as orden_producto,
     'ref_cargo' as clave_indicador, -1 as orden,
     NULL as producto_id, NULL AS class_icon,
     100 as width, 'left' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu

   /*UNION
   
    select NULL as indicador, NULL AS nombre_indicador, 'Ingresado por' as etiqueta_indicador,
    NULL AS etiqueta,
    'S' as tipo_dato, -1 as orden_producto,
    'ingresado_por' as clave_indicador, 1 as orden,
    NULL as producto_id, NULL AS class_icon,
    150 as width, 'left' as align, 1 as sortable,
    1 as locked, 1 as searchable, 1 as menu*/
   
   /*
    UNION
   
    select NULL as indicador, NULL AS nombre_indicador, 'CV' as etiqueta_indicador,
    NULL AS etiqueta,
    'S' as tipo_dato, 0 as orden_producto,
    'cv' as clave_indicador, 1 as orden,
    NULL as producto_id, 'fa fa-file-text' AS class_icon,
    90 as width, 'center' as align, 1 as sortable,
    1 as locked, 1 as searchable, 0 as menu
    */  

   UNION

   -- producto Dossier Inquiro Vitae
   select LN_cliente_indicadores.indicador, LN_indicadores.nombre_indicador, LN_indicadores.etiqueta as etiqueta_indicador,
     ISNULL(LN_cliente_productos.etiqueta_personalizada, LN_productos.nombre_producto) AS etiqueta, 
     LN_indicadores.tipo_dato, LN_cliente_productos.orden_producto,
     LN_indicadores.clave_indicador, LN_indicadores.orden, 
     LN_productos.producto_id, 

     case
      when LN_indicadores.clave_indicador = 'ACADEMICO' then 'fas fa-graduation-cap'
      when LN_indicadores.clave_indicador = 'PROFESIONAL' then 'fas fa-user-tie'
      when LN_indicadores.clave_indicador = 'COMERCIAL_LEGAL' then 'fas fa-gavel'
      when LN_indicadores.clave_indicador = 'COMPLIANCE' then 'fas fa-address-card'
      when LN_indicadores.clave_indicador = 'IV_CV' then 'fas fa-file-text'
      else LN_productos.class_icon
     end as class_icon,

     115 as width, 'center' as align, 1 as sortable,
     1 as locked, 1 as searchable, 0 as menu
   from LN_cliente_indicadores 
   inner join LN_indicadores 
    on LN_cliente_indicadores.indicador = LN_indicadores.indicador
   inner join LN_productos on LN_indicadores.producto = LN_productos.producto
   inner join LN_cliente_productos on LN_productos.producto = LN_cliente_productos.producto
   where LN_cliente_productos.cliente = @p_cliente
   and LN_productos.producto = @producto_led
   and LN_cliente_productos.activo = 1
   and LN_indicadores.visible = 1 
   and not exists 
   (select * from usuarios_restricciones 
   where cliente = @p_cliente 
   and user_id = @p_user_id 
   and indicador = LN_indicadores.indicador) 
   ) cols
  ORDER BY cols.orden_producto, cols.orden

  

 end


 if (@accion = 'panel_control' and @p_cliente is not null) begin


  select convert(varchar, LN_contrapartes.cliente) + '/' + 
    convert(varchar, LN_contrapartes.contraparte) + '/' + 
    convert(varchar, LN_gestiones.gestion) + '/' + RIGHT('00000000'+convert(varchar,LN_gestiones.iv_busqueda),8)+'_foto_sq.jpg' AS foto,
    convert(varchar, LN_contrapartes.cliente) + '/' + 
    convert(varchar, LN_contrapartes.contraparte) + '/' + 
    convert(varchar, LN_gestiones.gestion) + '/' + RIGHT('00000000'+convert(varchar,LN_gestiones.iv_busqueda),8)+'_foto_60.jpg' AS foto_60,
    convert(varchar, LN_contrapartes.cliente) + '/' + 
    convert(varchar, LN_contrapartes.contraparte) + '/' + 
    convert(varchar, LN_gestiones.gestion) + '/' + RIGHT('00000000'+convert(varchar,LN_gestiones.iv_busqueda),8)+'_foto_120.jpg' AS foto_120,
    LN_gestiones.iv_busqueda, LN_contrapartes.contraparte, 
    LN_gestiones.gestion, identificadores.country_code, country.country_name, 
    LN_familias.nombre_familia as familia, convert(varchar,LN_contrapartes.created,103) as created, convert(varchar,IV_busquedas.creado,103) as creado,
    padrones.nombre_persona + ' <div style="display:none;">' + padrones.identificacion + '</div><div style="display:none;" class="candidato">'+convert(varchar,LN_contrapartes.contraparte)+'</div>' as nombre_persona, 
    identificadores.abreviatura, padrones.identificacion, IV_busquedas.ref, IV_busquedas.ref_nombre,
    ISNULL(IV_busquedas.ref, '') + ' - ' + ISNULL(IV_busquedas.ref_nombre, '') AS referencia,
    IV_busquedas.ref_cargo, IV_busquedas.ref_seccion, 
    IV_busquedas.informe, productos.sigla, users.first_name, users.last_name, 
    users.first_name + ' ' + users.last_name as ingresado_por, 
    /*'<i class="fas fa-file-alt fa-2x"></i>' as cv,*/

    --iif(IV_busquedas.cv_existe > 0, '<i class="fa fa-check text-success fa-2x"></i>', '<i class="fa fa-times text-danger fa-2x"></i>') as cv,
    iif(IV_busquedas.cv_existe > 0, 'fa-check text-success', 'fa-times text-danger') as cv,
    iif(IV_busquedas.cv_existe > 0,1,0) as cv_existe, 
    users.email
  from LN_contrapartes
  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
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join IV_busquedas on LN_gestiones.iv_busqueda = IV_busquedas.iv_busqueda
  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 join informes on IV_busquedas.informe = informes.informe
  left join tipos_informe on informes.id_tipo_informe = tipos_informe.id_tipo_informe
  left join users on informes.ingresado_por = users.user_id
  left outer join contratos_items on IV_busquedas.item_contrato = contratos_items.item_contrato
  left outer join productos on contratos_items.producto = productos.producto

  where LN_contrapartes.cliente = @p_cliente
  and LN_familias.uso_familia = 'I'
  and LN_contrapartes.activo = 1
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  order by IV_busquedas.creado desc, country.country_name, padrones.nombre_persona


 end

 /* para obtener los valores de indicadores de las contrapartes IV del cliente */
 if (@accion = 'indicadores_panel_control') begin

  if (@p_cliente is not null) begin

   /** IMPORTANTE :
    Por ahora pido indicadores del producto asociado al IV con suscripción de indicadores
    y el de documentos hasta tanto todos los productos LED estén configurados con suspcripción de indicadores **/

    /* los de dossier que van por suscripción */
    select LN_contraparte_indicadores.contraparte,
      LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
      LN_contraparte_indicadores.valor_numerico,
      LN_contraparte_indicadores.valor_fecha,
      LN_contraparte_indicadores.valor_texto
    from LN_cliente_indicadores
    inner join LN_indicadores on LN_cliente_indicadores.indicador = LN_indicadores.indicador
    inner join LN_cliente_productos on LN_indicadores.producto = LN_cliente_productos.producto
    inner join LN_contraparte_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    inner join LN_contrapartes on (LN_contraparte_indicadores.contraparte = LN_contrapartes.contraparte
            and LN_cliente_productos.cliente = LN_contrapartes.cliente)
    inner join LN_productos on LN_cliente_productos.producto = LN_productos.producto
    inner join LN_gestiones on (LN_contraparte_indicadores.contraparte = LN_gestiones.contraparte
           and LN_contraparte_indicadores.gestion = LN_gestiones.gestion)
    where LN_gestiones.cliente = @p_cliente
    and LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_cliente_productos.activo = 1
    and LN_contrapartes.activo = 1
    and ln_contrapartes.servicio_contratado = 10
    and LN_productos.producto_consumos = 10


    UNION

    /* los de documentos */
    select LN_contraparte_indicadores.contraparte,
      LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
      LN_contraparte_indicadores.valor_numerico,
      LN_contraparte_indicadores.valor_fecha,
      LN_contraparte_indicadores.valor_texto

    from LN_contraparte_indicadores 
    inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
    inner join LN_cliente_productos on LN_indicadores.producto = LN_cliente_productos.producto
    inner join LN_contrapartes on (LN_contraparte_indicadores.contraparte = LN_contrapartes.contraparte
            and LN_cliente_productos.cliente = LN_contrapartes.cliente)
    inner join LN_productos on LN_cliente_productos.producto = LN_productos.producto
    inner join LN_familias on LN_cliente_productos.cliente = LN_familias.cliente
    inner join LN_familia_productos on (LN_familias.familia = LN_familia_productos.familia
             and LN_cliente_productos.producto = LN_familia_productos.producto)
    inner join LN_gestiones on (LN_contraparte_indicadores.contraparte = LN_gestiones.contraparte
           and LN_contraparte_indicadores.gestion = LN_gestiones.gestion)
    where LN_gestiones.cliente = @p_cliente
    and LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_cliente_productos.activo = 1
    and LN_familia_productos.activo = 1
    and LN_productos.documentos = 1
    and LN_familias.uso_familia = 'I'
    and LN_contrapartes.activo = 1
    and ln_contrapartes.servicio_contratado = 10



  end

 end


 /* para obtener los valores de indicadores de las contrapartes IV del cliente */
 if (@accion = 'indicadores_panel_controlr' and @p_cliente is not null and @p_user_id is not null ) begin
    select LN_contraparte_indicadores.contraparte,
      LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
      LN_contraparte_indicadores.valor_numerico,
      LN_contraparte_indicadores.valor_fecha,
      LN_contraparte_indicadores.valor_texto, 
      LN_indicadores.orden
      ,isnull(LN_demo.visible,0) as visible, 
    case 
    when ln_indicadores.clave_indicador = 'IV_CV' then 
     case 
      when LN_contraparte_indicadores.valor_numerico = 1 then 'Ingresado' 
      else 'No obtenido'
     end 
    else 
     case 
     when LN_contraparte_indicadores.valor_texto = 'P' then 'Ok'
     when LN_contraparte_indicadores.valor_texto = 'O' then 'Con Observaciones'
     when LN_contraparte_indicadores.valor_texto = 'N' then 'Alerta'
     when LN_contraparte_indicadores.valor_texto = 'X' then 'No obtenido'
     when LN_contraparte_indicadores.valor_texto = 'S' then 'No contratado'
     else 'En proceso'
     end 
    end as titulo, 

    case 
    when ln_indicadores.clave_indicador = 'IV_CV' then 
     case 
      when LN_contraparte_indicadores.valor_numerico = 1 then 'S' 
      else 'D'
     end 
    else 
     case 
     when LN_contraparte_indicadores.valor_texto = 'P' then 'S'
     when LN_contraparte_indicadores.valor_texto = 'O' then 'W'
     when LN_contraparte_indicadores.valor_texto = 'N' then 'D'
     when LN_contraparte_indicadores.valor_texto = 'X' then 'I'
     when LN_contraparte_indicadores.valor_texto = 'S' then 'I'
     else 'XX3'
     end 
    end as cls, 

    case 
    when ln_indicadores.clave_indicador = 'IV_CV' then 
     case 
      when LN_contraparte_indicadores.valor_numerico = 1 then 'fa-check-circle' 
      else 'fa-circle'
     end 
    else 
     case 
     when LN_contraparte_indicadores.valor_texto = 'P' then 'fa-check-circle'
     when LN_contraparte_indicadores.valor_texto = 'O' then 'fa-exclamation-circle'
     when LN_contraparte_indicadores.valor_texto = 'N' then 'fa-times-circle'
     when LN_contraparte_indicadores.valor_texto = 'X' then 'fa-circle'
     when LN_contraparte_indicadores.valor_texto = 'S' then 'fa-minus'
     else 'fa-spinner'
     end 
    end as icono, 

    case 
    when ln_indicadores.clave_indicador = 'IV_CV' then 
     case 
      when LN_contraparte_indicadores.valor_numerico = 1 then '#89ab5f' 
      else '#d3d3d3'
     end 
    else 
     case 
     when LN_contraparte_indicadores.valor_texto = 'P' then '#89ab5f'
     when LN_contraparte_indicadores.valor_texto = 'O' then '#fab724'
     when LN_contraparte_indicadores.valor_texto = 'N' then '#a72b2a'
     when LN_contraparte_indicadores.valor_texto = 'X' then '#d3d3d3'
     when LN_contraparte_indicadores.valor_texto = 'S' then '#d3d3d3'
     else '#d3d3d3'
     end 
    end as color  

    from LN_cliente_indicadores
    inner join LN_indicadores on LN_cliente_indicadores.indicador = LN_indicadores.indicador
    inner join LN_cliente_productos on LN_indicadores.producto = LN_cliente_productos.producto
    inner join LN_contraparte_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    inner join LN_contrapartes on (LN_contraparte_indicadores.contraparte = LN_contrapartes.contraparte
            and LN_cliente_productos.cliente = LN_contrapartes.cliente)
    inner join LN_productos on LN_cliente_productos.producto = LN_productos.producto
    inner join LN_gestiones on (LN_contraparte_indicadores.contraparte = LN_gestiones.contraparte
           and LN_contraparte_indicadores.gestion = LN_gestiones.gestion)
    left join ln_demo on ln_gestiones.gestion=ln_demo.gestion 
    where LN_gestiones.cliente = @p_cliente
    and LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_cliente_productos.activo = 1
    and LN_contrapartes.activo = 1
    and ln_contrapartes.servicio_contratado = 10
    and LN_productos.producto_consumos = 10
    --and LN_indicadores.clave_indicador not in ('VALORACION_IV','ACADEMICO','PROFESIONAL','COMERCIAL_LEGAL','COMPLIANCE','PRENSAIV','IV_CV')
    and not exists 
     (select * from usuarios_restricciones 
     where cliente = @p_cliente 
     and user_id = @p_user_id 
     and indicador = LN_indicadores.indicador) 
   
    UNION

    /* los de documentos */
    select LN_contraparte_indicadores.contraparte,
      LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
      LN_contraparte_indicadores.valor_numerico,
      LN_contraparte_indicadores.valor_fecha,
      LN_contraparte_indicadores.valor_texto, 
      LN_indicadores.orden
      ,isnull(LN_demo.visible,0) as visible, null, null, null, null

    from LN_contraparte_indicadores 
    inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
    inner join LN_cliente_productos on LN_indicadores.producto = LN_cliente_productos.producto
    inner join LN_contrapartes on (LN_contraparte_indicadores.contraparte = LN_contrapartes.contraparte
            and LN_cliente_productos.cliente = LN_contrapartes.cliente)
    inner join LN_productos on LN_cliente_productos.producto = LN_productos.producto
    inner join LN_familias on LN_cliente_productos.cliente = LN_familias.cliente
    inner join LN_familia_productos on (LN_familias.familia = LN_familia_productos.familia
             and LN_cliente_productos.producto = LN_familia_productos.producto)
    inner join LN_gestiones on (LN_contraparte_indicadores.contraparte = LN_gestiones.contraparte
           and LN_contraparte_indicadores.gestion = LN_gestiones.gestion)
    left join ln_demo on ln_gestiones.gestion=ln_demo.gestion 
    where LN_gestiones.cliente = @p_cliente
    and LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_cliente_productos.activo = 1
    and LN_familia_productos.activo = 1
    and LN_productos.documentos = 1
    and LN_familias.uso_familia = 'I'
    and LN_contrapartes.activo = 1
    and ln_contrapartes.servicio_contratado = 10
    --and LN_indicadores.clave_indicador not in ('VALORACION_IV','ACADEMICO','PROFESIONAL','COMERCIAL_LEGAL','COMPLIANCE','PRENSAIV','IV_CV')

    ORDER BY LN_indicadores.orden
 end



 /* Productos basados en contratos en Blue */
 if (@accion = 'productos') begin

  select productos.producto, productos.nombre_producto, productos.sigla, 
    contratos_items.item_contrato, isnull(contratos_items.cantidad_producto,0) as cantidad_producto,
    isnull(cant.total_consumo,0) as total_consumo
  from productos inner join contratos_items on productos.producto = contratos_items.producto
  inner join contratos_grupos on contratos_items.id_contrato = contratos_grupos.id_contrato
  inner join grupos_clientes on contratos_grupos.grupo = grupos_clientes.grupo
  left join
     (select ci.item_contrato, sum(isnull(cc.cantidad_consumida,0)) as total_consumo
     from contratos_items ci inner join contratos_grupos cg on ci.id_contrato = cg.id_contrato
     inner join grupos_clientes gc on cg.grupo = gc.grupo
     inner join consumo_contratos cc on ci.id_contrato = cc.item_contrato
     where gc.cliente = @p_cliente
     group by ci.item_contrato) cant on contratos_items.item_contrato = cant.item_contrato
  where grupos_clientes.cliente = @p_cliente
  and contratos_grupos.fecha_vigencia <= GETDATE()
  and contratos_grupos.fecha_vencimiento >= GETDATE()
  and contratos_grupos.estado_contrato = 'A'
  and isnull(contratos_items.cantidad_producto,0) > 0
  and isnull(contratos_items.cantidad_producto,0) > isnull(cant.total_consumo,0)
  
  group by productos.producto, productos.nombre_producto, productos.sigla, 
     contratos_items.item_contrato, contratos_items.cantidad_producto,
     cant.total_consumo

 end


 /* dado el ID producto y user_id obtiene tipo de informe basado es suscripcion en backend */
 if (@accion = 'productos_backend') begin


  select contratos_grupos.id_contrato, contratos_grupos.nombre_contrato,
  contratos_grupos.fecha_vigencia, contratos_grupos.fecha_vencimiento,
  contratos_items.item_contrato, contratos_items.producto, contratos_items.cantidad_producto,
  consumos.consumo, contratos_items.precio_unitario, contratos_items.moneda, 
  productos.nombre_producto, productos.confidencial, productos.sigla,
  tipos_informe.id_tipo_informe, tipos_informe.nombre_tipo_informe
  from contratos_grupos 
  inner join grupos_clientes on contratos_grupos.grupo = grupos_clientes.grupo
  inner join clientes_usuarios on grupos_clientes.cliente = clientes_usuarios.cliente
  inner join contratos_items on contratos_grupos.id_contrato = contratos_items.id_contrato
  inner join productos on contratos_items.producto = productos.producto

  inner join tipos_informe on productos.grupo_informes = tipos_informe.grupo_informes

  left outer join
   (select contratos_items.id_contrato, consumo_contratos.item_contrato,
      ISNULL(SUM(consumo_contratos.cantidad_consumida),0) AS consumo
   from consumo_contratos
   inner join informes on consumo_contratos.informe = informes.informe
   inner join contratos_items on consumo_contratos.item_contrato = contratos_items.item_contrato
   where informes.estado_informe NOT IN ('X', 'C', 'N') 
   group by contratos_items.id_contrato, consumo_contratos.item_contrato) consumos
  on (contratos_grupos.id_contrato = consumos.id_contrato AND contratos_items.item_contrato = consumos.item_contrato)
  where clientes_usuarios.user_id = @p_user_id
  and productos.estado_producto = 'A'
  and productos.inquiro_vitae = 1
  and contratos_grupos.fecha_vigencia <= GETDATE()
  and contratos_grupos.fecha_vencimiento >= GETDATE()
  and contratos_grupos.estado_contrato = 'A'
  and (ISNULL(contratos_items.cantidad_producto, 0) - ISNULL(consumos.consumo, 0)) > 0

  and (
   contratos_items.cliente is null OR contratos_items.cliente in (select clientes_usuarios.cliente
                   from clientes 
                   inner join clientes_usuarios on clientes.cliente = clientes_usuarios.cliente
                   inner join grupos_clientes on clientes.cliente = grupos_clientes.cliente
                   inner join grupos on grupos_clientes.grupo = grupos.grupo
                   where clientes_usuarios.user_id = @p_user_id
                   and clientes.estado_cliente = 'A'
                   and clientes.acceso_iv = 1
                   and (clientes_usuarios.iv_ingresar_busqueda = 1 OR clientes_usuarios.iv_consultar_busqueda = 1))
   )

  group by contratos_grupos.id_contrato, contratos_grupos.nombre_contrato,
     contratos_grupos.fecha_vigencia, contratos_grupos.fecha_vencimiento,
     contratos_items.item_contrato, contratos_items.producto, contratos_items.cantidad_producto,
     consumos.consumo, contratos_items.precio_unitario, contratos_items.moneda, 
     productos.nombre_producto, productos.confidencial, productos.sigla,
     tipos_informe.id_tipo_informe, tipos_informe.nombre_tipo_informe
  order by contratos_grupos.nombre_contrato, productos.nombre_producto


  /*select tipos_informe.id_tipo_informe, tipos_informe.nombre_tipo_informe, productos.sigla, productos.nombre_producto
   from productos 
   inner join tipos_informe on productos.grupo_informes = tipos_informe.grupo_informes
   inner join clientes_productos on productos.producto = clientes_productos.id_producto
   inner join usuarios_productos on (productos.producto = usuarios_productos.id_producto
   and clientes_productos.cliente = usuarios_productos.cliente)
   where tipos_informe.vigente = 1
   and productos.producto = @p_id_producto
   and usuarios_productos.user_id = @p_user_id
   group by tipos_informe.id_tipo_informe, tipos_informe.nombre_tipo_informe, productos.sigla, productos.nombre_producto
   order by tipos_informe.nombre_tipo_informe
  
  
   select tipos_informe.id_tipo_informe, tipos_informe.nombre_tipo_informe, productos.sigla, productos.nombre_producto
   from clientes_usuarios
   inner join clientes on clientes_usuarios.cliente = clientes.cliente
   inner join grupos_clientes on clientes.cliente = grupos_clientes.cliente
   inner join contratos_grupos on grupos_clientes.grupo = contratos_grupos.grupo
   inner join contratos_items on (contratos_grupos.id_contrato = contratos_items.id_contrato
   and clientes.cliente = contratos_items.cliente)
   inner join productos on contratos_items.producto = productos.producto
   inner join tipos_informe on productos.grupo_informes = tipos_informe.grupo_informes
   where clientes_usuarios.user_id = @p_user_id
   and productos.grupo_informes LIKE 'INQUIRO%'
   and contratos_items.cantidad_producto > 0
   and contratos_grupos.estado_contrato = 'A'
   and contratos_grupos.fecha_vigencia <= GETDATE()
   and contratos_grupos.fecha_vencimiento >= GETDATE()
   group by tipos_informe.id_tipo_informe, tipos_informe.nombre_tipo_informe, productos.sigla, productos.nombre_producto
   order by tipos_informe.nombre_tipo_informe*/

 end


 /** dado un id_tipo_informe,  trae los datos del producto. Utilizado para saber si es un confidencial o no una vez seleccionado el tipo en el primer paso de una nueva busqueda **/
 if (@accion = 'datos_producto') begin

  if (@p_id_tipo_informe is not null)

   select productos.producto, productos.nombre_producto, productos.sigla, productos.grupo_informes, ISNULL(productos.confidencial,0) AS confidencial
   from productos 
   inner join tipos_informe on productos.grupo_informes = tipos_informe.grupo_informes
   where tipos_informe.id_tipo_informe = @p_id_tipo_informe


 end



 /* dado el usuario y producto de IV, verificar que tenga cupo de acuerdo a la contratación de productos en backend
  Devuelve una consulta de un registro con un campo 'consumo' = 1 si puede consumir el producto o 0 en caso contrario
   */

 if (@accion = 'control_consumo') begin
  if (@p_user_id > 0 and @p_id_producto > 0) begin

   declare @limite_cliente int = null
   declare @limite_usuario int = null
   declare @bloquear_cliente bit = 0
   declare @bloquear_usuario bit = 0
   declare @fdesde date = null
   declare @fhasta date = null
   declare @cliente int = null


   select @limite_cliente = clientes_productos.limite_consumo,
     @limite_usuario = usuarios_productos.limite_consumo,
     @bloquear_cliente = clientes_productos.bloquear_consumo,
     @bloquear_usuario = usuarios_productos.bloquear_consumo,
     @fdesde = isnull(clientes_productos.fecha_desde, '1900-01-01'),
     @fhasta = isnull(clientes_productos.fecha_hasta, '2099-12-31'),
     @cliente = clientes_productos.cliente

   from clientes_productos inner join usuarios_productos on
    (clientes_productos.id_producto = usuarios_productos.id_producto
    and clientes_productos.cliente = usuarios_productos.cliente)
   where isnull(clientes_productos.fecha_desde, '1900-01-01') <= GETDATE()
   and isnull(clientes_productos.fecha_hasta, '2099-12-31') >= GETDATE()
   and usuarios_productos.id_producto = @p_id_producto
   and usuarios_productos.user_id = @p_user_id

   declare @consumos_cliente int = null
   declare @consumos_usuario int = null

   select @consumos_cliente = count(*)
   from usuarios_consumos 
   where usuarios_consumos.id_producto = @p_id_producto
   and usuarios_consumos.created between @fdesde AND @fhasta
   and usuarios_consumos.user_id IN 
    (select user_id 
    from usuarios_productos   
    where id_producto = @p_id_producto
    and cliente = @cliente
    group by user_id)

   select @consumos_usuario = count(*)
   from usuarios_consumos
   where usuarios_consumos.id_producto = @p_id_producto
   and usuarios_consumos.created between @fdesde AND @fhasta
   and usuarios_consumos.user_id = @p_user_id

   declare @bloq_usuario bit = 0
   declare @bloq_cliente bit = 0


   if (@limite_usuario = 0)
    set @bloq_usuario = 0
   else
    set @bloq_usuario = iif(@limite_usuario is not null, iif(@limite_usuario < @consumos_usuario, 0, iif(@bloquear_usuario = 0, 0, 1)), 1)

   if (@limite_cliente = 0)
    set @bloq_cliente = 0
   else
    set @bloq_cliente = iif(@limite_cliente is not null, iif(@limite_cliente < @consumos_cliente, 0, iif(@bloquear_cliente = 0, 0, 1)), 1)
   

   select iif( @bloq_cliente = 0 AND @bloq_usuario = 0, 1, 0 ) as consumo, @limite_cliente as limite_cliente,
      @limite_usuario as limite_usuario, @consumos_cliente as consumo_cliente, @consumos_usuario as consumos_usuario
    
  end
 end

 /** trae las referncias de búsquedas iv ingresadas por el cliente **/
 if (@accion = 'referencias') begin
  
  if (@p_cliente > 0)

   select distinct ref, ref_nombre from IV_busquedas
   where cliente = @p_cliente
   order by ref_nombre  

 end



 /** Ingresar la búsqueda de un nuevo candidato, crear la contraparte, la asociación con familias, el legajo y el informe en producción **/
 if (@accion = 'ingresar_busqueda') begin

  declare @iv_busqueda    int = null
  declare @confidencial    tinyint = 0
  declare @nombre_candidato   varchar(100) = null
  declare @identificador    varchar(30) = null
  declare @numero      varchar(100) = null
  declare @sexo      varchar(1) = null
  declare @country     varchar(2) = null

  declare @contrap     int = null

  declare @informe     int
  declare @nombre_asignado   varchar(100) = null

  declare @grupo      smallint = NULL
  declare @id_contrato    int = null
  declare @item_contrato    int = NULL
  declare @nombre_cliente    varchar(200) = NULL
  declare @grupo_informes    varchar(50) = NULL
  declare @nombre_tipo_informe  varchar(100) = NULL

  DECLARE @fecha_limite       datetime = dateadd(second,-1,dateadd(day,1,@p_fecha_limite_doc))
  DECLARE @fecha_comprometida datetime = dateadd(second,-1,dateadd(day,1,@p_fecha_comprometida))

  declare @prioridad tinyint = 5

  if (@p_id_padron is not null and @p_id_tipo_informe is not null) begin

   if (@p_id_producto is not null) begin
    select @confidencial = isnull(confidencial,0) from productos where producto = @p_id_producto
   end

   /** datos del padrón **/
   select @nombre_candidato = left(padrones.nombre_persona,100),
     @identificador = padrones.identificador,
     @numero = padrones.numero,
     @sexo = padrones.sexo,
     @country = identificadores.country_code
   from padrones inner join identificadores on padrones.identificador = identificadores.identificador
   where padrones.id_padron = @p_id_padron

   --select @nombre_candidato as aa,@identificador as dd,@numero as df,@country as fd,@p_id_padron as sdaffd

   /** partición del string referencia **/
   if (@p_ref is null or @p_ref_nombre is null) begin
    if (@p_referencia is not null) begin
     if (CHARINDEX('|',@p_referencia,1) > 1) begin
      set @p_ref = SUBSTRING(@p_referencia, 1, CHARINDEX('|',@p_referencia,1)-1)
      set @p_ref_nombre = SUBSTRING(@p_referencia, CHARINDEX('|',@p_referencia,1)+1,len(@p_referencia))
     end
    end
   end


   /** Determinación de datos del cliente y contrato **/
   select @grupo = grupo,
     @nombre_cliente = clientes.nombre_cliente
   from grupos_clientes inner join clientes on grupos_clientes.cliente = clientes.cliente
   where grupos_clientes.cliente = @p_cliente


   select @id_contrato = contratos_items.id_contrato,
     @item_contrato = contratos_items.item_contrato,
     @grupo_informes = tipos_informe.grupo_informes,
     @nombre_tipo_informe = tipos_informe.nombre_tipo_informe
   from contratos_items
   inner join contratos_grupos on contratos_items.id_contrato = contratos_grupos.id_contrato
   inner join productos on contratos_items.producto = productos.producto
   inner join tipos_informe on productos.grupo_informes = tipos_informe.grupo_informes
   left outer join
    (select contratos_items.id_contrato, consumo_contratos.item_contrato,
       ISNULL(SUM(consumo_contratos.cantidad_consumida),0) AS consumo
    from consumo_contratos
    inner join informes on consumo_contratos.informe = informes.informe
    inner join contratos_items on consumo_contratos.item_contrato = contratos_items.item_contrato
    where informes.estado_informe NOT IN ('X', 'C', 'N') 
    group by contratos_items.id_contrato, consumo_contratos.item_contrato) consumos
   on (contratos_grupos.id_contrato = consumos.id_contrato AND contratos_items.item_contrato = consumos.item_contrato)
   where contratos_grupos.grupo = @grupo
   and tipos_informe.id_tipo_informe = @p_id_tipo_informe
   and contratos_grupos.fecha_vigencia <= GETDATE()
   and contratos_grupos.fecha_vencimiento >= GETDATE()
   and productos.estado_producto = 'A'
   and productos.inquiro_vitae = 1


   /* Ingresar el informe */
   EXEC LN_informes 'obtener_id', NULL, @informe OUTPUT

   if (@informe IS NOT NULL) begin

    DECLARE @hist varchar(max) = @preffix + 'Creación del informe.'

    if (@p_estado_inicial = 'A' or @p_estado_inicial = 'T' or @p_estado_inicial = 'P') begin     

     select @nombre_asignado = isnull(first_name,'') + ' ' + isnull(last_name, '')
     from users where user_id = @p_asignado

     SET @hist = @hist + CHAR(13) + CHAR(10) + @preffix + 'Asignación del informe a ' + @nombre_asignado
    end

    if (@p_estado_inicial = 'T' or @p_estado_inicial = 'P') begin
     SET @hist = @hist + CHAR(13) + CHAR(10) + @preffix + 'Aceptación del informe.'
    end

    if (@p_estado_inicial = 'P') begin
     SET @hist = @hist + CHAR(13) + CHAR(10) + @preffix + 'Informe puesto en gestión.'
    end

    select @prioridad = isnull(prioridad_default,5) from tipos_informe where id_tipo_informe = @p_id_tipo_informe

    INSERT INTO informes
     (informe, cliente, identificador, numero, fecha_ingreso, id_tipo_informe,
     objeto_informe, historial_informe, ingresado_por, idioma, filial, prioridad, datamanager,
     asignado, estado_informe, fecha_asignado, fecha_aceptacion,
     excepcion, excepcion_prod, notificar_cliente, precio, moneda,
     nombre_contacto_1, email_contacto_1, telefono_contacto_1,fecha_limite_doc,fecha_comprometida)
    VALUES
     (@informe, @p_cliente, @identificador, @numero, @ahora, @p_id_tipo_informe,
     'INFORME GENERADO A TRAVES DE WEB INQUIRO VITAE - NUEVA BUSQUEDA', @hist,
     @p_user_id, @p_idioma, @p_filial, @prioridad, @p_datamanager,
     iif(@p_estado_inicial = 'A' OR @p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @p_asignado, NULL),
     @p_estado_inicial, 
     iif(@p_estado_inicial = 'A' OR @p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @ahora, NULL),
     iif(@p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @ahora, NULL), 0, 0, 0, NULL, NULL,
     @nombre_candidato, @p_email_candidato, @p_telefono_candidato, @fecha_limite, @fecha_comprometida)


    /* Generación de las subtareas */
    DECLARE @tarea INT = NULL
    DECLARE @tipo_subinforme INT = null
    DECLARE @orden tinyint = NULL
    DECLARE @prioridad_tarea tinyint = NULL
    DECLARE @id_tarea_gestion int = NULL
    DECLARE @activa_gestion tinyint = NULL
    DECLARE @usuario_gestion varchar(100) = NULL

    DECLARE TiposSubinformes CURSOR FOR

    select tipos_subinformes.id_tipo_subinforme, tipos_subinformes.orden, isnull(tipos_informe.prioridad_default,0) as prioridad_default, tipos_informe.genera_solicitud_doc
    from tipos_subinformes inner join tipos_informe on tipos_subinformes.id_tipo_subinforme = tipos_informe.id_tipo_informe
    where tipos_subinformes.id_tipo_informe = @p_id_tipo_informe
    order by tipos_subinformes.orden

    OPEN TiposSubinformes
    FETCH NEXT FROM TiposSubinformes INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion
    WHILE @@FETCH_STATUS = 0 BEGIN

     EXEC LN_informes 'obtener_id', NULL, @tarea OUTPUT

     if (@tarea > 0) begin

      /* La prioridad de cada tarea depende del tipo de informe. Si está en 0 o nula toma la del informe padre */

      INSERT INTO informes
       (informe, cliente, identificador, numero, fecha_ingreso, id_tipo_informe,
       objeto_informe, historial_informe, ingresado_por, idioma, 
       filial, prioridad, datamanager, estado_informe, orden, informe_padre)      
      VALUES
       (@tarea, @p_cliente, @identificador, @numero, @ahora, @tipo_subinforme,
       NULL, @preffix + 'Creación del informe.', @p_user_id, @p_idioma,
       @p_filial, iif(@prioridad_tarea > 0, @prioridad_tarea, @prioridad), @p_datamanager, 'I', @orden, @informe)

      /* identifico la tarea de gestión de documentación dentro del informe */
      IF (@activa_gestion = 1) BEGIN
       SET @id_tarea_gestion = @tarea
      END

     end

     FETCH NEXT FROM TiposSubinformes INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion
    END
    CLOSE TiposSubinformes
    DEALLOCATE TiposSubinformes

    /** Tarea de gestión de documentación */
    if (@id_tarea_gestion > 0 AND @p_asignado_gestion > 0 AND @p_estado_inicial = 'P') begin
     select @usuario_gestion = first_name + ' ' + last_name from users where user_id = @p_asignado_gestion

     UPDATE informes SET
      asignado = @usuario_gestion,
      fecha_asignado = @ahora,
      historial_informe = convert(varchar(max), historial_informe) + CHAR(13) + CHAR(10) + @preffix + 'Asignación de la tarea a ' + @usuario_gestion
           + CHAR(13) + CHAR(10) + @preffix + 'Aceptación de tarea' + CHAR(13) + CHAR(10) + @preffix + 'Tarea en ejecución',
      fecha_aceptacion = @ahora,
      fecha_iniciado = @ahora,
      estado_informe = 'E'
     WHERE informe = @id_tarea_gestion

    end

    
    /* Consumo del contrato */
    if (@item_contrato > 0) begin

     declare @consumo int

     insert into consumo_contratos
      (item_contrato, cliente, producto, informe, cantidad_consumida, user_id, consumido)
     values
      (@item_contrato, @p_cliente, @p_id_producto, @informe, 1, @p_user_id, @ahora)


     set @consumo = SCOPE_IDENTITY()

     declare @informes_log varchar(max) = 'Nuevo informe ingresado para ' + @nombre_cliente + '<br>'
     set @informes_log = @informes_log + 'Tipo de informe: ' + @nombre_cliente + '<br>'
     set @informes_log = @informes_log + 'Grupo de informes: ' + @grupo_informes + '<br>'
     set @informes_log = @informes_log + 'Cantidad consumida: 1' + '<br>'
     set @informes_log = @informes_log + 'Sujeto informado: ' + @nombre_candidato + '<br>'
     set @informes_log = @informes_log + 'Identificación: ' + @identificador + ' ' + @numero
      
     
     insert into contratos_log
      (fecha_log, user_id, id_contrato, item_contrato, consumo, detalle_log)
     values
      (@ahora, @p_user_id, @id_contrato, @item_contrato, @consumo, @informes_log)

    end
    /* fin de consumo contrato */


    /** INGRESO DE LA BUSQUEDA **/
    insert into IV_busquedas
     (cliente, informe, item_contrato, consumo, ref, ref_nombre, ref_seccion, ref_cargo, ref_confidencial, 
     creado, creado_por, id_padron, verif_padron, identificador, numero, nombre_candidato, email_candidato, 
     telefono_candidato, notas_candidato, sexo_candidato)
    values
     (@p_cliente, @informe, iif(@item_contrato > 0, @item_contrato, 0), iif(@consumo > 0, @consumo, 0),
     @p_ref, @p_ref_nombre, @p_ref_seccion, @p_ref_cargo, @confidencial, @ahora, @p_user_id, @p_id_padron, 1,
     @identificador, @numero, @nombre_candidato, @p_email_candidato, @p_telefono_candidato, @p_notas_candidato, @sexo)

    set @iv_busqueda = SCOPE_IDENTITY()

    if (@iv_busqueda is not null) begin

     insert into IV_busquedas_historico
      (iv_busqueda, iv_horario, user_id, detalle)
     values
      (@iv_busqueda, @ahora, @p_user_id, @preffix + 'Ingreso de nueva búsqueda.')


     /* Ingresar la contraparte y gestión */
     insert into LN_contrapartes (cliente, id_padron, country_code, datos_comerciales, activo, nombre_contacto,
            email_contacto, telefono_contacto, created, created_by, modified, modified_by,
            vigencia_desde, vigencia_hasta, subcontratista, codigo_idioma,
            clave, clave_temporal, clave_expira, servicio_contratado)
 
     values (@p_cliente, @p_id_padron, @country, @p_notas_candidato, 1, @nombre_candidato, @p_email_candidato, 
       @p_telefono_candidato, @ahora, @p_user_id, NULL, NULL, convert(date, @ahora), '2099-12-31', 0, 'spa', 'IV123456', 0, '2099-12-31',10)

     set @contrap = SCOPE_IDENTITY()

     INSERT INTO LN_gestiones (contraparte, cliente, desde, hasta, vigente, created, created_by, modified, modified_by, iv_busqueda)
     values (@contrap, @p_cliente, convert(date, @ahora), convert(date,'2099-12-31'), 1, @ahora, @p_user_id, NULL, NULL, @iv_busqueda)

     set @gestion = SCOPE_IDENTITY()

     insert into LN_contraparte_familias (contraparte, familia, activo, created, created_by)
     values (@contrap, @p_familia, 1, @ahora, @p_user_id)

     /* Actualizar el legajo */
     EXEC LN_actualizar_legajo @contrap, @gestion, @p_user_id
     /* Mandarlo al queue */
     if not exists (select * from LN_contrapartes_queue where contraparte = @contrap and processed = 0)
      insert into ln_contrapartes_queue (contraparte,id_taken) values (@contrap, 1.0 + floor(10 * RAND(convert(varbinary, newid()))))

     /**  tabla de Dossier para LED : obtener el producto LED asociado al producto comercial **/
     if (@p_id_producto is not null) begin

      select @producto_led = producto from LN_productos where producto_consumos = @p_id_producto

      if (@producto_led is not null)

       insert into LN_demo (contraparte, gestion, producto, informe, visible)
        values (@contrap, @gestion, @producto_led, @informe, 0)

     end


     -- Asentar consumo de usuario 
     insert into usuarios_consumos
      (user_id, cuit_consultado, id_producto, created)
     values
      (@p_user_id, @numero, @p_id_producto, @ahora)


     -- Devolución de los datos generados
     SELECT @iv_busqueda AS iv_busqueda, @informe AS informe, @contrap AS contraparte, @gestion AS gestion

    end
    /** fin busqueda **/

   end
   /** fin de informe **/

  end
 end


 /* a partir de un DOC_ID (tabla LN_docs) obtener el id de la búsqueda */

 if (@accion = 'busqueda_doc_id') begin
 
  if (@p_doc_id is not null) 

   select LN_docs.doc_id , LN_contrapartes.cliente, LN_contrapartes.contraparte, LN_docs.gestion , IV_busquedas.iv_busqueda
   from LN_docs
   inner join LN_contrapartes on LN_docs.contraparte = LN_contrapartes.contraparte
   inner join IV_busquedas on (LN_contrapartes.id_padron =  IV_busquedas.id_padron
          and LN_contrapartes.cliente = IV_busquedas.cliente)
   where LN_docs.doc_id = @p_doc_id

 end


 /* actualizar manual indicadores del producto inquiro > académicos, profesionales, comercial / judicial, compliance, prensa */

 if (@accion = 'actualizar_indicador') begin

  if (@p_contraparte is not null and @p_clave_indicador is not null and @p_valor_texto is not null) begin

   select @indicador = LN_indicadores.indicador 
      from LN_indicadores 
      inner join LN_productos on LN_indicadores.producto = LN_productos.producto
      inner join LN_cliente_productos on LN_indicadores.producto = LN_cliente_productos.producto
      inner join LN_contrapartes on LN_cliente_productos.cliente = LN_contrapartes.cliente
      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)
      inner join LN_cliente_indicadores on (LN_contrapartes.cliente = LN_cliente_indicadores.cliente
               AND LN_indicadores.indicador = LN_cliente_indicadores.indicador)
      where LN_productos.producto = @producto_led
      and LN_indicadores.clave_indicador = @p_clave_indicador
      and LN_contrapartes.contraparte = @p_contraparte
      and LN_productos.visible = 1
      and LN_cliente_productos.activo = 1
      and LN_familia_productos.activo = 1
      and LN_contraparte_familias.activo = 1
      and LN_contrapartes.activo = 1

   if (@indicador is not null) begin

    select @gestion = LN_gestiones.gestion
    from LN_gestiones inner join LN_contrapartes 
      on (LN_gestiones.contraparte = LN_contrapartes.contraparte
       and LN_gestiones.cliente = LN_contrapartes.cliente)
    where LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_gestiones.contraparte = @p_contraparte

    if (@gestion is not null) begin

     /* pasando S borra el indicador */
     /* AGB CAMBIA S POR Z Y COMENTA EL DELETE 08/01/2021*/
     if (@p_valor_texto != 'Z')

      if not exists (select contraparte, indicador, valor_texto
          from LN_contraparte_indicadores
          where contraparte = @p_contraparte
          and gestion = @gestion
          and indicador = @indicador)

       insert into LN_contraparte_indicadores
       (contraparte, gestion, indicador, valor_texto, visible, created, created_by)
       values
       (@p_contraparte, @gestion, @indicador, @p_valor_texto, 1, GETDATE(), @p_user_id)

      else

       update LN_contraparte_indicadores set 
          valor_texto = @p_valor_texto,
          modified = GETDATE(),
          modified_by = @p_user_id

       where contraparte = @p_contraparte
       and gestion = @gestion
       and indicador = @indicador

     else

      delete LN_contraparte_indicadores
      where contraparte = @p_contraparte
      and gestion = @gestion
      and indicador = @indicador
       AND 1=9

    end

   end

  end

 end

 if (@accion = 'obtener_indicadores') begin

  select LN_indicadores.orden,
    LN_indicadores.indicador, LN_indicadores.clave_indicador, 
    LN_indicadores.nombre_indicador, LN_indicadores.etiqueta,
    LN_contraparte_indicadores.valor_numerico,
    LN_contraparte_indicadores.valor_fecha,
    LN_contraparte_indicadores.valor_texto,
    LN_contraparte_indicadores.contraparte,
    LN_contraparte_indicadores.gestion,
    case
     /*when LN_indicadores.clave_indicador = 'VALORACION_IV' then 'fa fas fa-file-text'*/
     when LN_indicadores.clave_indicador = 'ACADEMICO' then 'fa fas fa-graduation-cap'
     when LN_indicadores.clave_indicador = 'PROFESIONAL' then 'fa fas fa-user-tie fa-briefcase'
     when LN_indicadores.clave_indicador = 'COMERCIAL_LEGAL' then 'fa fas fa-gavel'
     when LN_indicadores.clave_indicador = 'COMPLIANCE' then 'fa fas fa-address-card fa-id-card'
     when LN_indicadores.clave_indicador = 'COMPLIANCE2' then 'fa fa-camera-retro'
     when LN_indicadores.clave_indicador = 'PRENSAIV' then 'fas fa fa-newspaper-o fa-newspaper'
     else LN_productos.class_icon
    end as class_icon,
    iif (LN_contraparte_indicadores.valor_texto = 'P', '#89ab5f',--'green',/* 'text-success',*/
     iif(LN_contraparte_indicadores.valor_texto = 'O', '#fab724',--'#EBE250',/* 'text-warning',*/
      iif(LN_contraparte_indicadores.valor_texto = 'N', '#a72b2a',-- '#F03F2E', /* 'text-danger', */
      iif(LN_contraparte_indicadores.valor_texto = 'S', '#777',--'#41a9f4',--'#D5D9DE', 
      iif(LN_contraparte_indicadores.valor_texto = 'X', '#d3d3d3', NULL))))) as icon_color, --'#333333' -- '#777'
    ln_indicadores.valoracion

  from LN_contrapartes 
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join LN_cliente_indicadores on LN_contrapartes.cliente = LN_cliente_indicadores.cliente
  inner join LN_indicadores on LN_cliente_indicadores.indicador = LN_indicadores.indicador
  inner join LN_productos on LN_indicadores.producto = LN_productos.producto
  left outer join LN_contraparte_indicadores on 
       (LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
        AND LN_indicadores.indicador = LN_contraparte_indicadores.indicador
        AND LN_gestiones.gestion = LN_contraparte_indicadores.gestion)
  where LN_contrapartes.contraparte = @p_contraparte
  and LN_productos.producto = @producto_led

  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()

  order by LN_indicadores.orden

 end

 



 if (@accion = 'panel_control_excel' and @p_cliente is not null) begin


  select convert(varchar, LN_contrapartes.cliente) + '/' + 
    convert(varchar, LN_contrapartes.contraparte) + '/' + 
    convert(varchar, LN_gestiones.gestion) + '/' + RIGHT('00000000'+convert(varchar,LN_gestiones.iv_busqueda),8)+'_foto_sq.jpg' AS foto,
    convert(varchar, LN_contrapartes.cliente) + '/' + 
    convert(varchar, LN_contrapartes.contraparte) + '/' + 
    convert(varchar, LN_gestiones.gestion) + '/' + RIGHT('00000000'+convert(varchar,LN_gestiones.iv_busqueda),8)+'_foto_60.jpg' AS foto_60,
    convert(varchar, LN_contrapartes.cliente) + '/' + 
    convert(varchar, LN_contrapartes.contraparte) + '/' + 
    convert(varchar, LN_gestiones.gestion) + '/' + RIGHT('00000000'+convert(varchar,LN_gestiones.iv_busqueda),8)+'_foto_120.jpg' AS foto_120,
    LN_gestiones.iv_busqueda, LN_contrapartes.contraparte, 
    LN_gestiones.gestion, identificadores.country_code, country.country_name, 
    LN_familias.nombre_familia as familia, convert(varchar,LN_contrapartes.created,103) as created,
    convert(varchar(10),IV_busquedas.creado,121) as creado, padrones.nombre_persona, 
    identificadores.abreviatura, padrones.identificacion, IV_busquedas.ref, IV_busquedas.ref_nombre,
    ISNULL(IV_busquedas.ref, '') + ' - ' + ISNULL(IV_busquedas.ref_nombre, '') AS referencia,
    IV_busquedas.ref_cargo, IV_busquedas.ref_seccion, 
    IV_busquedas.informe, productos.sigla, users.first_name, users.last_name, IV_busquedas.cv_existe,
    IV_busquedas.email_candidato, IV_busquedas.telefono_candidato, 
    users.first_name + ' ' + users.last_name as ingresado_por, 
    /*'<i class="fas fa-file-alt fa-2x"></i>' as cv,*/

    iif(IV_busquedas.cv_existe > 0, '<i class="fa fa-check text-success fa-2x"></i>', '<i class="fa fa-times text-danger fa-2x"></i>') as cv,

    users.email
  from LN_contrapartes
  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
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join IV_busquedas on LN_gestiones.iv_busqueda = IV_busquedas.iv_busqueda
  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
  inner join informes on IV_busquedas.informe = informes.informe
  inner join tipos_informe on informes.id_tipo_informe = tipos_informe.id_tipo_informe
  inner join users on informes.ingresado_por = users.user_id
  left outer join contratos_items on IV_busquedas.item_contrato = contratos_items.item_contrato
  left outer join productos on contratos_items.producto = productos.producto

  where LN_contrapartes.cliente = @p_cliente
  and LN_familias.uso_familia = 'I'
  and LN_contrapartes.activo = 1
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  order by country.country_name, padrones.nombre_persona


 end



END
GO