dbo.SAAS_Consulta_CUIT_new

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
-- =============================================
/*
select * from ln_contrapartes where contraparte = 45069
select * from ln_contraparte_familias where contraparte = 45069
select * from ln_contraparte_indicadores where contraparte = 45069


select * from padrones where id_padron = 2336530
delete from ln_contraparte_indicadores where contraparte = 775
select distinct contraparte from SAAS_Historial_Consultas where contraparte in 
(
select contraparte from ln_contrapartes where cliente = 515494256 
)

 where contraparte = 775

exec [SAAS_Consulta_CUIT_new] 94437923,1,'30556565798',0,147,100

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_new 
 @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
 select @cliente
 declare @gestion int = (select gestion 
       from LN_gestiones 
       where contraparte = @contraparte 
         AND LN_gestiones.desde <= GETDATE()
       AND LN_gestiones.hasta >= GETDATE())
 -- Black List
 declare @BL_estado char(1) = 'V'
 declare @BL_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionBlackList')
 
 exec LN_dashboard_cliente 'AprobacionBlackList', @cliente, null, null, @contraparte
 select @BL_score = valor_numerico, @BL_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

 -- Fiscales
 declare @fiscales_estado char(1) = 'V'
 declare @fiscales_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionFiscales')
 
 exec LN_dashboard_cliente 'AprobacionFiscales', @cliente, null, null, @contraparte
 select @fiscales_score = valor_numerico, @fiscales_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

 -- Formales
 declare @formales_estado char(1) = 'V'
 declare @formales_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionFormales')
 
 exec LN_dashboard_cliente 'AprobacionFormales', @cliente, null, null, @contraparte
 select @formales_score = valor_numerico, @formales_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

    
 -- Legales
 declare @legales_estado char(1) = 'V'
 declare @legales_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionLegales')
 
 exec LN_dashboard_cliente 'AprobacionLegales', @cliente, null, null, @contraparte
 select @legales_score = valor_numerico, @legales_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

 -- Bancos
 declare @bancos_estado char(1) = 'V'
 declare @bancos_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionBancos')
 
 exec LN_dashboard_cliente 'AprobacionBancos', @cliente, null, null, @contraparte
 select @bancos_score = valor_numerico, @bancos_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

 -- Cheques
 declare @cheques_estado char(1) = 'V'
 declare @cheques_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionCheques')
 
 exec LN_dashboard_cliente 'AprobacionCheques', @cliente, null, null, @contraparte
 select @cheques_score = valor_numerico, @cheques_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

 -- Score Balance --> Se calcula en php y se manda como parametro
 declare @contables_estado char(1) = 'V'
 declare @contables_score  decimal(5,2) = @p_score_balance
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionContables')
 
 EXEC LN_dashboard_cliente 'AprobacionContables', @cliente, null, null, @contraparte, null, @p_score_balance

 select @contables_score = valor_numerico, @contables_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1
 
 -- Final 
 declare @final_estado char(1) = 'V'
 declare @final_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionFinal')
 
 exec LN_dashboard_cliente 'AprobacionFinal', @cliente, null, null, @contraparte
 select @final_score = valor_numerico, @final_estado = valor_texto
   from ln_contraparte_indicadores 
  where contraparte = @contraparte 
    and gestion = @gestion 
    and indicador = @indicador 
    and visible = 1

 
 --> Black List ROJO -> manda todos los colores a Rojo
 if ( @BL_estado = 'R') 
 begin
  set @fiscales_estado  = 'R' 
  set @Contables_estado = 'R'  
  set @Legales_estado   = 'R'  
  set @Bancos_estado    = 'R' 
  set @Cheques_estado   = 'R'
  set @final_estado     = 'R' -- Rojo 
 end 

 

 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(), 
                                       @final_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    

GO