dbo.LN_score_formal_antiguedad_edad

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <19/03/2019>
-- Modificado: 8/8/2023
-- Description: Cálculo de Score de alertas Formales 
-- =============================================
Mail de Coface 8/8/2023 
Personas Humanas – Edad: de 25 a 75 años   APROBADO
Personas Humanas – Edad: menor de 25 años o Edad: mayor a 75 años   RECHAZADO
Personas Jurídicas: Fecha de contrato social: mayor o igual a 2 años APROBADO
Personas Jurídicas: Fecha de contrato social: menor a 2 años.  RECHAZADO
Sucesiones  RECHAZADO (nosotros tomamos que sea fallecido)

Antes: 
Personas
Menor de 25 o mayor de 75 era 50%
Menor de 18 / Fallecido, rechazado
Empresas
Fecha de inicio de actividad  < 2 años, rechazado (ahora toma fecha de contrato social)

-- =============================================

DECLARE @score_edad decimal(10,2)
EXEC [dbo].[LN_score_formal_antiguedad_edad] 206404, @score_edad output
select @score_edad as score_edad
select * from ln_score_detalle_killer where contraparte = 206404
select * from ln_contrapartes where contraparte = 164995
select * from padrones where id_padron = 324792
select * from datos_personas where c01_cuit = '20083155109'
select * from ln_contrapartes where id_padron = (select id_padron from padrones where identificacion = '20076868116')

update ln_dias_alertas set dias = 75 where tabla = 'mayor' and cliente = 515494256
(select COUNT(*) from LN_cliente_indicadores where cliente = 515494256 and indicador = 245)
*/
CREATE OR ALTER PROCEDURE dbo.LN_score_formal_antiguedad_edad 
  @contraparte int,
  @score_formal decimal(10,2) = null output
AS
BEGIN
 SET NOCOUNT ON;
 declare @JF char(1) 
 declare @puntaje decimal(10,2) = null
 declare @detalle varchar(max) = null
 declare @detallep varchar(max) = null

 declare @cuit varchar(20)
 select @cuit = identificacion, @JF = upper(tipo_persona)
   from padrones 
   where id_padron = (select id_padron from ln_contrapartes where contraparte = @contraparte)

 declare @cliente int = (select cliente from ln_contrapartes where contraparte = @contraparte)

 if (@JF is null or (@JF != 'F' and @JF != 'J'))
  begin
  if (@cuit like '2%') set @JF = 'F' else set @JF = 'J'
  end 

 declare @gestion int = (select gestion 
       from LN_gestiones 
       where contraparte = @contraparte 
         AND LN_gestiones.desde <= GETDATE()
       AND LN_gestiones.hasta >= GETDATE())
 declare @indicador int = null
 declare @etiqueta varchar(100) = null
 select @indicador = indicador, @etiqueta = LN_productos.etiqueta
           from ln_indicadores 
        inner join LN_productos on ln_productos.producto = LN_indicadores.producto 
        where clave_indicador = 'AprobacionFormales'
 declare @tiene_indicador int  = (select COUNT(*) from LN_cliente_indicadores where cliente = @cliente and indicador = @indicador)
 if (exists (select * From ln_score_detalle_killer
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @indicador
       and visible = 1)
  and @tiene_indicador = 0)
 begin
  update ln_score_detalle_killer set visible = 0, modified = GETDATE()
   where contraparte = @contraparte 
     and gestion = @gestion 
     and indicador = @indicador
     and visible = 1
 end 

 if (exists (select * From ln_score_detalle_puntos
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @indicador
       and visible = 1)
  and @tiene_indicador = 0)
 begin
  update ln_score_detalle_puntos set visible = 0, modified = GETDATE()
   where contraparte = @contraparte 
     and gestion = @gestion 
     and indicador = @indicador
     and visible = 1
 end 

 if (exists (select * From LN_contraparte_indicadores
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @indicador
       and visible = 1)
  and @tiene_indicador = 0)
 begin 
  update LN_contraparte_indicadores set visible = 0, modified = GETDATE()
   where contraparte = @contraparte 
     and gestion = @gestion 
     and indicador = @indicador
     and visible = 1
 end 
 /*
  if  (@tiene_indicador = 0)
  begin
  set @score_edad = null
  return 
  end 
  */

set @puntaje = 100

if (@JF = 'F') 
begin
 declare @fecha_defuncion date = null
 declare @edad int = null 
 declare @defuncion bit = null
 declare @p_menor int = null 
 declare @p_mayor int = null 
 declare @p_puntos_menor decimal(5,2) = null
 declare @p_puntos_mayor decimal(5,2) = null 
 declare @p_puntos_fallecido decimal(5,2) = null 

 select @fecha_defuncion = fecha_defuncion, @defuncion = ISNULL(defuncion,0), 
  @edad = (cast(datediff(dd,c02_fecha_nac,GETDATE()) / 365.25 as int))
  from DATOS_PERSONAS
  where c01_cuit = @cuit

 --select @edad as edad
 --> buscamos si está personalizado en la tabla ln_dias_alertas (en el campo dias la edad esta en años)
 select @p_menor   = dias, 
     @p_puntos_menor = puntos
   from ln_dias_alertas where tabla = 'menor' and cliente = @cliente
 if (@p_menor is null) set @p_menor = 25      -- default 25 años
 if (@p_puntos_menor is null) set @p_puntos_menor = -100 -- ahora el default es -100 (antes era 50)

 select @p_mayor   = dias, 
     @p_puntos_mayor = puntos
   from ln_dias_alertas where tabla = 'mayor' and cliente = @cliente
 if (@p_mayor is null) set @p_mayor = 65     -- default 65 años
 if (@p_puntos_mayor is null) set @p_puntos_mayor = -100 -- ahora el default es -100 (antes era 50)

 if (@fecha_defuncion is null and isnull(@defuncion,0) = 0) 
 begin
  if (@edad is not null and @edad < @p_menor) 
  begin
    if (@p_puntos_menor < @puntaje)
     set @puntaje = @p_puntos_menor
    if  (@p_puntos_menor = -100)
    begin
     if @detalle is null 
      set @detalle = 'FORMALES:' 
     else
      set @detalle = @detalle + ','
    
     set @detalle = @detalle + ' PERSONA MENOR DE ' + CONVERT(varchar, @p_menor) + ' AÑOS'
    end 
    else
    begin
     if @detallep is null 
      set @detallep = 'FORMALES:' 
     else
      set @detallep = @detallep + ','
    
     set @detallep = @detallep + ' PERSONA MENOR DE ' + CONVERT(varchar, @p_menor) + ' AÑOS'
    end 
  end 

  if (@edad is not null and @edad > @p_mayor) 
  begin
    if (@p_puntos_mayor < @puntaje)
     set @puntaje = @p_puntos_mayor
    if  (@p_puntos_mayor = -100)
    begin
     if @detalle is null 
      set @detalle = 'FORMALES:' 
     else
      set @detalle = @detalle + ','
    
     set @detalle = @detalle + ' PERSONA MAYOR DE ' + CONVERT(varchar, @p_mayor) + ' AÑOS'
    end 
    else
    begin 
     if @detallep is null 
      set @detallep = 'FORMALES:' 
     else
      set @detallep = @detallep + ','
    
     set @detallep = @detallep + ' PERSONA MAYOR DE ' + CONVERT(varchar, @p_mayor) + ' AÑOS'
    end
  end 
 end


 --> Muerto o menor de 18 años siempre rechazo (ahora solo busca si está fallecido)
 --> buscamos si está personalizado en la tabla ln_dias_alertas (en el campo dias la edad esta en años)
 select @p_puntos_fallecido = puntos
   from ln_dias_alertas where tabla = 'fallecido' and cliente = @cliente
 if (@p_puntos_fallecido is null) set @p_puntos_fallecido = -100  

 if (@fecha_defuncion is not null or @defuncion = 1) 
 begin
   if (@p_puntos_fallecido < @puntaje)
    set @puntaje = @p_puntos_mayor
  
   if  (@p_puntos_fallecido = -100)
    begin
     if @detalle is null 
      set @detalle = 'FORMALES:' 
     else
      set @detalle = @detalle + ','
    
     set @detalle = @detalle + ' PERSONA FALLECIDA'
    end 
    else
    begin 
     if @detallep is null 
      set @detallep = 'FORMALES:' 
     else
      set @detallep = @detallep + ','
    
     set @detallep = @detallep + ' PERSONA FALLECIDA'
    end
  end 
end
else
begin
 declare @fecha_inicio_actividad date = null
 declare @p_dias   int = null 
 declare @p_puntos decimal(5,2) = null 

 --> buscamos si está personalizado en la tabla ln_dias_alertas
 select @p_dias   = dias, 
     @p_puntos = puntos
   from ln_dias_alertas where tabla = 'antiguedad' and cliente = @cliente
 if (@p_dias is null) set @p_dias = 720      -- default 720 dias, 2 años
 if (@p_puntos is null) set @p_puntos = -100 -- default rechazo por antiguedad menor a dos años 
 /* antes
  -- fecha de inicio actividad toma la menor fecha de los impuestos 
  select @fecha_inicio_actividad = convert(varchar(50),min(fecha_inscripcion))
  from padron_impuestos
  where id_padron = (select id_padron from padrones where identificacion = @cuit and habilitado = 1) 
  and fecha_inscripcion != '1901-01-01'
  */
 /* 9/8/2023: Pidieron tomar la fecha de contrato social para Formales (fecha de inscripion AFIP se tomara en Fiscales) */
 select @fecha_inicio_actividad = convert(varchar(50),fecha_contrato_social)
   from datos_sociedades
   where cuit = @cuit
   and fecha_contrato_social != '1901-01-01'
 declare @antiguedad_dias int = null
 set @antiguedad_dias = DATEDIFF ( dd , @fecha_inicio_actividad , getdate()) 

 if (@antiguedad_dias is not null and @antiguedad_dias < @p_dias) 
 begin
   if (@p_puntos < @puntaje)
    set @puntaje = @p_puntos

   if  (@p_puntos = -100)
   begin
    if @detalle is null 
     set @detalle = 'FORMALES:' 
    else
     set @detalle = @detalle + ','
    
    set @detalle = @detalle + '  ANTIGÜEDAD MENOR A ' + convert (varchar,(@p_dias / 360)) + ' AÑOS'
   end 
   else
   begin
    if @detallep is null 
     set @detallep = 'FORMALES:' 
    else
     set @detallep = @detallep + ','
    
    set @detallep = @detallep + '  ANTIGÜEDAD MENOR A ' + convert (varchar,(@p_dias / 360)) + ' AÑOS'
   end

  
 end
end

if  (@tiene_indicador != 0)
begin
-- Detalle Killers 

 if exists (select * From ln_score_detalle_killer
             where contraparte = @contraparte 
      and gestion = @gestion 
      and indicador = @indicador
      and detalle_killer != isnull(@detalle,'')
      and visible = 1)
  UPDATE LN_score_detalle_killer
     set visible = 0, modified = getdate()
   where contraparte = @contraparte 
     and gestion = @gestion 
     and indicador = @indicador

 if (@detalle is not null) 
 begin
  if not exists (select * From ln_score_detalle_killer
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @indicador
       and detalle_killer = isnull(@detalle,'')
       and visible = 1)
     insert into ln_score_detalle_killer
     (contraparte, gestion, indicador, detalle_killer, visible, created)
     values (@contraparte, @gestion, @indicador, @detalle, 1, getdate()) 
 end 

 if (@detallep is not null) 
   set @detallep = isnull(@etiqueta, '') + ': ' + @detallep
  
  if exists (select * From ln_score_detalle_puntos
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @indicador
       and detalle != isnull(@detallep,'')
       and visible = 1)
   UPDATE LN_score_detalle_puntos
      set visible = 0, modified = getdate()
    where contraparte = @contraparte 
      and gestion = @gestion 
      and indicador = @indicador

  if (@detallep is not null) 
  begin
   if not exists (select * From ln_score_detalle_puntos
      where contraparte = @contraparte 
        and gestion = @gestion 
        and indicador = @indicador
        and detalle = isnull(@detallep,'')
        and visible = 1)
      insert into ln_score_detalle_puntos
      (contraparte, gestion, indicador, detalle, visible, created)
      values (@contraparte, @gestion, @indicador, @detallep, 1, getdate()) 
  end 

end 
 set @score_formal =  @puntaje

END

GO