dbo.SAAS_Consulta_CUIT_ANTERIOR

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: María Inés Soria
-- Create date: 13/11/2019 
-- Cuando se consulta un CUIT en saas
-- 1) Según el saas_usuario_contrato, buscamos a que cliente de fidelitas pertenece
-- 2) Si el cuit consultado no está cargado como contraparte del cliente, lo damos de alta (ln_contrapartes, ln_gestiones, ln_contraparte_familia)
-- 3) Calculamos todos los scores, los guardamos
--    el score de balances como se calcula por php, lo tenemos que calcular antes para el cuit y mandarlo como parámetro
-- 4) Si cualquiera de todos los score es Rojo, el score final dará Rojo
-- En la tabla ln_cliente_nivel tienen que estar cargados los rangos de rojo, amarillo, verde para todos los indicadores de score
-- =============================================
/*
exec [SAAS_Consulta_CUIT] 94437923,1,'30556565798',0,147,29.9

exec [SAAS_Consulta_CUIT] 94437923 , 1 , '30709653497' , 0 , 147 , 35

select * from LN_contrapartes where cliente = 515494256 and activo =  1
*/

CREATE OR ALTER PROCEDURE dbo.SAAS_Consulta_CUIT_ANTERIOR 
 @p_user_id int, 
 @p_saas_usuario_contrato int, 
 @p_cuit varchar(100), 
 @p_id_padron int, 
 @p_familia int = null, 
 @p_score_balance decimal(5,2) = 100, --> como se calcula por php lo tenemos que calcular antes (para el cuit) y mandarlo como parámetro
 @prueba varchar(2) = 'no'

AS
BEGIN

 SET NOCOUNT ON;
 declare @country varchar(2)
 declare @contraparte int, @gest int 

 if (isnull(@p_saas_usuario_contrato,0) = 0) return 
 if (isnull(@p_cuit,'') = '' and isnull(@p_id_padron,0) = 0) return 
  
 declare @cliente int = null
 set @cliente =  (SELECT cliente from saas_clientes 
                           where saas_cliente = (SELECT SAAS_Usuarios_Contratos.saas_cliente
                                 FROM SAAS_Usuarios_Contratos
                             WHERE saas_usuario_contrato = @p_saas_usuario_contrato))
 
 if (isnull(@p_user_id,0) = 0) 
  set @p_user_id = (SELECT SAAS_Usuarios_Contratos.saas_usuario
        FROM SAAS_Usuarios_Contratos
       WHERE saas_usuario_contrato = @p_saas_usuario_contrato) 

 if (@cliente is null) return
 
 if (isnull(@p_cuit,'') != '')
  select top 1 @p_id_padron = id_padron, 
      @country = isnull(country_code, 'AR')
    from padrones 
    left join identificadores on identificadores.identificador = padrones.identificador
    where identificacion = @p_cuit 
     and habilitado = 1
 else 
  if (isnull(@p_id_padron,0) != 0) 
   select top 1 @p_id_padron = id_padron, 
       @country = isnull(country_code, 'AR'), 
       @p_cuit = identificacion
     from padrones 
     left join identificadores on identificadores.identificador = padrones.identificador
     where id_padron = @p_id_padron
      and habilitado = 1

 if (@p_familia is null or @p_familia = 0) --> si no viene la familia, le ponemos la de ese cliente para ese pais
  select top 1 @p_familia = familia from LN_familias where cliente = @cliente and visible = 1 and country_code = @country

 if (@p_familia is null or @p_familia = 0) --> si no encontramos para ese pais, le ponemos el de argentina
  select top 1 @p_familia = familia from LN_familias where cliente = @cliente and visible = 1 and country_code = 'AR'
 
 --> Buscamos si ese CUIT ya está cargado como contraparte del Cliente 
 --> Si no está lo damos de alta

 if not exists (select * from LN_contrapartes 
                 where LN_contrapartes.cliente = @cliente 
       and LN_contrapartes.id_padron = @p_id_padron 
       and LN_contrapartes.activo = 1
       and LN_contrapartes.subcontratista = 0) 
 begin
  --> contraparte
  insert into LN_contrapartes (id_padron,    cliente,  country_code, activo, clave,    clave_temporal, clave_expira,                vigencia_desde,          vigencia_hasta, carga_inicial, subcontratista,  created,    created_by)
                       values (@p_id_padron, @cliente, @country,     1,      'sinuso', 0,               DATEADD(year,10, getdate()), convert(date,GETDATE()), '2099-12-31',   0,             0,              GETDATE(),  @p_user_id)
 
  SET @contraparte = SCOPE_IDENTITY()
  --> gestion 
  insert into ln_gestiones(cliente,  contraparte,  desde,                  hasta,        vigente, created,  created_by)
                   values (@cliente, @contraparte, convert(date,GETDATE()),'2099-12-31', 1,       GETDATE(),@p_user_id)
  --> familia
  insert into LN_contraparte_familias(contraparte,  familia,    estado_homologacion, activo, created,  created_by)
                              values (@contraparte, @p_familia, 'N',                 1,      GETDATE(),@p_user_id) 
 end
 else 
 begin 
  set @contraparte = (select LN_contrapartes.contraparte from LN_contrapartes 
        where LN_contrapartes.cliente = @cliente 
          and LN_contrapartes.id_padron = @p_id_padron 
          and LN_contrapartes.activo = 1
          and LN_contrapartes.subcontratista = 0)
  if not exists (select * from LN_contraparte_familias
      where contraparte = @contraparte  
          and familia = @p_familia
        and activo = 1)
   insert into LN_contraparte_familias(contraparte,  familia,    estado_homologacion, activo, created,  created_by)
                                  values (@contraparte, @p_familia, 'N',                 1,      GETDATE(),@p_user_id) 
  
 end 

 SELECT @gest = LN_gestiones.gestion
   FROM LN_gestiones INNER JOIN LN_contrapartes
   ON LN_gestiones.contraparte = LN_contrapartes.contraparte
   INNER JOIN padrones ON LN_contrapartes.id_padron = padrones.id_padron
   inner join LN_contraparte_familias on LN_contraparte_familias.contraparte = LN_contrapartes.contraparte
   WHERE LN_gestiones.desde <= GETDATE()
   AND LN_gestiones.hasta >= GETDATE()
   AND LN_contrapartes.activo = 1
   AND LN_contrapartes.contraparte = @contraparte

 --select @contraparte as contraparte, @p_familia as familia, @gest as gestion 

 --> Calculamos los scores
 declare @indicador int = null
 declare @estado char(1) = 'V'
 declare @score decimal(5,2)
 declare @color varchar(1)

 -- Formales
 declare @formales_estado char(1) = 'V'
 declare @formales_score  decimal(5,2) = 100 
 -- Fiscales
 declare @fiscales_estado char(1) = 'V'
 declare @fiscales_score  decimal(5,2) = 100 
 
 -- Contables --> se manda como parametro
 declare @Contables_estado char(1) = 'V'
 declare @Contables_score  decimal(5,2) = @p_score_balance 
 select @indicador = LN_indicadores.indicador
  from LN_indicadores
  where clave_indicador = 'SCORE_BAL'
  and (exists (select * from LN_cliente_indicadores where indicador = LN_indicadores.indicador and cliente = @cliente) 
    or (select isnull(suscribe_indicadores,0) from LN_productos where LN_productos.producto = LN_indicadores.producto) = 0)
 set @score = @Contables_score
 set @color = null 
 -- Busco rango para el ROJO - AMARILLO - VERDE 
 IF (@score is not null and @indicador is not null) 
 begin
  select @color = texto from ln_cliente_nivel where cliente = @cliente and indicador = @indicador and desde <= @score and hasta > @score
  --exec LN_Graba_indicador @contraparte, @gest, @indicador, @score, @p_user_id 
  if (@color is null)
  begin 
   if (@score = -100) set @contables_estado = 'R'
  end 
  else 
  begin 
   set @Contables_estado = @color
  end 
 end 

 -- Legales 
 declare @Legales_estado char(1) = 'V'
 declare @Legales_score  decimal(5,2) = 100 
 DECLARE @score_judicial decimal(10,2)
 EXEC [dbo].[LN_score_judicial] @contraparte, @score_judicial output
 if ( @score_judicial is not null ) set @Legales_score = @score_judicial
 select @indicador = LN_indicadores.indicador
  from LN_indicadores
  where clave_indicador = 'SCORE_JUDICIAL'
  and (exists (select * from LN_cliente_indicadores where indicador = LN_indicadores.indicador and cliente = @cliente) 
    or (select isnull(suscribe_indicadores,0) from LN_productos where LN_productos.producto = LN_indicadores.producto) = 0)
 set @score = @Legales_score
 set @color = null 
 -- Busco rango para el ROJO - AMARILLO - VERDE 
 IF (@score is not null and @indicador is not null) 
 begin
  select @color = texto from ln_cliente_nivel where cliente = @cliente and indicador = @indicador and desde <= @score and hasta > @score
  --exec LN_Graba_indicador @contraparte, @gest, @indicador, @score, @p_user_id 
  if (@color is null)
  begin 
   if (@score = -100) set @legales_estado = 'R'
  end 
  else 
  begin 
   set @legales_estado = @color
  end 
 end 

 -- Bancos y Cheques 
 declare @Bancos_estado char(1) = 'V'
 declare @Bancos_score  decimal(5,2) = 100 
 declare @Cheques_estado char(1) = 'V'
 declare @Cheques_score  decimal(5,2) = 100 

 DECLARE @score_alertas  decimal(10,2),
   @score_cendeu   decimal(10,2),
   @score_ch       decimal(10,2)
 EXEC [dbo].[LN_score_alertas] @p_familia, @contraparte, @score_alertas output, @score_cendeu output, @score_ch output, @prueba
 if ( @score_cendeu is not null ) set @Bancos_score   = @score_cendeu
 if ( @score_ch is not null )     set @Cheques_score  = @score_ch

 select @indicador = LN_indicadores.indicador
  from LN_indicadores
  where clave_indicador = 'SCORE_CENDEU'
  and (exists (select * from LN_cliente_indicadores where indicador = LN_indicadores.indicador and cliente = @cliente) 
  or (select isnull(suscribe_indicadores,0) from LN_productos where LN_productos.producto = LN_indicadores.producto) = 0)
 set @score = @score_cendeu
 set @color = null 
 IF (@score is not null and @indicador is not null) 
 begin
  select @color = texto from ln_cliente_nivel where cliente = @cliente and indicador = @indicador and desde <= @score and hasta > @score
  --exec LN_Graba_indicador @contraparte, @gest, @indicador, @score, @p_user_id 
  if (@color is null)
  begin 
   if (@score = -100) set @Bancos_estado = 'R'
  end 
  else 
  begin 
   set @Bancos_estado = @color
  end 
 end 

 select @indicador = LN_indicadores.indicador
  from LN_indicadores
  where clave_indicador = 'SCORE_CHEQUES'
  and (exists (select * from LN_cliente_indicadores where indicador = LN_indicadores.indicador and cliente = @cliente) 
    or (select isnull(suscribe_indicadores,0) from LN_productos where LN_productos.producto = LN_indicadores.producto) = 0)
 set @score = @score_ch
 set @color = null 
 IF (@score is not null and @indicador is not null) 
 begin
  select @color = texto from ln_cliente_nivel where cliente = @cliente and indicador = @indicador and desde <= @score and hasta > @score
  --exec LN_Graba_indicador @contraparte, @gest, @indicador, @score, @p_user_id 
  if (@color is null)
  begin 
   if (@score = -100) set @Cheques_estado = 'R'
  end 
  else 
  begin 
   set @Cheques_estado = @color
  end 
 end 

 if ( @formales_estado  = 'R' or 
      @fiscales_estado  = 'R' or 
   @Contables_estado = 'R' or 
   @Legales_estado   = 'R' or 
   @Bancos_estado    = 'R' or 
   @Cheques_estado   = 'R') 
   set @estado = 'R' -- Rojo 
 else 
  if ( @formales_estado  = 'A' or 
    @fiscales_estado  = 'A' or 
    @Contables_estado = 'A' or 
    @Legales_estado   = 'A' or 
    @Bancos_estado    = 'A' or 
    @Cheques_estado   = 'A')  
    set @estado = 'A' -- Amarillo 
    else 
    set @estado = 'V' -- Verde 
 
 insert into SAAS_Historial_Consultas (saas_usuario_contrato, fecha_consulta, 
                                       estado, 
            formales_estado, formales_score, 
            fiscales_estado, fiscales_score, 
            contables_estado, contables_score, 
            legales_estado, legales_score, 
            bancos_estado, bancos_score, 
            cheques_estado, cheques_score, 
            created, created_by,
            contraparte) 
                      values (@p_saas_usuario_contrato, GETDATE(), 
                                       @estado, 
            @formales_estado, @formales_score, 
            @fiscales_estado, @fiscales_score, 
            @contables_estado, @contables_score, 
            @legales_estado, @legales_score, 
            @bancos_estado, @bancos_score, 
            @cheques_estado, @cheques_score, 
            GETDATE(), @p_user_id, 
            @contraparte) 
 declare @id int = SCOPE_IDENTITY()

 insert into LN_contrapartes_queue(contraparte, created, processed) values (@contraparte, GETDATE(),0)

 select * from SAAS_Historial_Consultas where saas_historial_consulta = @id 

END    
--select * from LN_contraparte_indicadores where cliente = 2254875

GO