dbo.LN_scores_ARCOR

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <08/11/2022>
-- Description: Cálculo de Scores de ARCOR
-- IMPORTANTE: Cuando se llama con la accion 'ARCOR_FINAL' 
--             se calculan todas las demas para luego guardar el resultado final en el indicador ARCOR_FINAL
--             por lo cual, con esa accion por cada contraparte sera suficiente, 
--             en el queue solo vamos a llamarlo con el indicador ARCOR_FINAL
--             y en el queue lo llamamos ultimo, para que ya se hayan calculado 
--             la de Apocrifas/Fidecheck/Aportes y Contribuciones que se necesitan
-- =============================================
*/ 
/*
exec [LN_scores_ARCOR] 'ARCOR_final',194098

select * from ln_contrapartes where contraparte = 196342
select max(monto) from  LN_dias_alertas where cliente = 861672084 and tabla = 'cendeu'
*/
CREATE OR ALTER PROCEDURE dbo.LN_scores_ARCOR 
  @accion      varchar(100), 
  @contraparte int, 
  @p_user_id   int = 94437923 
AS
BEGIN
 SET NOCOUNT ON;
 
 declare @score_datos decimal(10,2) = null 
 declare @score_memo varchar(100) = null

 declare @cliente int = null 
 declare @gestion int = null 
 declare @familia int = null 
 declare @pais_contraparte varchar(10) = null 


 declare @indic_accion int = null 
 declare @nombre_indic_accion varchar(500) = null 

 declare @valor_texto varchar(100) = null 
 declare @valor_numerico decimal(12,2) = null 
 declare @valor_memo varchar(100) = null
 
 declare @valor_texto_ant varchar(100) = null 
 declare @valor_numerico_ant decimal(12,2) = null 
 declare @valor_memo_ant varchar(100) = null

 declare @trazabilidad varchar(max) = null 
 declare @trazabilidad2 varchar(max) = null 

 declare @cuit varchar(20)

 declare @producto int = null 
 declare @indicador int = null 
 declare @valor varchar(100) = null 

    declare @nombre_indicador varchar(100) = null -- de los indicadores a tener en cuenta
 declare @clave_indicador varchar(100) = null 


 declare @detallep varchar(max) = null

/* ------------------------------------------------------------------- */
/* Acciones generales */ 
 select @cliente = ln_contrapartes.cliente, 
     @gestion = LN_gestiones.gestion, 
     @familia = ln_contraparte_familias.familia, 
     @pais_contraparte = ln_contrapartes.country_code, 
     @cuit = identificacion 
   from ln_contrapartes
  inner join padrones on padrones.id_padron = ln_contrapartes.id_padron 
  inner join LN_gestiones on LN_gestiones.contraparte = LN_contrapartes.contraparte
  inner join ln_contraparte_familias on ln_contraparte_familias.contraparte = LN_contrapartes.contraparte

   where ln_contrapartes.contraparte = @contraparte 
    AND LN_gestiones.desde <= GETDATE()
    AND LN_gestiones.hasta >= GETDATE()
    AND LN_gestiones.vigente = 1

 if (@gestion is null or @cliente is null) 
 begin
  select 'Sin gestion o cliente'
  return
 end 

 --> Para ver si la contraparte tiene el indicador activo
 set @indic_accion = null 
 set @nombre_indic_accion = null 
 SELECT @indic_accion = LN_indicadores.indicador, @nombre_indic_accion = LN_indicadores.nombre_indicador 
  FROM LN_indicadores 
  INNER JOIN LN_productos ON LN_indicadores.producto = LN_productos.producto
  INNER JOIN LN_familia_productos ON LN_productos.producto = LN_familia_productos.producto
  INNER JOIN LN_contraparte_familias ON LN_familia_productos.familia = LN_contraparte_familias.familia
  INNER JOIN LN_gestiones ON LN_contraparte_familias.contraparte = LN_gestiones.contraparte
  INNER JOIN LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
  INNER JOIN LN_cliente_indicadores ON (LN_cliente_indicadores.indicador = LN_indicadores.indicador and LN_cliente_indicadores.cliente = LN_contrapartes.cliente )
 WHERE LN_indicadores.clave_indicador = @accion
  AND LN_contraparte_familias.contraparte = @contraparte
  AND LN_productos.visible = 1
  --AND LN_indicadores.visible = 1 -- Estos tienen visible = 0, porque no se ven en el panel, por eso le saco esta condicion
  AND LN_contraparte_familias.activo = 1
  AND LN_cliente_indicadores.activo = 1
  AND LN_familia_productos.activo = 1
  AND LN_gestiones.desde <= getdate()
  AND LN_gestiones.hasta >= getdate()
  AND LN_gestiones.vigente = 1 

 if (@indic_accion is null) 
 begin
  select @contraparte, 'Indicador no se calcula'
  return
 end

 /* */ 

 select  @valor_numerico_ant = valor_numerico, 
   @valor_texto_ant = valor_texto, 
   @valor_memo_ant = convert(varchar(100),valor_memo)
  from LN_contraparte_indicadores
  where contraparte = @contraparte
  and gestion = @gestion 
  and visible = 1
  and indicador = @indic_accion 
/* ------------------------------------------------------------------- */

/* Acciones */ 
 --declare @tiene_indicador int  = (select COUNT(*) from LN_cliente_indicadores where cliente = @cliente and indicador = @indic_accion)
 --lo hace arriba y sale por @indic_accion is null ('Indicador no se calcula')

 IF (@accion = 'ARCOR_INFORME')  

 BEGIN

  set @valor_texto = null 
  set @detallep = null 
  
  declare @sc_informe int = null 
  select @sc_informe = valor_numerico 
   from ln_contraparte_indicadores 
   inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
   where contraparte = @contraparte 
   and gestion = @gestion 
   and clave_indicador = 'SCORE_IC'
   and ln_contraparte_indicadores.visible = 1

  if (@sc_informe < 40)
  begin
   set @valor_texto = 'R'
   set @detallep = 'SCORE INFORME COMERCIAL: menor a 40'  
  end 
  if (@sc_informe >= 40 and @sc_informe < 70)
  begin
   set @valor_texto = 'A'
   set @detallep = 'SCORE INFORME COMERCIAL: entre 40 y 70' 
  end 
  if (@sc_informe >= 70)
  begin
   set @valor_texto = 'V'
   set @detallep = 'SCORE INFORME COMERCIAL: mayor a 70' 
  end
   
 END



 IF (@accion = 'ARCOR_BCRA')   /*
  SIT BANCARIA
  Sit 1 - Verde
  Sit 2 - Amarillo
  Sit 3 o peor - Rojo
  */

 BEGIN

  set @valor_texto = null 
  set @detallep = null 
  if (@pais_contraparte = 'AR')
  begin
   declare @peor int = null 
   declare @mes_bcra varchar(6) = null 
   declare @monto int = null 
   declare @monto_bcra bigint = 0
     
   select @peor = valor_numerico, @mes_bcra = valor_texto
     from ln_contraparte_indicadores 
     inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    where contraparte = @contraparte 
     and gestion = @gestion 
     and clave_indicador = 'SITUACION_CD'
     and ln_contraparte_indicadores.visible = 1

   -- Si en la tabla dias_alerta tienen considerado montos (x ej Sanofi) 
   select @monto = max(monto) from LN_dias_alertas where cliente = @cliente and tabla = 'cendeu'
   if (@monto is not null)  -- hay que tener en cuenta el monto 
   begin 
    declare @bancos table (fec_inf varchar(6), banco varchar(100), situacion int, deuda numeric(12,2))
    insert into @bancos 
        exec obtener_bancos_test @cuit
    select @monto_bcra = SUM(deuda) from @bancos where fec_inf = @mes_bcra and situacion = @peor
    set @monto_bcra = @monto_bcra * 1000 -- porque el dato viene en miles 
   end 

   if (@monto is null) 
   begin
    if (@peor >= 3)
    begin
     set @valor_texto = 'R'
     set @detallep = 'SIT BANCARIA: Sit 3 o peor'
    end 
    if (@peor = 2)
    begin
     set @valor_texto = 'A'
     set @detallep = 'SIT BANCARIA: Sit 2'
    end 
    if (@peor = 1)
    begin
     set @valor_texto = 'V'
     set @detallep = 'SIT BANCARIA: Sit 1'
    end
   end 
   else 
   begin 
    if (@peor >= 3 and @monto_bcra >= @monto)
    begin
     set @valor_texto = 'R'
     set @detallep = 'SIT BANCARIA: Sit 3 o peor y monto mayor a $' + format(@monto, '###,###,##0.00') 
    end 
    if (@peor = 2  and @monto_bcra >= @monto)
    begin
     set @valor_texto = 'A'
     set @detallep = 'SIT BANCARIA: Sit 2 y monto mayor a $' + format(@monto, '###,###,##0.00') 
    end 
    if (@peor = 1)
    begin
     set @valor_texto = 'V'
     set @detallep = 'SIT BANCARIA: Sit 1'
    end
    else 
    begin
     if (@peor > 1 and @monto_bcra < @monto)
     begin
      set @valor_texto = 'V'
      set @detallep = 'SIT BANCARIA: Sit ' + format(@peor,'#0') + ' monto < $ ' +  format(@monto, '###,###,##0.00') 
     end
    end 

   end 
     end  
 END

 IF (@accion = 'ARCOR_CHEQUES')  /*
  CHEQUES SF
  Sin cheques - Verde
  1 a 3 cheques en el último año - Amarillo
  Más de 3 cheques en el último año - Rojo
  */

 BEGIN
  set @valor_texto = null 
  set @detallep = null 
  if (@pais_contraparte = 'AR')
  begin
   declare @total_cheques int = 0 
   /*
    select @total_cheques  = count(*)
     from ChequesBCRA
      where cuit_firmante = @cuit and Cuit_relacionado is null 
    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 visible = 1
    AND fecha_levantamiento is null 
    AND fecha_rechazo >= getdate() - 365
    AND ChequesBCRA.id_causa = 2
    */
   /* No lo calculamos, lo tomamos del indicador */

   select @total_cheques = valor_numerico 
     from ln_contraparte_indicadores 
     inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    where contraparte = @contraparte 
     and gestion = @gestion 
     and clave_indicador = 'CHEQUES_RECH'
     and ln_contraparte_indicadores.visible = 1
      
   if (@total_cheques = 0) 
   begin
    set @valor_texto = 'V'
    set @detallep = 'CHEQUES SF: Sin cheques'
   end
   if (@total_cheques > 3) 
   begin
    set @valor_texto = 'R'
    set @detallep = 'CHEQUES SF: Más de 3 cheques en el último año'
   end
   if (@total_cheques > 0 and @total_cheques <= 3) 
   begin
    set @valor_texto = 'A'
    set @detallep = 'CHEQUES SF: 1 a 3 cheques en el último año'
   end 
  end
 END 

 IF (@accion = 'ARCOR_JUDICIAL')  /*
  SIT JUDICIAL
  Evento menor a 2 años - Rojo
  Evento mayor a 2 años - Amarillo
  Sin Eventos - Verde
  */

 BEGIN
  set @valor_texto = null 
  set @detallep = null 
  if (@pais_contraparte = 'AR')

  begin
   if (@cliente != 861672084) -- No es arcor 
   begin
    declare @score_judicial int = null 
    select @score_judicial = valor_numerico 
       from ln_contraparte_indicadores 
       inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
      where contraparte = @contraparte 
       and gestion = @gestion 
       and clave_indicador = 'SCORE_JUDICIAL'
       and ln_contraparte_indicadores.visible = 1
    if (@score_judicial is null) set @score_judicial = 100 
    if (@score_judicial >= 61) 
    begin
      set @valor_texto = 'V'
      set @detallep = 'SIT JUDICIAL: Score Judicial > 60'
    end
    if (@score_judicial >= 41 and @score_judicial < 61) 
    begin
      set @valor_texto = 'A'
      set @detallep = 'SIT JUDICIAL: Score Judicial entre 40 y 60'
    end 
    if (@score_judicial >= 0 and @score_judicial < 41) 
    begin
      set @valor_texto = 'R'
      set @detallep = 'SIT JUDICIAL: Score Judicial menor a 40'
    end
   end 

   else 

   begin -- Para ARCOR 

    declare @fecha_juicios date = null 
    select @fecha_juicios = max(fecha_causa) from juicios 
          inner join tipos_causas on
              tipos_causas.id_tipo_causa = juicios.id_tipo_causa
          left join tiempo_causas on
              tiempo_causas.cau_codigo   = juicios.id_tipo_causa
          left join padrones AS pf ON (pf.identificacion = cuit_actor) 
          WHERE cuit_demandado = @cuit
          and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
          and juicios.visible = 1
    if (@fecha_juicios is null) 
    begin
     set @valor_texto = 'V'
     set @detallep = 'SIT JUDICIAL: Sin Eventos'

    end 
    if (@fecha_juicios >= getdate() - (365*2)) 
    begin
     set @valor_texto = 'R'
     set @detallep = 'SIT JUDICIAL: Evento menor a 2 años'
    end 
    if (@fecha_juicios < getdate() - (365*2)) 
    begin
     set @valor_texto = 'A'
     set @detallep = 'SIT JUDICIAL: Evento mayor a 2 años'
    end
   end
  end

 END 

 IF (@accion = 'ARCOR_AYC')  /* El peor de los indicadores de Deudas Previsionales 
  -- DP_301 (Aportes Exigibles s/ AFIP) y
  -- DP_351 (Contribuciones Exigibles s/ AFIP)
  */

 BEGIN
  set @valor_texto = null 
  set @detallep = null 
  if (@pais_contraparte = 'AR')

  begin
   declare @aportes        int = null 
   declare @contribuciones int = null 

   select @aportes = valor_numerico
     from ln_contraparte_indicadores 
     inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    where contraparte = @contraparte 
     and gestion = @gestion 
     and clave_indicador = 'DP_301'
     and ln_contraparte_indicadores.visible = 1
   select @contribuciones = valor_numerico
     from ln_contraparte_indicadores 
     inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    where contraparte = @contraparte 
     and gestion = @gestion 
     and clave_indicador = 'DP_351'
     and ln_contraparte_indicadores.visible = 1


   if (@aportes = 1 or @contribuciones = 1) 
   begin
    set @valor_texto = 'R' 
    set @detallep = 'APORTES Y CONTRIBUCIONES: Registra deuda'
   end
   if (@aportes = 0 and @contribuciones = 0) 
   begin
    set @valor_texto = 'V' 
    set @detallep = 'APORTES Y CONTRIBUCIONES: No registra deuda'
   end
  end 
 END 

 IF (@accion = 'ARCOR_APOCRIFAS')  /* Refleja el color del indicador APOCRIFAS */

 BEGIN
  declare @apocrifas int = null 
  set @valor_texto = null 
  set @detallep = null 
  if (@pais_contraparte = 'AR')
  begin
   select @apocrifas = valor_numerico 
     from ln_contraparte_indicadores 
     inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    where contraparte = @contraparte 
     and gestion = @gestion 
     and clave_indicador = 'APOCRIFAS'
     and ln_contraparte_indicadores.visible = 1
   
   if (@apocrifas = 1)
   begin
    set @valor_texto = 'R'
    set @detallep = 'FACTURAS APÓCRIFAS: Posee'
   end 
   if (@apocrifas = 0)
   begin
    set @valor_texto = 'V'
    set @detallep = 'FACTURAS APÓCRIFAS: No Posee'
   end
  end 
 END 

 IF (@accion = 'ARCOR_FIDECHECK')  /* Reflaje color del indicador FIDECHECK */

 BEGIN
  set @valor_texto = null 
  set @detallep = null 
 
   select @valor_texto = valor_texto 
     from ln_contraparte_indicadores 
     inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
    where contraparte = @contraparte 
     and gestion = @gestion 
     and clave_indicador = 'FIDECHECK'
     and ln_contraparte_indicadores.visible = 1

   set @detallep = CASE @valor_texto
       WHEN 'P' THEN 'FIDECHECK: Positivo'
       WHEN 'N' THEN 'FIDECHECK: Negativo'
       WHEN 'S' THEN 'FIDECHECK: Sujeto Obligado'
       WHEN 'E' THEN 'FIDECHECK: PEP'
       WHEN 'R' THEN 'FIDECHECK: Relacionado'
       else NULL 
      END 
   set @valor_texto = CASE @valor_texto
         WHEN 'P' THEN 'R'
         WHEN 'N' THEN 'V'
         WHEN 'S' THEN 'V'
         WHEN 'E' THEN 'A'
         WHEN 'R' THEN 'A'
         else NULL 
        END 
  

 
 END 
 
 IF (@accion = 'ARCOR_FINAL')  /* El peor de todos los Scores anteriores */
 BEGIN
  set @valor_texto = null 
  set @detallep = null 

  EXEC [LN_scores_ARCOR] 'ARCOR_BCRA', @contraparte, @p_user_id
  EXEC [LN_scores_ARCOR] 'ARCOR_CHEQUES', @contraparte, @p_user_id
  EXEC [LN_scores_ARCOR] 'ARCOR_JUDICIAL', @contraparte, @p_user_id
  EXEC [LN_scores_ARCOR] 'ARCOR_AYC', @contraparte, @p_user_id
  EXEC [LN_scores_ARCOR] 'ARCOR_APOCRIFAS', @contraparte, @p_user_id
  EXEC [LN_scores_ARCOR] 'ARCOR_FIDECHECK', @contraparte, @p_user_id
  EXEC [LN_scores_ARCOR] 'ARCOR_INFORME', @contraparte, @p_user_id



  if exists (select *
       from ln_contraparte_indicadores 
       inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
      where contraparte = @contraparte 
       and gestion = @gestion 
       and clave_indicador like 'ARCOR_%' and clave_indicador != 'ARCOR_FINAL'
       and valor_texto = 'R' 
       and ln_contraparte_indicadores.visible = 1) 
  begin 
   set @valor_texto = 'R'
  end 
  else 
  begin
   if exists (select *
        from ln_contraparte_indicadores 
        inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
       where contraparte = @contraparte 
        and gestion = @gestion 
        and clave_indicador like 'ARCOR_%' and clave_indicador != 'ARCOR_FINAL'
        and valor_texto = 'A' 
        and ln_contraparte_indicadores.visible = 1) 
   begin 
    set @valor_texto = 'A'
   end 
   else 
   begin
    if exists (select *
         from ln_contraparte_indicadores 
         inner join LN_indicadores on LN_indicadores.indicador = LN_contraparte_indicadores.indicador
        where contraparte = @contraparte 
         and gestion = @gestion 
         and clave_indicador like 'ARCOR_%' and clave_indicador != 'ARCOR_FINAL'
         and valor_texto = 'V' 
         and ln_contraparte_indicadores.visible = 1) 
    begin 
     set @valor_texto = 'V'
    end 
   end
  end
  
  
 END 

 set @valor_numerico = null 
 if (@valor_texto = 'R') set @valor_numerico = 10
 if (@valor_texto = 'A') set @valor_numerico = 50
 if (@valor_texto = 'V') set @valor_numerico = 100

 if not exists (select contraparte, gestion, indicador
      from LN_contraparte_indicadores
      where contraparte = @contraparte
      and gestion = @gestion 
      and visible = 1
      and indicador = @indic_accion)
  INSERT INTO LN_contraparte_indicadores
  (contraparte, gestion, indicador, valor_texto, valor_numerico, created, created_by,familia)
  VALUES
  (@contraparte, @gestion, @indic_accion, @valor_texto, @valor_numerico, GETDATE(), @p_user_id,@familia)
 else
  UPDATE LN_contraparte_indicadores SET
    valor_texto = @valor_texto,
    valor_numerico = @valor_numerico,
    familia = @familia, 
    modified = GETDATE(),
    modified_by = @p_user_id
  WHERE contraparte = @contraparte
   AND gestion = @gestion
   AND indicador = @indic_accion
   and visible = 1

 /* Guarda los detalles del por que de los puntos asignados */
 if exists (select * From ln_score_detalle_puntos
             where contraparte = @contraparte 
      and gestion = @gestion 
      and indicador = @indic_accion
      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 = @indic_accion

 if (@detallep is not null and @valor_texto != 'V') --> Para que no guarde detalle cuando es verde
 --if (@detallep is not null) 

 begin
  if not exists (select * From ln_score_detalle_puntos
     where contraparte = @contraparte 
       and gestion = @gestion 
       and indicador = @indic_accion
       and detalle = isnull(@detallep,'')
       and visible = 1)

     insert into ln_score_detalle_puntos
     (contraparte, gestion, indicador, detalle, visible, created)
     values (@contraparte, @gestion, @indic_accion, @detallep, 1, getdate()) 
 end 

 /* Si cambió el valor del indicador, guarda trazabilidad  */ 


 
 if (isnull(@valor_texto_ant,'') != isnull(@valor_texto,''))
 begin 
  /* guarda trazabilidad del indicador calculado */
  set @trazabilidad = 'Actualizado automáticamente: ' + @nombre_indic_accion + ': ' + isnull(@valor_texto,'-')
  set @trazabilidad2 = 'CAMBIO_INDICADOR_'+convert(varchar,@indic_accion)
  exec insertar_trazabilidad  @trazabilidad, @cliente, @trazabilidad2,'WWW', @contraparte,1

 end


 --select @valor_texto  
 select @valor_texto_ant as valor_texto_ant, @valor_texto as valor_texto, @indic_accion as indicador

END

GO