dbo.SAAS_Consulta_CUIT

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,'27208925658',0,147,100

select * from SAAS_Historial_Consultas where saas_historial_consulta =7271
select * from 

exec LN_dashboard_cliente 'AprobacionBlackList', 515494256, null, null, 160338

select * from ln_contraparte_indicadores where contraparte = 160338

select * from ln_contrapartes where contraparte = 160338
select * from clientes where cliente = 515494256


select contraparte, gestion, valor_texto as valor
  from LN_contraparte_indicadores 
  inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
  where clave_indicador = 'AprobacionBlackList'
  and contraparte = 160338



*/

CREATE OR ALTER PROCEDURE dbo.SAAS_Consulta_CUIT 
 @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
/*
select * from padrones where identificacion='33711511569'
select * from ln_contrapartes where id_padron=46947759
select * from clientes where cliente in (515494256,1811919445) 

--insert into LN_cliente_nivel (cliente,desde,hasta,texto,texto_largo,titulo,color_fondo,color_texto,indicador)
--select 1811919445 as cliente,desde,hasta,texto,texto_largo,titulo,color_fondo,color_texto,indicador from LN_cliente_nivel where cliente in (515494256,1811919445)  and indicador=250
*/


 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 

 --> En Monitoreo 
 -- 6 Fiscales, 9 Legales, 10 Bancos, 18 Cheques 
 --> Se pueden agregar más secciones, agregando en @lista
 declare @seccion int 
 declare @lista varchar(max) = '6,9,10,18'

 DECLARE secciones CURSOR FOR
   select item from dbo.DelimitedSplit8K(@lista, ',') 
 OPEN secciones
 FETCH NEXT FROM secciones INTO @seccion
 WHILE @@fetch_status = 0
 BEGIN
   if not exists (select * from monitoreos where cliente = @cliente and cuit = @p_cuit and visible = 1 and seccion_mantenimiento = @seccion)
   begin 
    insert into monitoreos (cliente, user_id,   seccion_mantenimiento,fecha_vigencia_mantenimiento,visible,cuit, fecha_desde) 
        values (@cliente,@p_user_id,@seccion,             getdate()+365               ,'1',    @p_cuit,getdate())
   end 
   else 
   begin 
    update monitoreos set fecha_vigencia_mantenimiento = getdate()+365
     where cliente = @cliente 
       and cuit    = @p_cuit
       and seccion_mantenimiento = @seccion
   end 
   FETCH NEXT FROM secciones INTO @seccion
 END 
 CLOSE secciones
 DEALLOCATE secciones

 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 @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')
 
/*
select *
  from ln_contraparte_indicadores 
 where contraparte = 160338
   and gestion = 157434 
   and indicador = 250 
   and visible = 1


select * from LN_control_black_list 
where
   identificacion = '33711511569' 
  and visible = 1 
  and ISNULL(vigencia_desde, '1901-01-01') < GETDATE()
  and ISNULL(vigencia_hasta, '2099-12-31') > GETDATE()
*/      


 --iif(valor_numerico=100,'V','R')  --
 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
    
--    select @BL_score as valor_numerico, @BL_estado as valor_texto,@contraparte as contra,@gestion as gest,@indicador as indic
    
 -- 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


 -- Patrimonio
 declare @patrimonio_estado char(1) = 'V'
 declare @patrimonio_score  decimal(5,2) = 100 
 set @indicador = (select indicador from ln_indicadores where clave_indicador = 'AprobacionPatrimonio')
 
 exec LN_dashboard_cliente 'AprobacionPatrimonio', @cliente, null, null, @contraparte
 select @patrimonio_score = valor_numerico, @patrimonio_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 @formales_estado  = 'R' 
  set @patrimonio_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, 
            patrimonio_estado, patrimonio_score, 
            contables_estado, contables_score, 
            legales_estado, legales_score, 
            bancos_estado, bancos_score, 
            cheques_estado, cheques_score, 
            created, created_by,
            contraparte,black_list,black_list_score) 
                      values (@p_saas_usuario_contrato, GETDATE(), 
                                       @final_estado, 
            @formales_estado, @formales_score, 
            @fiscales_estado, @fiscales_score, 
            @patrimonio_estado, @patrimonio_score, 
            @contables_estado, @contables_score, 
            @legales_estado, @legales_score, 
            @bancos_estado, @bancos_score, 
            @cheques_estado, @cheques_score, 
            GETDATE(), @p_user_id, 
            @contraparte,@BL_estado,@BL_score) 
 declare @id int = SCOPE_IDENTITY()
 /* el insert en LN_contrapartes_queue estaba comentado, lo volvemos a poner el 29/12/2021*/
 if not exists (select * from LN_contrapartes_queue where contraparte = @contraparte and processed = 0)
 begin
-- insert into LN_contrapartes_queue(contraparte) values (@contraparte)
  insert into LN_contrapartes_queue (contraparte,id_taken) values (@contraparte,1.0 + floor(10 * RAND(convert(varbinary, newid()))))
 end  

 select * from SAAS_Historial_Consultas where saas_historial_consulta = @id 

END

GO