dbo.LN_Panel_Control_SAAS_1

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)
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_Panel_Control_SAAS_1 
 @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)
 */
 select  distinct upper(p2.nombre_persona) as asegurado, 
   SAAS_Contratos.nro_contrato as contrato,
   upper(padrones.nombre_persona) as analizado,
   --convert(varchar(10),max(hc.fecha_consulta),103) as Fecha,
   historial.id, 
   (select convert(varchar(10),fecha_consulta,103) from SAAS_Historial_Consultas where saas_historial_consulta = historial.id) as Fecha,
   (select estado from SAAS_Historial_Consultas where saas_historial_consulta = historial.id) as estado,
   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 
  

 from LN_contrapartes lnc

    inner JOIN
    (select contraparte, saas_usuario_contrato, MAX(saas_historial_consulta) as id
      from SAAS_Historial_Consultas
      group by contraparte, saas_usuario_contrato) as historial
      on (historial.contraparte = lnc.contraparte) 
      

    --inner join SAAS_Historial_Consultas hc on hc.contraparte = lnc.contraparte
    inner join SAAS_Usuarios_Contratos uc on uc.saas_usuario_contrato = historial.saas_usuario_contrato
    inner join SAAS_Usuarios u on u.saas_usuario = uc.saas_usuario    
    inner join SAAS_Contratos on SAAS_Contratos.saas_contrato = uc.saas_contrato
    inner join SAAS_Clientes c on c.saas_cliente = u.saas_cliente 

    inner join padrones on padrones.id_padron = Lnc.id_padron
    inner join padrones p2 on c.id_padron = p2.id_padron

    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 (@p_contraparte = 0 or lnc.contraparte = @p_contraparte) 
    /*
     GROUP BY    p2.nombre_persona, 
     SAAS_Contratos.nro_contrato,
     upper(padrones.nombre_persona),
     novedad.valor,
     final.valor, 
     final.color, 
     black.valor, 
     formales.valor,
     fiscales.valor,
     contables.valor,
     legales.valor,
     bancos.valor,
     cheques.valor,
     lnc.contraparte, 
     LN_excepciones_final.tipo_excepcion*/
    ORDER BY analizado

END 
GO