dbo.IV_panel_control

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/* sp IV_panel_control

exec IV_panel_control 'productos_backend',NULL,98092071,10
exec IV_panel_control 'indicadores_panel_controlr',3920694,15342812,10
exec IV_panel_control 'panel_control',2254875
exec IV_panel_control 'panel_control_uid',738156811
select * from users where username = 'naranja'

*/
-- update informes set ingresado_por=475782038 where informe in (100689192,100689177,100688945,100688558,100687281)

--select * from clientes where nombre_cliente like '%hire%'

-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.IV_panel_control
 -- 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(512) = null,
 @p_ref_seccion      varchar(100) = null,
 @p_ref_cargo      varchar(100) = null,
 @p_email_candidato     varchar(512) = null,
 @p_telefono_candidato    varchar(512) = 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,
    @p_team_id                          int = null,
 @p_fecha_inicio      datetime = null,
    @p_id_referencia                    varchar(512) = 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, 
     70 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, 
     50 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, 
     60 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'
      when LN_indicadores.clave_indicador = 'PRENSAIV' then 'fas fa fa-newspaper-o fa-newspaper'
      else LN_productos.class_icon
     end as class_icon,

     --115 as width, 
     80 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_uid' and @p_cliente is not null) 
    begin

    if (@p_user_id is 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 creado2,
    convert(varchar,INF.fecha_hora_pub,103) as creado,
    IIF (INF.gestion is null,1,isnull(INF.visible,0)) as publicado,
    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, users.user_id as usuario_alta,
    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,ln_contrapartes.id_referencia,INF.fecha_hora_pub as fecha_finalizado,ln_contrapartes.hit_summary as informe_hit,
                LN_tipo_informes.nombre_tipo_informe,informes.estado_informe
                ,INF.TAT_prod,INF.TAT_front,LN_contrapartes.created as fecha_alta,INF.fecha_hora_pub as fecha_cierre,
    LN_familias.country_code as country_code_family,country_familia.country_name as country_name_family

  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
        inner join LN_tipo_informes on informes.id_tipo_informe = LN_tipo_informes.idtipoinforme        
  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
  left join (select gestion, isnull(LN_demo.visible,0) as visible, informes.estado_informe, LN_demo.fecha_hora_pub,ln_demo.TAT_prod,ln_demo.TAT_front 
                from ln_demo
         inner join informes on informes.informe = LN_demo.informe) INF on INF.gestion = LN_gestiones.gestion 
  left join country country_familia ON country_familia.country_code=LN_familias.country_code
  where LN_contrapartes.cliente = @p_cliente
  and isnull(INF.estado_informe,'') not in ('P')
  /*and isnull(INF.estado_informe,'') not in ('N','C')*/
  and LN_contrapartes.servicio_contratado = 10 
  and LN_contrapartes.activo = 1
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_gestiones.vigente = 1 
  and isnull(tipos_informe.visible_publico,1)=1
        /*AND USERS.user_id=@p_user_id */
  /*order by IV_busquedas.creado desc, country.country_name, padrones.nombre_persona*/
  order by ln_contrapartes.created desc, country.country_name, padrones.nombre_persona
    END
    ELSE /*NO SE FILTRA POR USER_ID EL USUARIO GENERAL DEL CLIENTE PUEDE VISUALIZAR TODAS LAS ORDENES */
    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 creado2,
    convert(varchar,INF.fecha_hora_pub,103) as creado,
    IIF (INF.gestion is null,1,isnull(INF.visible,0)) as publicado,
    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, users.user_id as usuario_alta,
    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,ln_contrapartes.id_referencia,INF.fecha_hora_pub as fecha_finalizado,ln_contrapartes.hit_summary as informe_hit,LN_tipo_informes.nombre_tipo_informe,informes.estado_informe
                ,INF.TAT_prod,INF.TAT_front,LN_contrapartes.created as fecha_alta,INF.fecha_hora_pub as fecha_cierre ,
    LN_familias.country_code as country_code_family,country_familia.country_name as country_name_family
  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
        inner join LN_tipo_informes on informes.id_tipo_informe = LN_tipo_informes.idtipoinforme
  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
  left join (select gestion, isnull(LN_demo.visible,0) as visible, informes.estado_informe, LN_demo.fecha_hora_pub,ln_demo.TAT_prod,ln_demo.TAT_front 
                from ln_demo
         inner join informes on informes.informe = LN_demo.informe) INF on INF.gestion = LN_gestiones.gestion 
  left join country country_familia ON country_familia.country_code=LN_familias.country_code
  where LN_contrapartes.cliente = @p_cliente
  /*and isnull(INF.estado_informe,'') not in ('N','C')*/
  and isnull(INF.estado_informe,'') not in ('P')
  and LN_contrapartes.servicio_contratado = 10 
  and LN_contrapartes.activo = 1
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_gestiones.vigente = 1 
  and isnull(tipos_informe.visible_publico,1)=1
  order by ln_contrapartes.created desc, country.country_name, padrones.nombre_persona
  /*order by IV_busquedas.creado desc, country.country_name, padrones.nombre_persona*/
    END
    
 end


 -- select * from clientes where nombre_cliente like 'fidelitas%' 2254875
 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 creado2,
    --convert(varchar,LN_demo.fecha_publicacion,103) as creado,
    convert(varchar,INF.fecha_publicacion,103) as creado,
    --isnull(LN_demo.visible,0) as publicado,
    --isnull(LN_demo.visible,1) as publicado,
    /* 5/4/2023:
     si no esta en ln_demo, lo tomamos como publicado (por inconsistencia de datos viejos, que no estan ahi) */
    --IIF (not exists (select * from LN_demo d where d.contraparte = LN_contrapartes.contraparte and d.gestion = LN_gestiones.gestion),1,isnull(LN_demo.visible,0)) as publicado,
    IIF (INF.gestion is null,1,isnull(INF.visible,0)) as publicado,
                IIF (INF.gestion is null,1,isnull(INF.visible,0)) as visible,
    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
  --inner join ln_demo on (ln_demo.contraparte = LN_gestiones.contraparte and ln_demo.gestion=ln_gestiones.gestion)
  --left join ln_demo on (ln_demo.contraparte = LN_gestiones.contraparte and ln_demo.gestion=ln_gestiones.gestion)
  left join (select gestion, isnull(LN_demo.visible,0) as visible, informes.estado_informe, LN_demo.fecha_publicacion
                from ln_demo
         inner join informes on informes.informe = LN_demo.informe) INF on INF.gestion = LN_gestiones.gestion 
  where LN_contrapartes.cliente = @p_cliente
  and isnull(INF.estado_informe,'') not in ('N','C')
  --and (LN_familias.uso_familia = 'I' or LN_familias.servicio_contratado=10)
  and LN_contrapartes.servicio_contratado = 10 
  and LN_contrapartes.activo = 1
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_gestiones.vigente = 1 
  --and tipos_informe.visible_publico=1
  and isnull(tipos_informe.visible_publico,1)=1
  order by INF.fecha_publicacion desc , padrones.nombre_persona ASC 

/* 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_gestiones.vigente = 1 
    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_gestiones.vigente = 1 
    and LN_cliente_productos.activo = 1
    and LN_familia_productos.activo = 1
    and LN_productos.documentos = 1
    --and (LN_familias.uso_familia = 'I' or LN_familias.servicio_contratado=10)
    and ln_contrapartes.servicio_contratado = 10
    and LN_contrapartes.activo = 1

  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, 

      /*isnull(LN_contraparte_indicadores.valor_texto,'S') as valor_texto, */

      LN_indicadores.orden
      --,isnull(LN_demo.visible,0) as visible, 
      --,isnull(LN_demo.visible,1) as visible, 
      /* 5/4/2023:
       si no esta en ln_demo, lo tomamos como publicado (por inconsistencia de datos viejos, que no estan ahi) */
      --,IIF (not exists (select * from LN_demo d where d.contraparte = LN_contrapartes.contraparte and d.gestion = LN_gestiones.gestion),1,isnull(LN_demo.visible,0)) as visible,
      ,IIF (INF.gestion is null,1,isnull(INF.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 'Aprobado'
     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 
    left join (select gestion, isnull(LN_demo.visible,0) as visible, informes.estado_informe 
                from ln_demo
         inner join informes on informes.informe = LN_demo.informe) INF on INF.gestion = LN_gestiones.gestion
    where LN_gestiones.cliente = @p_cliente
    and isnull(INF.estado_informe,'') not in ('N','C')
    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, 
      --,isnull(LN_demo.visible,1) as visible, 
      /* 5/4/2023:
       si no esta en ln_demo, lo tomamos como publicado (por inconsistencia de datos viejos, que no estan ahi) */
      --,IIF (not exists (select * from LN_demo d where d.contraparte = LN_contrapartes.contraparte and d.gestion = LN_gestiones.gestion),1,isnull(LN_demo.visible,0)) as visible,
      ,IIF (INF.gestion is null,1,isnull(INF.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 
    left join (select gestion, isnull(LN_demo.visible,0) as visible, informes.estado_informe 
                from ln_demo
         inner join informes on informes.informe = LN_demo.informe) INF on INF.gestion = LN_gestiones.gestion
    where LN_gestiones.cliente = @p_cliente
    and isnull(INF.estado_informe,'') not in ('N','C')
    and LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_gestiones.vigente = 1 
    and LN_cliente_productos.activo = 1
    and LN_familia_productos.activo = 1
    and LN_productos.documentos = 1
    --and (LN_familias.uso_familia = 'I' or LN_familias.servicio_contratado=10)
    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



 /* para obtener los valores de indicadores de las contrapartes IV del cliente */
 if (@accion = 'indicadores_panel_controlAGB' 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
      --,isnull(LN_demo.visible,1) as visible
      /* 5/4/2023:
       si no esta en ln_demo, lo tomamos como publicado (por inconsistencia de datos viejos, que no estan ahi) */
      ,IIF (not exists (select * from LN_demo d where d.contraparte = LN_contrapartes.contraparte and d.gestion = LN_gestiones.gestion),1,
      isnull(LN_demo.visible,0)) as visible

    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
      --,isnull(LN_demo.visible,1) as visible
      /* 5/4/2023:
       si no esta en ln_demo, lo tomamos como publicado (por inconsistencia de datos viejos, que no estan ahi) */
      ,IIF (not exists (select * from LN_demo d where d.contraparte = LN_contrapartes.contraparte and d.gestion = LN_gestiones.gestion),1,
      isnull(LN_demo.visible,0)) as visible 

    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' or LN_familias.servicio_contratado=10)
    and LN_contrapartes.servicio_contratado = 10 
    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 */
  inner join tipos_informe on (productos.grupo_informes = tipos_informe.grupo_informes and productos.estado_producto='A' and tipos_informe.vigente=1)

  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)
  inner join usuarios_productos on usuarios_productos.user_id = clientes_usuarios.user_id 
  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 usuarios_productos.id_producto=10 
  and tipos_informe.visible_publico=1

  /*
   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 ltrim(rtrim(ref)) as ref,ltrim(rtrim(ref_nombre)) as ref_nombre from IV_busquedas
   where cliente = @p_cliente and ref is not null and ref_nombre is not null
   order by ref asc,ref_nombre asc
 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_busqueda1') 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
 
  if (@p_fecha_limite_doc is null)
  begin
  set @p_fecha_limite_doc = getdate()+2
  end
  if (@p_fecha_comprometida is null)
  begin
  set @p_fecha_comprometida = getdate()+5
  end
 
  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
  exec @informe = Genera_ID_Informe_2022 @cliente,null
 
  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
 
  set @ahora = getdate()
 
  update informes set informe= @informe, cliente=@p_cliente, identificador=@identificador, numero=@numero, fecha_ingreso=@ahora, id_tipo_informe=@p_id_tipo_informe,
  objeto_informe='INFORME GENERADO A TRAVES DE WEB INQUIRO VITAE - NUEVA BUSQUEDA', historial_informe=@hist, ingresado_por=@p_user_id, idioma=@p_idioma, filial=@p_filial, prioridad=@prioridad, datamanager=@p_datamanager,
  asignado=iif(@p_estado_inicial = 'A' OR @p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @p_asignado, NULL), estado_informe=@p_estado_inicial, 
  fecha_asignado=iif(@p_estado_inicial = 'A' OR @p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @ahora, NULL), fecha_aceptacion=iif(@p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @ahora, NULL),
  excepcion=0, excepcion_prod=0, notificar_cliente=0, precio=null, moneda=null,
  nombre_contacto_1=@nombre_candidato, email_contacto_1=@p_email_candidato, telefono_contacto_1=@p_telefono_candidato,fecha_limite_doc=@fecha_limite,fecha_comprometida=@fecha_comprometida
  where informe = @informe and idreg= @informe
 
  /* 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
  exec @tarea = Genera_ID_Informe_2022 @cliente,@informe
  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 */
 
 
  update informes set informe =@tarea , cliente=@p_cliente, identificador=@identificador, numero=@numero, fecha_ingreso=@ahora, id_tipo_informe=@tipo_subinforme,
  objeto_informe=null, historial_informe=@preffix + 'Creación del informe.', ingresado_por=@p_user_id, idioma=@p_idioma, 
  filial=@p_filial, prioridad=iif(@prioridad_tarea > 0, @prioridad_tarea, @prioridad), datamanager=@p_datamanager, estado_informe='I', orden=@orden, informe_padre=@informe
  where informe = @tarea and idreg= @tarea
 
  /* 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) values (@contrap)
 
  /**  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
  */


  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 = NULL
  DECLARE @fecha_comprometida datetime = NULL
  declare @prioridad tinyint = 5
  DECLARE @hist varchar(max) = ''
  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 @USID INT = NULL
  declare @consumo int
  declare @informes_log varchar(max) = ''
  declare @isinicial bit

 
 /** 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
*/
  if (@p_fecha_limite_doc is null)
  begin
   set @p_fecha_limite_doc = getdate()+2
  end
  if (@p_fecha_comprometida is null)
  begin
   set @p_fecha_comprometida = getdate()+5
  end

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

  SET @prioridad = 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
   exec @informe = Genera_ID_Informe_2022 @cliente,null



   if (@informe IS NOT NULL) begin

    SET @hist = @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

    set @ahora = getdate()

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



    /* 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 @USID INT = 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,0 as USID
    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

/*
select tipos_subinformes.id_tipo_subinforme, tipos_subinformes.orden, isnull(tipos_informe.prioridad_default,0) as prioridad_default, tipos_informe.genera_solicitud_doc,
/*ln_tareas.*,LN_Roles.*,*/LN_Team_Members.user_id as USID
from tipos_subinformes inner join tipos_informe on tipos_subinformes.id_tipo_subinforme = tipos_informe.id_tipo_informe
inner join ln_tareas on ln_tareas.id_tipo_informe_blue = tipos_subinformes.id_tipo_subinforme 
inner join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
inner join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
where tipos_subinformes.id_tipo_informe =   @p_id_tipo_informe 
and LN_Team_Members.id_team=@p_team_id
and LN_Team_Members.activo=1
and LN_Team_Members.isdefault=1

order by tipos_subinformes.orden

*/




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


     -- EXEC LN_informes 'obtener_id', NULL, @tarea OUTPUT
     exec @tarea = Genera_ID_Informe_2022 @cliente,@informe


     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 */


      update informes set informe =@tarea , cliente=@p_cliente, identificador=@identificador, numero=@numero, fecha_ingreso=@ahora, id_tipo_informe=@tipo_subinforme,
       objeto_informe=null, historial_informe=@preffix + 'Creación del informe.', ingresado_por=@p_user_id, idioma=@p_idioma, 
       filial=@p_filial, prioridad=iif(@prioridad_tarea > 0, @prioridad_tarea, @prioridad), datamanager=@p_datamanager, estado_informe='I', orden=@orden, informe_padre=@informe      
      where informe = @tarea and idreg= @tarea

      /* 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,@USID
    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 (ISNULL(@item_contrato,0) > 0) begin

     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()

     set @informes_log = '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, GETDATE(), @p_user_id, NULL, NULL, GETDATE(), GETDATE()+700, 0, 'spa', 'IV123456', 0, GETDATE()+700,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, GETDATE(), GETDATE()+700, 1, GETDATE(), @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, GETDATE(), @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, GETDATE())

     -- 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
 /** 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_contraparte_informe') begin
  if (@p_fecha_limite_doc is null)
  begin
   set @p_fecha_limite_doc = getdate()+2
  end
  if (@p_fecha_comprometida is null)
  begin
   set @p_fecha_comprometida = getdate()+5
  end

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

  SET @prioridad = 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
   exec @informe = Genera_ID_Informe_2022 @cliente,null
   
   /* Ingresar el informe en el sistema de terceros */
   declare @informeIVterceros int
   declare @cliIVTerceros int=632095719
   declare @dependency_tree varchar(200)=null

   exec @informeIVterceros = Genera_ID_Informe_2022 @cliIVTerceros,null

   

   if (@informe IS NOT NULL) begin

    SET @hist = @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

    set @ahora = getdate()
    declare @superv int = null
    declare @idTarea int = null 
    declare @duracionDesign int = 0

    select top 1 @superv = LN_Team_Members.user_id
    from LN_informe_design inner join ln_tareas on (ln_tareas.idTarea = LN_informe_design.idElem and LN_informe_design.tipo_elem='T')
    left join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
    left join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
    where LN_informe_design.idTipoInforme =  @p_id_tipo_informe
    and LN_Team_Members.id_team=@p_team_id
    and LN_Team_Members.activo=1
    and LN_Team_Members.isdefault=1
    and LN_Roles.id_rol=1 

        update informes set informe= @informe, cliente=@p_cliente, identificador=@identificador, numero=@numero, fecha_ingreso=@ahora, id_tipo_informe=@p_id_tipo_informe,
     objeto_informe='INFORME GENERADO A TRAVES DE WEB INQUIRO VITAE', historial_informe=@hist, ingresado_por=@p_user_id, idioma=@p_idioma, filial=@p_filial, prioridad=@prioridad, datamanager=@p_datamanager,
     /*asignado=iif(@p_estado_inicial = 'A' OR @p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @p_asignado, NULL), */
     asignado = @superv,
     estado_informe= 'A',/*@p_estado_inicial, */
/* fecha_asignado=iif(@p_estado_inicial = 'A' OR @p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @ahora, NULL), 
fecha_aceptacion=iif(@p_estado_inicial = 'T' OR @p_estado_inicial = 'P', @ahora, NULL),
*/
     fecha_asignado=getdate(), 
     fecha_aceptacion=getdate(),

     excepcion=0, excepcion_prod=0, notificar_cliente=0, precio=null, moneda=null,
     nombre_contacto_1=@nombre_candidato, email_contacto_1=@p_email_candidato, telefono_contacto_1=@p_telefono_candidato,fecha_limite_doc=@fecha_limite,fecha_comprometida=@fecha_comprometida
     where informe = @informe and idreg= @informe

     /* Ingresar el informe en Fidelitas */
     declare @tipoinformeTerceros int = 9876021

     update informes set informe= @informeIVterceros, cliente=@cliIVTerceros, identificador=@identificador, numero=@numero, fecha_ingreso=@ahora, id_tipo_informe=@tipoinformeTerceros,
     objeto_informe='SOLICITUD DE DATA CLEANING TERCEROS', historial_informe=@hist, ingresado_por=@p_user_id, idioma=@p_idioma, filial=@p_filial, prioridad=@prioridad, datamanager=@p_datamanager,
     informeid_terceros = @informe,
     asignado = @superv,
     estado_informe= 'A',
     fecha_asignado=getdate(), 
     fecha_aceptacion=getdate(),
     excepcion=0, excepcion_prod=0, notificar_cliente=0, precio=null, moneda=null,
     nombre_contacto_1=@nombre_candidato, email_contacto_1=@p_email_candidato, telefono_contacto_1=@p_telefono_candidato,fecha_limite_doc=@fecha_limite,fecha_comprometida=@fecha_comprometida
     where informe = @informeIVterceros and idreg= @informeIVterceros


     

    /* Generación de las subtareas */

    DECLARE TiposSubinformes CURSOR FOR


    select ln_tareas.id_tipo_informe_blue, LN_informe_design.orden, isnull(LN_informe_design.prioridad,0) as prioridad_default,0 as genera_solicitud_doc,/*  tipos_informe.genera_solicitud_doc,*/
    /*ln_tareas.*,LN_Roles.*,*/LN_Team_Members.user_id as USID,ln_tareas.isinicial,LN_tareas.idTarea,LN_informe_design.duracion,LN_informe_design.dependency_tree  
    from LN_informe_design inner join ln_tareas on (ln_tareas.idTarea = LN_informe_design.idElem and LN_informe_design.tipo_elem='T')
    left join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
    left join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
    where LN_informe_design.idTipoInforme = @p_id_tipo_informe
    and LN_Team_Members.id_team= @p_team_id
    and LN_Team_Members.activo=1
    and LN_Team_Members.isdefault=1
    and LN_tareas.activo=1 
    and LN_informe_design.activo=1 
    order by LN_informe_design.orden



    OPEN TiposSubinformes
    FETCH NEXT FROM TiposSubinformes INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion,@USID,@isinicial,@idTarea,@duracionDesign,@dependency_tree
    WHILE @@FETCH_STATUS = 0 BEGIN


     -- EXEC LN_informes 'obtener_id', NULL, @tarea OUTPUT
     exec @tarea = Genera_ID_Informe_2022 @cliente,@informe

     --select @tarea as miTarea

     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 */

      update informes set informe =@tarea , cliente=@p_cliente, identificador=@identificador, numero=@numero, fecha_ingreso=getdate(), id_tipo_informe=@tipo_subinforme,
       objeto_informe=null, historial_informe=@preffix + 'Creación de tarea.', ingresado_por=@p_user_id, idioma=@p_idioma,asignado=@USID, fecha_inicio = getdate(),
       /*
        fecha_comprometida=getdate()+@duracionDesign,
        */
       fecha_comprometida=DATEADD(minute,@duracionDesign,getdate()),

       fecha_asignado=getdate(),
       filial=@p_filial, 
       /*prioridad=iif(@prioridad_tarea > 0, @prioridad_tarea, @prioridad), */
       prioridad=5,
       datamanager=@p_datamanager, estado_informe='A', orden=@orden, informe_padre=@informe,
       ready_to_start = iif(@dependency_tree is null,1,0),
       /*ready_to_start = @isinicial,*/
       idTarea=@idTarea

      where informe = @tarea and idreg= @tarea 

      /* 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,@USID,@isinicial,@idTarea,@duracionDesign,@dependency_tree
    END
    CLOSE TiposSubinformes
    DEALLOCATE TiposSubinformes



   /*TAREAS DEL INFORME DE TERCEROS DATA CLEANING*/

    DECLARE TipoTerceros CURSOR FOR

    select ln_tareas.id_tipo_informe_blue, LN_informe_design.orden, isnull(LN_informe_design.prioridad,0) as prioridad_default,0 as genera_solicitud_doc,/*  tipos_informe.genera_solicitud_doc,*/
    /*ln_tareas.*,LN_Roles.*,*/LN_Team_Members.user_id as USID,ln_tareas.isinicial,LN_tareas.idTarea,LN_informe_design.duracion,LN_informe_design.dependency_tree  
    from LN_informe_design inner join ln_tareas on (ln_tareas.idTarea = LN_informe_design.idElem and LN_informe_design.tipo_elem='T')
    left join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
    left join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
    where LN_informe_design.idTipoInforme =  @tipoinformeTerceros--@p_id_tipo_informe
    and LN_Team_Members.id_team=@p_team_id
    and LN_Team_Members.activo=1
    and LN_Team_Members.isdefault=1
    order by LN_informe_design.orden

    --informeid_terceros = @informe, @idTarea
    OPEN TipoTerceros
    FETCH NEXT FROM TipoTerceros INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion,@USID,@isinicial,@idTarea,@duracionDesign,@dependency_tree
    WHILE @@FETCH_STATUS = 0 BEGIN
     
     exec @tarea = Genera_ID_Informe_2022 @cliIVTerceros,@informeIVterceros

     /*select @tarea as miTarea*/

     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 */

      update informes set informe =@tarea , cliente=@cliIVTerceros, identificador=@identificador, numero=@numero, fecha_ingreso=getdate(), id_tipo_informe=@tipo_subinforme,
       objeto_informe=null, historial_informe=@preffix + 'Creación de tarea.', ingresado_por=@p_user_id, idioma=@p_idioma,asignado=@USID, 
       fecha_comprometida=DATEADD(minute,@duracionDesign,getdate()) ,
       filial=@p_filial, prioridad=iif(@prioridad_tarea > 0, @prioridad_tarea, @prioridad), datamanager=@p_datamanager, estado_informe='I', orden=@orden, informe_padre=@informeIVterceros,
       ready_to_start = iif(@dependency_tree is null,1,0),
       /*ready_to_start = @isinicial,*/
       
       informeid_terceros=@informe,idTarea=@idTarea 
      where informe = @tarea and idreg= @tarea

      /* 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 TipoTerceros INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion,@USID,@isinicial,@idTarea,@duracionDesign,@dependency_tree
    END
    CLOSE TipoTerceros
    DEALLOCATE TipoTerceros



   /* FIN DE TERCEROS*/



    /** 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,*/
      asignado = @USID,
      fecha_asignado = getdate(),
      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 = getdate(),
      fecha_iniciado = getdate(),
      estado_informe = 'E'
     WHERE informe = @id_tarea_gestion

    end

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

/* declare @consumo int
declare @informes_log varchar(max) = 'Nuevo informe ingresado para ' + @nombre_cliente + '<br>'
*/
     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()

     set @informes_log  = '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, GETDATE(), @p_user_id, NULL, NULL, GETDATE(), GETDATE()+700, 0, 'spa', 'IV123456', 0, GETDATE()+700,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, GETDATE(), GETDATE()+700, 1, GETDATE(), @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, GETDATE(), @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, GETDATE())

     -- 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




 if (@accion = 'alta_contraparte_informe') 
        begin

  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


   /** 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
   exec @informe = Genera_ID_Informe_2022 @cliente,null
   
   /* Ingresar el informe en el sistema de terceros */
   declare @infIVTerceros int
   declare @IVTerceros int=632095719
   declare @dep_tree varchar(200)=null

   /*exec @infIVTerceros = Genera_ID_Informe_2022 @IVTerceros,null*/
   if (@informe IS NOT NULL) begin

    SET @hist = @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

    set @ahora = getdate()
    declare @superV1 int = null
    declare @idTarea1 int = null 
    declare @duracionDesign1 int = 0

    /* AGB 
     @infIVTerceros int
     @IVTerceros int=632095719
     @dep_tree varchar(200)=null
     */

    select top 1 @superv1 = LN_Team_Members.user_id
    from LN_informe_design inner join ln_tareas on (ln_tareas.idTarea = LN_informe_design.idElem and LN_informe_design.tipo_elem='T')
    left join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
    left join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
    where LN_informe_design.idTipoInforme =  @p_id_tipo_informe
    and LN_Team_Members.id_team=@p_team_id
    and LN_Team_Members.activo=1
    and LN_Team_Members.isdefault=1
    and LN_Roles.id_rol=1 

        update informes set informe= @informe, cliente=@p_cliente, identificador=@identificador, numero=@numero, 
                    fecha_ingreso=@ahora, id_tipo_informe=@p_id_tipo_informe,
     objeto_informe='INFORME GENERADO A TRAVES DE WEB INQUIRO VITAE', historial_informe=@hist, ingresado_por=@p_user_id, 
                    idioma=@p_idioma, filial=@p_filial, prioridad=@prioridad, datamanager=@p_datamanager,
     asignado = @superv1,
     estado_informe= 'A',
     fecha_asignado=getdate(), 
     fecha_aceptacion=getdate(),
     nombre_contacto_1=@nombre_candidato, email_contacto_1=@p_email_candidato, telefono_contacto_1=@p_telefono_candidato,
     fecha_limite_doc=@p_fecha_limite_doc,
     fecha_comprometida=@p_fecha_comprometida
     /*,fecha_inicio=@p_fecha_inicio*/
     
     where informe = @informe and idreg= @informe

     /* Ingresar el informe en Fidelitas */
     declare @tip_informeTerceros int = 9876021


   /* update informes set informe= @infIVTerceros, cliente=@IVTerceros, identificador=@identificador, numero=@numero, 
                       fecha_ingreso=@ahora, id_tipo_informe=@tip_informeTerceros,
    objeto_informe='SOLICITUD DE DATA CLEANING TERCEROS', historial_informe=@hist, 
                       ingresado_por=@p_user_id, idioma=@p_idioma, filial=@p_filial, prioridad=@prioridad, datamanager=@p_datamanager,
    informeid_terceros = @informe,
    asignado = @superv1,
    estado_informe= 'A',
    fecha_asignado=getdate(), 
    fecha_aceptacion=getdate(),
    nombre_contacto_1=@nombre_candidato, email_contacto_1=@p_email_candidato, 
    telefono_contacto_1=@p_telefono_candidato,fecha_limite_doc=@p_fecha_limite_doc,fecha_comprometida=@p_fecha_comprometida
    where informe = @infIVTerceros and idreg= @infIVTerceros
   
               */
    /* Generación de las subtareas */

    DECLARE TiposSubinformes CURSOR FOR


    select ln_tareas.id_tipo_informe_blue, LN_informe_design.orden, isnull(LN_informe_design.prioridad,0) as prioridad_default,0 as genera_solicitud_doc,/*  tipos_informe.genera_solicitud_doc,*/
    LN_Team_Members.user_id as USID,ln_tareas.isinicial,LN_tareas.idTarea,LN_informe_design.duracion,LN_informe_design.dependency_tree  
    from LN_informe_design inner join ln_tareas on (ln_tareas.idTarea = LN_informe_design.idElem and LN_informe_design.tipo_elem='T')
    left join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
    left join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
    where LN_informe_design.idTipoInforme = @p_id_tipo_informe
    and LN_Team_Members.id_team= @p_team_id
    and LN_Team_Members.activo=1
    and LN_Team_Members.isdefault=1
    and LN_tareas.activo=1 
    and LN_informe_design.activo=1 
    order by LN_informe_design.orden



    OPEN TiposSubinformes
    FETCH NEXT FROM TiposSubinformes INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion,@USID,@isinicial,@idTarea,@duracionDesign1,@dep_tree
    WHILE @@FETCH_STATUS = 0 BEGIN


     -- EXEC LN_informes 'obtener_id', NULL, @tarea OUTPUT
     exec @tarea = Genera_ID_Informe_2022 @cliente,@informe

     --select @tarea as miTarea

     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 */

      update informes set informe =@tarea , 
       cliente=@p_cliente, 
       identificador=@identificador, 
       numero=@numero, 
       fecha_ingreso=@p_fecha_inicio,
       id_tipo_informe=@tipo_subinforme,
       objeto_informe=null, historial_informe=@preffix + 'Creación de tarea.',
       ingresado_por=@p_user_id, 
       idioma=@p_idioma,
       asignado=@USID, 
       
       fecha_inicio = @p_fecha_inicio,
       

       /*fecha_comprometida=@p_fecha_comprometida,*/
       fecha_comprometida= DATEADD(minute,isnull(@duracionDesign,1), @p_fecha_inicio) ,
       
       fecha_asignado=getdate(),
       
       filial=@p_filial, 
       prioridad=5,
       datamanager=@p_datamanager, 
       estado_informe='A', 
       orden=@orden, 
       informe_padre=@informe,
       ready_to_start = iif(@dep_tree is null,1,0),
       ready_to_start_date = iif(@dep_tree is null,getdate(),null),
       /*ready_to_start = @isinicial,*/
       idTarea=@idTarea
      where informe = @tarea and idreg= @tarea 

      /* 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,@USID,@isinicial,@idTarea,@duracionDesign1,@dep_tree
    END
    CLOSE TiposSubinformes
    DEALLOCATE TiposSubinformes



   /*TAREAS DEL INFORME DE TERCEROS DATA CLEANING*/

    DECLARE TareasTipoTerceros CURSOR FOR

    select ln_tareas.id_tipo_informe_blue, LN_informe_design.orden, isnull(LN_informe_design.prioridad,0) as prioridad_default,0 as genera_solicitud_doc,/*  tipos_informe.genera_solicitud_doc,*/
    /*ln_tareas.*,LN_Roles.*,*/LN_Team_Members.user_id as USID,ln_tareas.isinicial,LN_tareas.idTarea,LN_informe_design.duracion,LN_informe_design.dependency_tree  
    from LN_informe_design inner join ln_tareas on (ln_tareas.idTarea = LN_informe_design.idElem and LN_informe_design.tipo_elem='T')
    left join LN_Roles on ln_tareas.perfil_usr_default=ln_roles.id_rol
    left join LN_Team_Members on LN_Team_Members.id_rol=LN_Roles.id_rol
    where LN_informe_design.idTipoInforme =  @tip_informeTerceros
    and LN_Team_Members.id_team=@p_team_id
    and LN_Team_Members.activo=1
    and LN_Team_Members.isdefault=1
    order by LN_informe_design.orden

    --select 'TAREAS TERCERO',@@FETCH_STATUS as GGGG

    OPEN TareasTipoTerceros
    FETCH NEXT FROM TareasTipoTerceros INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion,@USID,@isinicial,@idTarea,@duracionDesign1,@dep_tree
    WHILE @@FETCH_STATUS = 0 BEGIN
   
     
                    /*
                     exec @tarea = Genera_ID_Informe_2022  @IVTerceros,@infIVTerceros
                    
                     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 */
                    
                     update informes set /*informe =@tarea , cliente=@IVTerceros,*/ identificador=@identificador, numero=@numero, fecha_ingreso=getdate(), id_tipo_informe=@tipo_subinforme,
                     objeto_informe=null, historial_informe=@preffix + 'Creación de tarea.', ingresado_por=@p_user_id, idioma=@p_idioma,asignado=@USID, 
                     fecha_comprometida=DATEADD(minute,isnull(@duracionDesign,1),getdate()) ,
                     filial=@p_filial, prioridad=iif(isnull(@prioridad_tarea,0) > 0, isnull(@prioridad_tarea,0), isnull(@prioridad,1)), 
                     datamanager=@p_datamanager, estado_informe='I', orden=@orden, informe_padre=@infIVTerceros,
                    
                     ready_to_start = iif(@dep_tree is null,1,0),
                     ready_to_start_date = iif(@dep_tree is null,getdate(),null),
                    
                     informeid_terceros=@informe,idTarea=@idTarea 
                     where informe = @tarea and idreg= @tarea
                    
                     /* 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 TareasTipoTerceros INTO @tipo_subinforme, @orden, @prioridad_tarea, @activa_gestion,@USID,@isinicial,@idTarea,@duracionDesign,@dependency_tree
    END
    CLOSE TareasTipoTerceros
    DEALLOCATE TareasTipoTerceros



   /* FIN DE TERCEROS*/



    /** 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,*/
      asignado = @USID,
      fecha_asignado = getdate(),
      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 = getdate(),
      fecha_iniciado = getdate(),
      estado_informe = 'E'
     WHERE informe = @id_tarea_gestion

    end

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


     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()

     set @informes_log  = '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,id_referencia)
 
     values (@p_cliente, @p_id_padron, @country, @p_notas_candidato, 1, @nombre_candidato, @p_email_candidato, 
       @p_telefono_candidato, GETDATE(), @p_user_id, NULL, NULL, GETDATE(), GETDATE()+700, 0, 'spa', 'IV123456', 0, GETDATE()+700,10,@p_id_referencia)

     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, GETDATE(), GETDATE()+700, 1, GETDATE(), @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, GETDATE(), @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,idTeam)
        values (@contrap, @gestion, @producto_led, @informe, 0,@p_team_id)

     end


     -- Asentar consumo de usuario 
     insert into usuarios_consumos (user_id, cuit_consultado, id_producto, created)
     values (@p_user_id, @numero, @p_id_producto, GETDATE())
     
     /* Actualizar el legajo */
     EXEC LN_actualizar_legajo @contrap, @gestion, @p_user_id
     
     -- 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*/ ln_gestiones.iv_busqueda
   from LN_docs
   inner join LN_contrapartes on LN_docs.contraparte = LN_contrapartes.contraparte
   inner join ln_gestiones on (ln_gestiones.contraparte = ln_docs.contraparte and ln_gestiones.gestion = ln_docs.gestion)
   /*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 = 'contrapartes_repetidos' )
        BEGIN

            SELECT 
                LN_contrapartes.contraparte,
                LN_demo.visible
            FROM LN_contrapartes
                INNER JOIN LN_demo ON LN_demo.contraparte=LN_contrapartes.contraparte
            WHERE
                LN_contrapartes.cliente=@p_cliente
                AND LN_contrapartes.id_padron=@p_id_padron
                AND LN_contrapartes.activo=1
            RETURN;
        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' or LN_familias.servicio_contratado=10)
  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