dbo.LN_Panel_Control_SAAS

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
--
-- =============================================
-- Author: María Inés Soria
-- Create date: 19/12/2019
-- Description: Panel de Control para clientes con SAAS (como Coface)
-- =============================================
/*
exec LN_Panel_Control_SAAS 515494256, 193734

select * from ln_contrapartes where id_padron in (
select id_padron from padrones where identificacion = '20285794235') 
and cliente = 515494256


exec LN_Panel_Control_SAAS_datos 515494256
*/
CREATE OR ALTER PROCEDURE dbo.LN_Panel_Control_SAAS 
 @p_cliente int = 515494256, 
 @p_contraparte int = 0
AS
BEGIN
 SET NOCOUNT ON;
 /*
  select * From SAAS_Historial_Consultas where contraparte = 45099
  136,
  247
  )
  select * from SAAS_Usuarios_Contratos where saas_usuario_contrato in (1,3)
  */
 declare @historial table 
 (estado char(1), 
  bancos_estado char(1), 
  cheques_estado char(1), 
  contables_estado char(1), 
  patrimonio_estado char(1), 
  fiscales_estado char(1), 
  formales_estado char(1), 
  legales_estado char(1), 
  black_list varchar(1),

  fecha_consulta date, numero_contrato varchar(50),razon_social varchar(max),identificacion varchar(100), 
  nombre varchar(max), email varchar(max), texto varchar(max),contraparte int,asegurado varchar(max), color_final varchar(10), estado_final varchar(50)
  /*,
    black_list varchar(1),
    black_list_score int
    */
   , contrato_activo bit, cliente_activo bit)
  
 if (isnull(@p_contraparte,0) != 0) 
 begin
  insert into @historial exec [SAAS_historial] 0,0,@p_contraparte,0 
  if (isnull(@p_cliente,0) = 0)
  begin
  set @p_cliente = (select cliente from ln_contrapartes where contraparte = @p_contraparte) 
  end 
 end
 else
 begin 
  insert into @historial exec [SAAS_historial] 0,0,0,@p_cliente
 end 
  
 select  distinct upper(historial.asegurado) as asegurado, 
   historial.numero_contrato as contrato,
   historial.razon_social as analizado,
   historial.identificacion as identificacion,
   convert(varchar(10),historial.fecha_consulta, 103) as Fecha, 
   historial.fecha_consulta, 
   historial.estado as estado_consulta, 
   novedad.valor as ultima_novedad, 
   final.color as color_final, 
   final.valor as estado, 
   black.valor as blacklist_estado, 
   formales.valor as formales_estado,
   fiscales.valor as fiscales_estado,
   contables.valor as contables_estado,
   patrimonio.valor as patrimonio_estado,
   legales.valor as legales_estado,
   bancos.valor as bancos_estado,
   cheques.valor as cheques_estado,
   lnc.contraparte as contraparte, 
   LN_excepciones_final.tipo_excepcion as excepcion,
   historial.contrato_activo, 
   historial.cliente_activo
  /* ,black_list,black_list_score*/

  

 from LN_contrapartes lnc

    inner JOIN @historial historial on historial.contraparte = lnc.contraparte 

    inner join LN_gestiones on lnc.contraparte = LN_gestiones.contraparte
    inner join LN_contraparte_familias on lnc.contraparte = LN_contraparte_familias.contraparte
    inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia

    inner join LN_cliente_productos on lnc.cliente = LN_cliente_productos.cliente
    inner join LN_familia_productos on (LN_cliente_productos.producto = LN_familia_productos.producto
             and LN_contraparte_familias.familia = LN_familia_productos.familia)
    left outer join LN_excepciones_final  on (LN_excepciones_final.contraparte = lnc.contraparte 
               AND LN_excepciones_final.estado = 1
               AND LN_excepciones_final.vigencia >= GETDATE()) 


    LEFT OUTER JOIN
    (select contraparte, gestion, convert(varchar(10),valor_fecha,103) as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'ULTIMA_NOVEDAD') as novedad
      on (novedad.contraparte = lnc.contraparte and
       novedad.gestion = LN_gestiones.gestion) 

    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionBlackList') as black
      on (black.contraparte = lnc.contraparte and
       black.gestion = LN_gestiones.gestion) 
       
    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionFormales') as formales
      on (formales.contraparte = lnc.contraparte and
       formales.gestion = LN_gestiones.gestion) 

    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionFiscales') as fiscales
      on (fiscales.contraparte = lnc.contraparte and
       fiscales.gestion = LN_gestiones.gestion) 

    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionLegales') as legales
      on (legales.contraparte = lnc.contraparte and
       legales.gestion = LN_gestiones.gestion) 

    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionContables') as contables
      on (contables.contraparte = lnc.contraparte and
       contables.gestion = LN_gestiones.gestion) 

    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionPatrimonio') as patrimonio
      on (patrimonio.contraparte = lnc.contraparte and
       patrimonio.gestion = LN_gestiones.gestion) 


    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionBancos') as bancos
      on (bancos.contraparte = lnc.contraparte and
       bancos.gestion = LN_gestiones.gestion) 

    LEFT OUTER JOIN
    (select contraparte, gestion, valor_texto as valor
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      where clave_indicador = 'AprobacionCheques') as cheques
      on (cheques.contraparte = lnc.contraparte and
       cheques.gestion = LN_gestiones.gestion)     
    
    LEFT OUTER JOIN
    (select contraparte, gestion, 
            valor = isnull(ln_cliente_nivel.texto_largo,LN_contraparte_indicadores.valor_texto), 
      color = isnull(ln_cliente_nivel.color_fondo,'') 
      from LN_contraparte_indicadores 
      inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
      left outer join ln_cliente_nivel 
      on (ln_cliente_nivel.cliente = @p_cliente 
          and ln_cliente_nivel.indicador = LN_indicadores.indicador 
          and desde <= LN_contraparte_indicadores.valor_numerico and hasta > LN_contraparte_indicadores.valor_numerico)
      where clave_indicador = 'AprobacionFinal') as final
      on (final.contraparte = lnc.contraparte and
       final.gestion = LN_gestiones.gestion) 
  
    where lnc.cliente = @p_cliente
    and lnc.activo = 1
    and LN_familias.visible = 1
    and LN_contraparte_familias.activo = 1
    and LN_gestiones.desde <= GETDATE()
    and LN_gestiones.hasta >= GETDATE()
    and LN_familia_productos.activo = 1
    and LN_cliente_productos.activo = 1
    and (isnull(@p_contraparte,0) = 0 or lnc.contraparte = @p_contraparte) 
    and not exists (select * from @historial historial2 where historial2.contraparte = historial.contraparte and historial2.numero_contrato = historial.numero_contrato and historial2.fecha_consulta > historial.fecha_consulta) 
    
    ORDER BY analizado

END 
GO