dbo.LN_score_alertas_new

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <19/03/2019>
-- Description: Cálculo de Score de alertas
-- =============================================
DECLARE @score_alertas decimal(10,2),
@score_cendeu  decimal(10,2),
@score_ch      decimal(10,2)
EXEC [dbo].[LN_score_alertas_new] 122, 107, @score_alertas output, @score_cendeu output, @score_ch output 
select @score_alertas as score_alertas, @score_cendeu as score_cendeu, @score_ch as score_cheques
SELECT * FROM ln_contrapartes where contraparte = 107

*/

CREATE OR ALTER PROCEDURE dbo.LN_score_alertas_new 
  @familia int, 
  @contraparte int,
  @score_alertas decimal(10,2) = null output, 
  @score_cendeu  decimal(10,2) = null output, 
  @score_ch      decimal(10,2) = null output
AS
BEGIN
 SET NOCOUNT ON;
 declare @detalle varchar(max) = null
 declare @detalle2 varchar(max) = null

 declare @cliente int = null
 DECLARE @p_5anios smalldatetime = getdate() - 1825
    DECLARE @p_2anios smalldatetime = getdate() - 730
    DECLARE @p_1anio  smalldatetime = getdate() - 365
 declare @cuit varchar(20)
 declare @cheques table (causal int, fecha_rechazo date, fecha_levantamiento date) 

    set @cuit = (select identificacion from padrones where id_padron = (select id_padron from ln_contrapartes where contraparte = @contraparte))
 set @cliente = (select cliente from ln_contrapartes where contraparte = @contraparte)

 declare @ind_cendeu  int = (select indicador from ln_indicadores where clave_indicador = 'SCORE_CENDEU')
 declare @ind_ch      int = (select indicador from ln_indicadores where clave_indicador = 'SCORE_CHEQUES')
 declare @ind_alertas int = (select indicador from ln_indicadores where clave_indicador = 'SCORE_ALERTAS_BCRA')

 select @ind_alertas, @ind_cendeu, @ind_ch

 declare @tiene_cendeu int  = (select COUNT(*) from LN_cliente_indicadores where cliente = @cliente and indicador = @ind_cendeu)
 declare @tiene_ch int      = (select COUNT(*) from LN_cliente_indicadores where cliente = @cliente and indicador = @ind_ch)
 declare @tiene_alertas int = (select COUNT(*) from LN_cliente_indicadores where cliente = @cliente and indicador = @ind_alertas) 

 select @tiene_alertas, @tiene_cendeu, @tiene_ch

 /* Podemos tener definidos montos, y cantidad en la tabla ln_dias_alerta, si hay los tomamos de ahi, sino usamos los defaults */
 declare @p_montodeu  bigint = null 
 declare @p_puntosdeu decimal(5,2) = null 
 select  @p_montodeu = monto, 
      @p_puntosdeu = puntos
         from ln_dias_alertas where tabla = 'cendeu' and cliente = @cliente

 declare @p_montoch  bigint = null 
 declare @p_diasch   int = null 
 declare @p_cantch   int = null 
 declare @p_puntosch decimal(5,2) = null 
 select @p_diasch   = dias, 
     @p_montoch  = monto, 
     @p_cantch   = cantidad,
     @p_puntosch = puntos
     from ln_dias_alertas where tabla = 'cheques' and cliente = @Cliente
 /* */  


if (@tiene_ch > 0 or @tiene_alertas > 0) 
BEGIN

/*----------------------------------------------------------
CHEQUES RECHAZADOS:
----------------------------------------------------------*/
-- Cheques Sin Fondos y cheques por defectos formales:
 insert into @cheques
 select id_causa, fecha_rechazo, fecha_levantamiento 
 from ChequesBCRA 
    WHERE (Cuit_firmante = @cuit)
     AND (id_cheque = (select max(id_cheque) from chequesBCRA b 
                      where (b.cuit_firmante = chequesBCRA.cuit_firmante)
                       and (b.nro_cheque = chequesBCRA.nro_cheque)
                       and (b.visible = 1)))
        AND (id_causa in (1,2))
        AND (visible = 1)
     AND (
       (
        (
            (
                            (fecha_levantamiento IS NULL) 
                            AND (fecha_rechazo >= @p_5anios)
                        ) 
            OR 
            (
                (
                                (fecha_levantamiento >= @p_5anios) 
                                AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 15)
                            ) 
                AND 
                (
                                (
                                    (fecha_pago IS NOT NULL) 
                                    AND (fecha_levantamiento >= (GETDATE() - 2))
                                ) 
                                OR 
                                (
                                    (
                                        (fecha_pago IS NULL) 
                                        AND (fecha_levantamiento >= (GETDATE() - 2))
                                    ) 
                                    AND (fecha_rechazo >= @p_5anios)
                                ) 
                ) 
            ) 
        ) 
        OR 
        (
            (
                            (fecha_levantamiento >= @p_5anios) 
                AND 
                            (
                                ((datediff(day,fecha_rechazo,fecha_levantamiento)) >= 16 ) 
                                AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 90)
                            ) 
            ) 
            AND 
            (
                            (
                                (fecha_pago IS NOT NULL) 
                                AND (fecha_levantamiento >= @p_1anio)
                            )
                OR 
                            (
                                (
                                    (fecha_pago IS NULL) 
                                    AND (fecha_levantamiento >= @p_1anio)
                                ) 
                                AND 
                                (fecha_rechazo >= @p_5anios)
                            )
            ) 
        ) 
    )
    OR 
    (
        (
                        (
                            (fecha_levantamiento >= @p_5anios) 
                            AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) > 90)
                        ) 
                        AND 
                        (fecha_levantamiento >= @p_2anios) 
        ) 
        AND (fecha_rechazo >= @p_5anios) 
    )
   )

-- Cheques Sin Fondos, Sin Levantar
  declare @p_cant_30      int = (select count(*) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo > GETDATE()-30)
  declare @p_cant_30_90   int = (select count(*) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo between GETDATE()-90 and GETDATE()-30)
  declare @p_cant_90_180  int = (select count(*) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo between GETDATE()-180 and GETDATE()-90)
  declare @p_cant_180_360 int = (select count(*) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo between GETDATE()-360 and GETDATE()-180)
  declare @p_total_dias   int = null
  if (@p_diasch is not null) 
  begin
     set @p_total_dias = (select count(*) from @cheques where causal = 2 and fecha_levantamiento is null and fecha_rechazo > GETDATE()-@p_diasch)
  end 
  /*
   select @p_cant_30    
   select @p_cant_30_90   
   select @p_cant_90_180  
   select @p_cant_180_360
   */
  declare @puntaje_30      decimal(5,2) = 20
  declare @puntaje_30_90   decimal(5,2) = 10
  declare @puntaje_90_180  decimal(5,2) = 10 
  declare @puntaje_180_360 decimal(5,2) = 6

 if (@p_diasch is not null) /* Tomamos lo definido en la tabla ln_dias_alerta */
 begin
  set @puntaje_30      = 46
  if (@p_total_dias >= @p_cantch) set @puntaje_30 = @p_puntosch
  set @puntaje_30_90   = 0
  set @puntaje_90_180  = 0 
  set @puntaje_180_360 = 0
  if (@puntaje_30 = -100) 
  begin
   if (@detalle is null) 
    set @detalle = 'CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de ' + convert(varchar,@p_cantch) + ' en los últimos ' + convert(varchar,@p_diasch) + ' días'
    else 
    set @detalle = @detalle + '; CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de ' + convert(varchar,@p_cantch) + ' en los últimos ' + convert(varchar,@p_diasch) + ' días'
  end
 end 
 else  /* Tomamos los defaults */
 begin
  set @puntaje_30 = (case 
        when @p_cant_30 = 0 then 20
        when @p_cant_30 >= 1  and @p_cant_30 <= 3  then 20-20*0.50
        when @p_cant_30 >= 4  and @p_cant_30 <= 9  then 20-20*1
        when @p_cant_30 >= 10 and @p_cant_30 <= 30 then -100
        else -100
       end)
  if (@puntaje_30 = -100) 
  begin
   if (@detalle is null) 
      set @detalle = 'CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 10 en los ultimos 30 días'
      else 
      set @detalle = @detalle + '; CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 10 en los ultimos 30 días'
  end

  set @puntaje_30_90 = (case 
        when @p_cant_30_90 = 0 then 10
        when @p_cant_30_90 >= 1  and @p_cant_30_90 <= 3  then 10-10*0.5
        when @p_cant_30_90 >= 4  and @p_cant_30_90 <= 9  then 10-10*1
        when @p_cant_30_90 >= 10 and @p_cant_30_90 <= 30 then -100
        else -100
       end)
  if (@puntaje_30_90 = -100) 
  begin
   if (@detalle is null) 
      set @detalle = 'CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 10 entre 30 y 90 días'
      else 
      set @detalle = @detalle + '; CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 10 entre 30 y 90 días'
  end
  set @puntaje_90_180 = (case 
        when @p_cant_90_180 = 0 then 10
        when @p_cant_90_180 >= 1  and @p_cant_90_180 <= 3  then 10-10*0.1
        when @p_cant_90_180 >= 4  and @p_cant_90_180 <= 9  then 10-10*0.5
        when @p_cant_90_180 >= 10 and @p_cant_90_180 <= 30 then 10-10*1
        else -100
       end)
  if (@puntaje_90_180 = -100) 
  begin
   if (@detalle is null) 
      set @detalle = 'CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 30 entre 90 y 180 días'
      else 
      set @detalle = @detalle + '; CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 30 entre 90 y 180 días'
  end
  set @puntaje_180_360 = (case 
        when @p_cant_180_360 = 0 then 6
        when @p_cant_180_360 >= 1  and @p_cant_180_360 <= 3  then 6-6*0.25
        when @p_cant_180_360 >= 4  and @p_cant_180_360 <= 9  then 6-6*0.50
        when @p_cant_180_360 >= 10 and @p_cant_180_360 <= 30 then 6-6*1
        else -100
       end)
  if (@puntaje_180_360 = -100) 
  begin
   if (@detalle is null) 
      set @detalle = 'CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 30 entre 180 y 360 días'
      else 
      set @detalle = @detalle + '; CHEQUES RECHAZADOS SIN FONDOS, SIN LEVANTAR: Más de 30 entre 180 y 360 días'
  end

 end 

  declare @score_alerta_cheque_sf decimal(5,2) = 100
  if (@puntaje_30      = -100 or 
   @puntaje_30_90   = -100 or
   @puntaje_90_180  = -100 or
   @puntaje_180_360 = -100) 
   begin
    set @score_alerta_cheque_sf = -100
   end 
   else
   begin
    declare @maximo   decimal(5,2) = (6+10+10+20) 
    declare @obtenido decimal(5,2) = (@puntaje_30 +
              @puntaje_30_90   +
              @puntaje_90_180  +
              @puntaje_180_360) 
    --select @maximo, @obtenido
    --select @puntaje_30, @puntaje_30_90, @puntaje_90_180, @puntaje_180_360
    set @score_alerta_cheque_sf = 100 * @obtenido / @maximo
   end 

-- Cheques Defectos Formales ultimos 180 dias
 
  declare @p_cant_df  int = (select count(*) from @cheques where causal = 1 and fecha_rechazo > GETDATE()-180)
  declare @puntaje_df decimal(5,2) = 100
  
  set @puntaje_df = (case 
        when @p_cant_df = 0 then 100
        when @p_cant_df >= 1 and @p_cant_df <= 4  then 80
        when @p_cant_df >= 5 and @p_cant_df <= 9  then 50
        else -100
       end)
  
  if (@puntaje_df = -100) 
  begin
   if (@detalle is null) 
      set @detalle = 'CHEQUES RECHAZADOS POR DEFECTOS FORMALES: Más de 30 en últimos 180 días'
      else 
      set @detalle = @detalle + '; CHEQUES RECHAZADOS POR DEFECTOS FORMALES: Más de 30 en últimos 180 días'
  end 
  declare @score_alerta_cheque_df decimal(5,2)
  set @score_alerta_cheque_df = @puntaje_df
END 

if (@tiene_cendeu > 0 or @tiene_alertas > 0) 
BEGIN
/*----------------------------------------------------------
CENDEU:
----------------------------------------------------------*/

-- Central deudores: Deudas de los ultimos 180 dias
declare @score_bancos int = 100

  declare @bancos table (fec_inf varchar(6), banco varchar(100), situacion int, deuda numeric(12,2))
  
  insert into @bancos 
         exec obtener_bancos_test @cuit
  declare @dtEnd date = (select max(fecha_informacion) from fuentes_procesadas where visible = '1' and fuente = 'cendeu')
  declare @dtEnd_cuit date = (select convert(date,isnull(max(fec_inf),'200112')+'01') from @bancos)
  if (@dtEnd_cuit > @dtEnd) set @dtEnd = @dtEnd_cuit
  declare @hoy date = dateadd(MM,-5,@dtEnd) -- le resta 5, para tomar los ultimos 6 meses
  
  declare @6meses varchar(6) = (convert(varchar(4),DATEPART(yyyy,@hoy))
          +
          right('0'+convert(varchar(2),DATEPART(mm,@hoy)),2))

  declare @cant_1 int = (select COUNT(distinct banco) from @bancos where cast(fec_inf as int) >= cast(@6meses as int) and situacion = 1)
  declare @cant_2 int = (select COUNT(distinct banco) from @bancos where cast(fec_inf as int) >= cast(@6meses as int) and situacion = 2)
  declare @cant_3 int = 0, @monto_3 numeric(18,2) = 0
  select @cant_3 = COUNT(distinct banco) from @bancos where cast(fec_inf as int) >= cast(@6meses as int) and  situacion >= 3
  set @monto_3 = (select sum(sbr.total*1000) from (select banco, max(deuda) as total from @bancos a where cast(a.fec_inf as int) >= cast(@6meses as int) and a.situacion >= 3 group by banco) sbr)
  
  --select @6meses, @cant_1, @cant_2, @cant_3, @monto_3
  if (@p_montodeu is not null) /* Tomamos lo que esta definido en la tabla ln_dias_alertas */
  begin 
   if (@cant_3 > 0)
   begin
     if (@monto_3 >  @p_montodeu) set @score_bancos = @p_puntosdeu 
    
     if (@monto_3 <= @p_montodeu) set @score_bancos = 80
   end 
   else
   begin
    if (@cant_2 > 0) set @score_bancos = 80
    else set @score_bancos = 100
   end
  end

  else
   
  begin   /* Tomamos valores defaults */
   if (@cant_3 > 0)
   begin
     if (@monto_3 >  10000) set @score_bancos = -100
     if (@monto_3 <= 10000) set @score_bancos = 80
   end 
   else
   begin
    if (@cant_2 > 0) set @score_bancos = 80
    else set @score_bancos = 100
   end
  end 
  
  if (@score_bancos = -100) 
  begin
    if (@detalle2 is null) 
      set @detalle2 = 'CENTRAL DE DEUDORES: Últimos 6 meses, Situación 3, 4 o 5, Monto mayor a $ 10.000'
      else 
      set @detalle2 = @detalle2+ '; CENTRAL DE DEUDORES: Últimos 6 meses, Situación 3, 4 o 5, Monto mayor a $ 10.000'
  end 
END 

-- Detalle Killers 
 
 declare @gestion int = (select gestion 
       from LN_gestiones 
       where contraparte = @contraparte 
         AND LN_gestiones.desde <= GETDATE()
       AND LN_gestiones.hasta >= GETDATE())

         
 if (@tiene_cendeu > 0)  
 BEGIN                
  if exists (select * From ln_score_detalle_killer
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @ind_cendeu
       and detalle_killer != isnull(@detalle2,'')
       and visible = 1)
   UPDATE LN_score_detalle_killer
      set visible = 0, modified = getdate()
    where contraparte = @contraparte 
      and gestion = @gestion 
      and indicador = @ind_cendeu
 
  if (@detalle2 is not null) 
  begin
   if not exists (select * From ln_score_detalle_killer
      where contraparte = @contraparte 
        and gestion = @gestion 
        and indicador = @ind_ch
        and detalle_killer = isnull(@detalle2,'')
        and visible = 1)

      insert into ln_score_detalle_killer
      (contraparte, gestion, indicador, detalle_killer, visible, created)
      values (@contraparte, @gestion, @ind_cendeu, @detalle2, 1, getdate()) 
  end 
 END 

 if (@tiene_ch > 0)  
 BEGIN                
  if exists (select * From ln_score_detalle_killer
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @ind_ch
       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 = @ind_ch
 
  if (@detalle is not null) 
  begin
   if not exists (select * From ln_score_detalle_killer
      where contraparte = @contraparte 
        and gestion = @gestion 
        and indicador = @ind_ch
        and detalle_killer = isnull(@detalle,'')
        and visible = 1)

      insert into ln_score_detalle_killer
      (contraparte, gestion, indicador, detalle_killer, visible, created)
      values (@contraparte, @gestion, @ind_ch, @detalle, 1, getdate()) 
  end 
 END

 if (@tiene_alertas > 0) 
  BEGIN    
  if (@detalle is null) 
  begin
  set @detalle = ISNULL(@detalle2,'') 
  end
  else
  begin  
   if (@detalle2 is not null)
   begin
   set @detalle = @detalle + '; ' + @detalle2
   end 
  end 

  if exists (select * From ln_score_detalle_killer
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @ind_alertas
       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 = @ind_alertas
 
  if (@detalle is not null) 
  begin
   if not exists (select * From ln_score_detalle_killer
      where contraparte = @contraparte 
        and gestion = @gestion 
        and indicador = @ind_ch
        and detalle_killer = isnull(@detalle,'')
        and visible = 1)

      insert into ln_score_detalle_killer
      (contraparte, gestion, indicador, detalle_killer, visible, created)
      values (@contraparte, @gestion, @ind_alertas, @detalle, 1, getdate()) 
  end 
 END



-- Devuelve el Scores
 if (@tiene_alertas > 0) 
 BEGIN
  if (@score_alerta_cheque_df = -100 or @score_alerta_cheque_sf = -100 or @score_bancos = -100) 
      begin
   set @score_alertas = -100
   end 
   else 
   begin
   -- En la formula general de Score: 
   -- Cheques sin fondos        pesa 46.29 --> 72.12 % del puntaje maximo 
   -- Cheques defectos formales pesa 0.13  --> 0.2 % del puntaje maximo
   -- Deudas                    pesa 17.76 --> 27.68 % del puntaje maximo 
   set @score_alertas = @score_alerta_cheque_sf * 72.12/100 +
         @score_alerta_cheque_df * 0.2/100 +
         @score_bancos * 27.68/100

   end
 END 
 if (@tiene_cendeu > 0) 
 BEGIN
  set @score_cendeu = @score_bancos
 END 
 if (@tiene_ch > 0) 
 BEGIN
  if (@score_alerta_cheque_df = -100 or @score_alerta_cheque_sf = -100) 
      begin
    set @score_ch = -100
   end 
   else 
   begin
    set @score_ch = @score_alerta_cheque_sf * 72.12/72.32 +
        @score_alerta_cheque_df * 0.2/72.32

   end
 END 
END

GO