dbo.API_BCRA

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <14/07/2021>
-- Description: Api datos BCRA - Personalizado por cliente en la tabla ln_dias_alerta (producto = 'API') 
-- =============================================
exec [API_BCRA] 240155302, '30611995128'
exec [API_BCRA] 2254875, '30611995128'


select * from ChequesBCRA 
    WHERE Cuit_firmante = '30611995128'
  and fecha_levantamiento is not null 
and fecha_rechazo > GETDATE() - 90
and visible = 1
order by nro_cheque 

*/

CREATE OR ALTER PROCEDURE dbo.API_BCRA 
  @cliente int, 
  @cuit varchar(20)
AS
BEGIN
 SET NOCOUNT ON;
 
 declare @respuesta table (nodo varchar(max), valor bit, detalle varchar(max)) 

 DECLARE @p_5anios smalldatetime = getdate() - 1825
    DECLARE @p_2anios smalldatetime = getdate() - 730
    DECLARE @p_1anio  smalldatetime = getdate() - 365
 declare @cheques table (causal int, fecha_rechazo date, fecha_levantamiento date, monto decimal(12,2)) 

 declare @p_montoch  bigint = null 
 declare @p_diasch   int = null 
 declare @p_cantch   int = null 
 declare @p_comentarios varchar(max) = null 

 declare @p_cant int  = 0
 declare @p_monto decimal(12,2) = 0
 declare @p_nodo varchar(max)


------------------------------------------------------------------------------------
-- Vemos si tiene cosas definidas en ln_dias_alerta, sino le ponemos el default
------------------------------------------------------------------------------------
 declare @ln_dias_alertas table (
    [cliente] [int] ,
    [tabla] [varchar](50) ,
    [causal] [int] ,
    [dias] [int] ,
    [levantados] [char](1) ,
    [cantidad] [int] ,
    [monto] [bigint] ,
    [valor] [decimal](18, 2) ,
    [puntos] [decimal](5, 2) ,
    [contenido] [varchar](20) ,
    [producto] [varchar](10) ,
    [comentario_interno] [varchar](max),
    [nodo] [varchar](max))

 if exists (select * from ln_dias_alertas where cliente = @Cliente and producto = 'API')
 begin 
 insert into @ln_dias_alertas (cliente,tabla,causal,dias,levantados,cantidad,monto,valor,puntos,contenido,producto,comentario_interno)
       select cliente,tabla,causal,dias,levantados,cantidad,monto,valor,puntos,contenido,producto,comentario_interno
    from ln_dias_alertas where cliente = @Cliente and producto = 'API'
 end 
 else 
 begin 
  insert into @ln_dias_alertas (cliente,tabla,causal,dias,levantados,cantidad,monto,valor,puntos,contenido,producto,comentario_interno,nodo)
     values (@cliente, 'api-cendeu','2','360',NULL,NULL,NULL,NULL,1,NULL,'API','Situación mayor o igual a 2, en la Central de deudores, en los último 12 meses','Central_Deudores_sitMayor2')

  insert into @ln_dias_alertas (cliente,tabla,causal,dias,levantados,cantidad,monto,valor,puntos,contenido,producto,comentario_interno,nodo)
     values (@cliente, 'api-cheques','2','180','N',NULL,NULL,NULL,1,NULL,'API','Cheques rechazados sin fondos, sin levantar, en los últimos 6 meses','Cheques_rechazados_sinFondo_sinLevantar')

  insert into @ln_dias_alertas (cliente,tabla,causal,dias,levantados,cantidad,monto,valor,puntos,contenido,producto,comentario_interno,nodo)
     values (@cliente, 'api-cheques','1','180','N',NULL,NULL,NULL,1,NULL,'API','Cheques rechazados por defectos formales, sin levantar, en los últimos 6 meses','Cheques_rechazados_defectosFormales_sinLevantar' )
  
  insert into @ln_dias_alertas (cliente,tabla,causal,dias,levantados,cantidad,monto,valor,puntos,contenido,producto,comentario_interno,nodo)
     values (@cliente, 'api-cheques',NULL,'90','S','10',NULL,NULL,1,NULL,'API','Más de 10 cheques rechazados, levantados, en los ultimos 3 meses','Cheques_rechazados_levantados' )
 end 

----------------------------------------------------------
-- CENDEU:
----------------------------------------------------------
 declare @bancos table (fec_inf varchar(6), banco varchar(100), situacion int, deuda numeric(12,2))
 declare @p_causal int
 declare @p_causal_ant int = 9 
 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

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cendeu' 
      and cliente = @Cliente and producto = 'API')
 begin 

 DECLARE C_cendeu CURSOR FOR
   select  isnull(dias,360), 
     isnull(monto,0), 
     isnull(cantidad,0), 
     comentario_interno,
     isnull(causal,2),
     ISNULL(nodo,'Central_Deudores_sitMayor'+convert(varchar,isnull(causal,'2')))
     from @ln_dias_alertas 
     where tabla = 'api-cendeu' 
     and cliente = @Cliente and producto = 'API'
     order by causal desc

 OPEN C_cendeu
 FETCH NEXT FROM C_cendeu INTO   @p_diasch,
         @p_montoch, 
         @p_cantch,
         @p_comentarios,
         @p_causal, 
         @p_nodo
 WHILE @@fetch_status = 0
 BEGIN 

  set @p_cant  = 0
  set @p_monto = 0

  declare @p_meses int = ((@p_diasch / 30) - 1) * (-1)
  declare @hoy date = dateadd(MM,@p_meses,@dtEnd) 
  declare @xmeses varchar(6) = (convert(varchar(4),DATEPART(yyyy,@hoy))
          + right('0'+convert(varchar(2),DATEPART(mm,@hoy)),2))

  select @p_cant = COUNT(distinct banco)
    from @bancos 
   where cast(fec_inf as int) >= cast(@xmeses as int) and situacion >= @p_causal and situacion < @p_causal_ant

  select @p_monto = sum(sbr.total*1000) from 
  (select banco, max(deuda) as total from @bancos a where cast(a.fec_inf as int) >= cast(@xmeses as int) 
     and a.situacion >= @p_causal and a.situacion < @p_causal_ant group by banco) sbr

  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
  set @p_causal_ant = @p_causal

  FETCH NEXT FROM C_cendeu INTO   @p_diasch,
          @p_montoch, 
          @p_cantch,
          @p_comentarios,
          @p_causal, 
          @p_nodo


 end 
 END  
 CLOSE C_cendeu
 DEALLOCATE C_cendeu


----------------------------------------------------------
-- CHEQUES RECHAZADOS:
----------------------------------------------------------
 insert into @cheques
 select id_causa, fecha_rechazo, fecha_levantamiento, importe 
 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 

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal = 2 and levantados = 'N' and producto = 'API')
 begin 
  select @p_diasch   = isnull(dias,360),
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno, 
    @p_nodo = ISNULL(nodo,'Cheques_rechazados_sinFondo_sinLevantar')
    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal = 2 and levantados = 'N' and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where causal = 2 
   and fecha_levantamiento is null 
   and fecha_rechazo > GETDATE() - @p_diasch

  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 

-- Cheques DEFECTOS FORMALES - SIN LEVANTAR 

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal = 1 and levantados = 'N' and producto = 'API')
 begin 
  select @p_diasch   = isnull(dias,360),
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno , 
    @p_nodo = isnull(nodo,'Cheques_rechazados_defectosFormales_sinLevantar' )

    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal = 1 and levantados = 'N' and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where causal = 1
   and fecha_levantamiento is null 
   and fecha_rechazo > GETDATE() - @p_diasch
   
  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 

-- Cheques SIN FONDOS o DEFECTOS FORMALES - SIN LEVANTAR 

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal is null and levantados = 'N' and producto = 'API')
 begin 
  select @p_diasch   = isnull(dias,360),
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno, 
    @p_nodo = isnull(nodo,'Cheques_rechazados_sinLevantar')

    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal is null and levantados = 'N' and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where fecha_levantamiento is null 
   and fecha_rechazo > GETDATE() - @p_diasch

  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 

---------------------------------------------------------------------------------------------
-- Cheques SIN FONDOS - LEVANTADOS

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal = 2 and levantados = 'S' and producto = 'API')
 begin 
  select @p_diasch   = isnull(dias,360),
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno, 
    @p_nodo = isnull(nodo,'Cheques_rechazados_sinFondo_levantados' )
 
    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal = 2 and levantados = 'S' and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where causal = 2 
   and fecha_levantamiento is not null 
   and fecha_rechazo > GETDATE() - @p_diasch


  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 

 -- Cheques DEFECTOS FORMALES - LEVANTADOS

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal = 1 and levantados = 'S' and producto = 'API')
 begin 
  select @p_diasch   = isnull(dias,360),
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno, 
    @p_nodo = isnull(nodo,'Cheques_rechazados_defectosFormales_levantados')
    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal = 1 and levantados = 'S' and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where causal = 1
   and fecha_levantamiento is not null 
   and fecha_rechazo > GETDATE() - @p_diasch

  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 

 -- Cheques SIN FONDOS o DEFECTOS FORMALES - LEVANTADOS

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal is null and levantados = 'S' and producto = 'API')
 begin 
  select @p_diasch   = isnull(dias,360),
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno ,
    @p_nodo = isnull(nodo,'Cheques_rechazados_levantados')
    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal is null and levantados = 'S' and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where fecha_levantamiento is not null 
   and fecha_rechazo > GETDATE() - @p_diasch

  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 

 -- Cheques SIN FONDOS o DEFECTOS FORMALES - LEVANTADOS o SIN LEVANTAR

 if exists (select * from @ln_dias_alertas 
      where tabla = 'api-cheques' 
      and cliente = @Cliente and causal is null and levantados is null and producto = 'API')
 begin 
  select @p_diasch    = isnull(dias,360), 
    @p_montoch  = isnull(monto,0), 
    @p_cantch   = isnull(cantidad,0), 
    @p_comentarios = comentario_interno, 
    @p_nodo = isnull(nodo,'Cheques_rechazados')
    from @ln_dias_alertas 
    where tabla = 'api-cheques' 
    and cliente = @Cliente and causal is null and levantados is null and producto = 'API'
  set @p_cant  = 0
  set @p_monto = 0
  select @p_cant = count(*), @p_monto = SUM(monto) 
    from @cheques 
    where fecha_rechazo > GETDATE() - @p_diasch

  if (@p_cant > @p_cantch and @p_monto >= @p_montoch) 
  begin
   insert into @respuesta values (@p_nodo, 1, @p_comentarios)
  end 
  else
  begin
   insert into @respuesta values (@p_nodo, 0, @p_comentarios)
  end 
 end 



 select * from @respuesta

END

GO