dbo.procesamiento_insur_continuacion

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
--exec procesamiento_insur_continuacion '30708627581'    
CREATE OR ALTER PROCEDURE dbo.procesamiento_insur_continuacion
@cuit_arranca varchar(20) = '30708627581' 
AS

BEGIN
    SET NOCOUNT ON;
 declare @p_cuit       varchar(20) 
    declare @p_nombre     varchar(100)
 declare @p_cod_alerta char(5)
 declare @p_registro   varchar(max)
 declare @p_cant       int
 declare @p_hay        char(1)
 -- concursos / juicios
 declare @p_fecha      char(10)
 declare @p_causa      varchar(100)
 declare @p_juzgado    varchar(100)
 declare @p_secretaria varchar(100)
 declare @p_cuit_actor varchar(100)
 declare @p_actor      varchar(100)
 declare @p_actor2     varchar(100)
 declare @p_observaciones varchar(100)
 -- Central de Deudores
 declare @dtEnd date, @aniomes char(6)
 declare @p_fec_inf varchar(6)
 declare @p_situacion varchar(2)
 declare @p_modified datetime
 declare @p_created datetime
 declare @p_periodo_deuda varchar(10)
 declare @p_total decimal(13,1)
 declare @p_EntidadesBancarias_nombre varchar(100)
 declare @p_deudas table (
       id_cendeu int,
       codigo_ent int,
       fec_inf varchar(6),
       tipo varchar(2),
       iden varchar(20) ,
       actividad varchar(3),
       situacion varchar(2),
       prestamos decimal(12,1),
       particip decimal(12,1),
       garotor decimal(12,1),
       o_cptos decimal(12,1),
       garprefa decimal(12,1),
       garprefb decimal(12,1),
       singarpref decimal(12,1),
       cgarprefa decimal(12,1),
       cgarprefb decimal(12,1),
       sincgarpre decimal(12,1),
       previ decimal(12,1),
       deuda_cubr decimal(12,1),
       proc_jud decimal(1,0),
       refinanc decimal(1,0),
       rec_oblig decimal(1,0),
       sit_juridi decimal(1,0),
       irre_dispt decimal(1,0),
       dia_atraso decimal(4),
       vigente bit,
       modified datetime,
       id_persona int,
       deuda_total decimal(13,1),
       modified_by int,
       deu_observaciones varchar(1024),
       id_fuente int,
       dbf_filename varchar(15),
       created datetime,
       visible varchar(100),
       bco_codigo int,
       periodo_deuda varchar(10),
       total decimal(13,1),
       EntidadesBancarias_nombre varchar(100),
       habeas_data varchar(100)
       )
 -- Cheques Rechazados
 declare @cant  varchar(20)
 declare @monto varchar(20)
 declare @p_5anios smalldatetime = getdate() - 1825
 declare @p_2anios smalldatetime = getdate() - 730
 declare @p_1anio  smalldatetime = getdate() - 365
 declare @p_cant_cheq int
 declare @p_importe_cheq decimal(16,2)
 --
 -- declare @insur_procesamiento table (cuit varchar(20), nombre varchar(100), detalle varchar(500))

--- Borra la tabla de insur que va a contener los resultados de hoy
/*
delete from insur_procesamiento
delete from insur_totales    where fecha = convert(date,getdate())
delete from Insur_Historico  where fecha = convert(date,getdate())
delete from Insur_Ignorados  where fecha = convert(date,getdate())

set @p_cant = (select count(*) from proceso_insur)
insert into insur_totales values (getdate(),@p_cant, 0, getdate(), null)
*/

--set @cuit_arranca = (select MAX(cuit) from insur_procesamiento where nombre is not null)
DECLARE recorre1 CURSOR
    FOR
    /*
     SELECT cuit FROM proceso_insur 
     where cuit > @cuit_arranca
     ORDER BY cuit
     */
 SELECT cuit FROM proceso_insur 
 where not exists 
       (select * from insur_procesamiento 
       where right('00000000000' + RTRIM(ltrim(insur_procesamiento.cuit)), 11) 
        = right('00000000000' + RTRIM(ltrim(proceso_insur.cuit)), 11))
 ORDER BY proceso_insur.cuit 

OPEN recorre1 
Fetch next from recorre1 INTO @p_cuit

WHILE (@@FETCH_STATUS <> -1 )
BEGIN
 set @p_nombre = null
 set @p_registro = null
 set @p_cant = 0
 set @p_hay = 'N'

 set @p_cant = (select count(*) from padrones where identificacion = @p_cuit) 
 if (@p_cant = 0) begin
      set @p_registro = '99999REGISTRO NO VALIDADO'
      INSERT INTO insur_procesamiento VALUES (@p_cuit, @p_nombre, @p_registro)
 end

 else
 
 begin 

  set @p_nombre = (select top 1 nombre_persona from padrones where identificacion = @p_cuit and habilitado = 1) 
     
------- Concursos 
  set @p_Cod_Alerta = '88013'
  DECLARE concursos CURSOR
  FOR
  SELECT CONVERT(varchar(10),fecha_causa,103) AS fecha_causa_dmy,
      tipos_causas.nombre_causa, 
      juzgado,
      secretaria,
      cuit_actor as cuit_otra_parte, 
      actor as nombre_otra_parte,
      pf.nombre_persona as nombre_padron_otra_parte,
      observaciones
  FROM juicios 
  left 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 = @p_cuit )
    and juicios.visible = 1 
    and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
    and juicios.id_tipo_causa = 'CRC'
    and (convert(date, modified) >= convert(date,getdate() - 7)  or convert(date,created) >= convert(date,getdate() - 7))

  OPEN concursos

  Fetch next from concursos INTO  @p_fecha,
          @p_causa, 
          @p_juzgado,
          @p_secretaria,
          @p_cuit_actor,
          @p_actor,
          @p_actor2,
          @p_observaciones
           
  WHILE (@@FETCH_STATUS <> -1 )
  BEGIN

                set @p_registro = @p_cod_Alerta

                if (isnull(@p_causa,'') != '') begin
       set @p_registro = rtrim(@p_registro) + rtrim(@p_causa) + ';'
                end
    
    if (isnull(@p_fecha,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Fecha: ' + rtrim(@p_fecha) + ';'
                end

                if (isnull(@p_juzgado,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Juz.: ' + rtrim(@p_juzgado) + ';'
                end

    if (isnull(@p_secretaria,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Sec.: ' + rtrim(@p_secretaria) + ';'
                end

    if (isnull(@p_actor2,'') != '') begin
        set @p_registro = rtrim(@p_registro) + ' Actor: ' + rtrim(@p_actor2)
                end
    else
    begin
     if (isnull(@p_actor,'') != '') begin
      set @p_registro = rtrim(@p_registro) + ' Actor: ' + rtrim(@p_actor)
     end
     else
     begin
      if (isnull(@p_observaciones,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Actor: ' + rtrim(@p_observaciones)
      end
     end 
    end 
    set @p_cant = (SELECT COUNT(*) 
         FROM Insur_Historico
           WHERE Cuit = @p_cuit
            AND rtrim(Detalle) = rtrim(@p_registro))
    IF (@p_cant = 0) begin
     set @p_hay = 'C'
     INSERT INTO Insur_Historico     VALUES (@p_cuit, @p_registro, getdate())
     INSERT INTO insur_procesamiento VALUES (@p_cuit, @p_nombre, @p_registro)
    end    
    ELSE
    begin 
     INSERT INTO Insur_Ignorados VALUES (@p_cuit, @p_registro, getdate())
    end 
    Fetch next from concursos INTO  
          @p_fecha,
          @p_causa, 
          @p_juzgado,
          @p_secretaria,
          @p_cuit_actor,
          @p_actor,
          @p_actor2,
          @p_observaciones
  END
  CLOSE concursos
  DEALLOCATE concursos

------- Juicios
  set @p_Cod_Alerta = '88015'
  DECLARE causas CURSOR
  FOR
  SELECT CONVERT(varchar(10),fecha_causa,103) AS fecha_causa_dmy,
      tipos_causas.nombre_causa, 
      juzgado,
      secretaria,
      cuit_actor as cuit_otra_parte, 
      actor as nombre_otra_parte,
      pf.nombre_persona as nombre_padron_otra_parte,
      observaciones
  FROM juicios 
  left 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 = @p_cuit )
    and juicios.visible = 1 
    and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365)
    and juicios.id_tipo_causa in 
                               ( 'EJH',
                                       'EJP',
                                       'IDF',
                                       'IB',
                                       'JE',
                                       'JPC',
                                       'JP',
                                       'JS',
                                       'JSM',
                                       'LF',
                                       'LJ',
                                       'CP',
                                       'PQ',
                                       'QCC',
                                       'QDC',
                                       'QDZ',
                                       'SPR',
                                       'PRQ')

    and (convert(date, modified) >= convert(date,getdate() - 7)  or convert(date,created) >= convert(date,getdate() - 7))
 
    OPEN causas
 
  Fetch next from causas INTO  @p_fecha,
          @p_causa, 
          @p_juzgado,
          @p_secretaria,
          @p_cuit_actor,
          @p_actor,
          @p_actor2,
          @p_observaciones
           
  WHILE (@@FETCH_STATUS <> -1 )
  BEGIN

                set @p_registro = @p_cod_Alerta

                if (isnull(@p_causa,'') != '') begin
       set @p_registro = rtrim(@p_registro) + rtrim(@p_causa) + ';'
                end
    
    if (isnull(@p_fecha,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Fecha: ' + rtrim(@p_fecha) + ';'
                end

                if (isnull(@p_juzgado,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Juz.: ' + rtrim(@p_juzgado) + ';'
                end

    if (isnull(@p_secretaria,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Sec.: ' + rtrim(@p_secretaria) + ';'
                end

    if (isnull(@p_actor2,'') != '') begin
        set @p_registro = rtrim(@p_registro) + ' Actor: ' + rtrim(@p_actor2)
                end
    else
    begin
     if (isnull(@p_actor,'') != '') begin
      set @p_registro = rtrim(@p_registro) + ' Actor: ' + rtrim(@p_actor)
     end
     else
     begin
      if (isnull(@p_observaciones,'') != '') begin
       set @p_registro = rtrim(@p_registro) + ' Actor: ' + rtrim(@p_observaciones)
      end
     end 
    end 
    set @p_cant = (SELECT COUNT(*)
         FROM Insur_Historico
           WHERE Cuit = @p_cuit
            AND rtrim(Detalle) = rtrim(@p_registro))
    IF (@p_cant = 0) begin
     set @p_hay = 'J'
     INSERT INTO Insur_Historico VALUES (@p_cuit, @p_registro, getdate())
     INSERT INTO insur_procesamiento VALUES (@p_cuit, @p_nombre, @p_registro)
    end    
    ELSE
    begin 
     INSERT INTO Insur_Ignorados VALUES (@p_cuit, @p_registro, getdate())
    end 
    Fetch next from causas INTO 
          @p_fecha,
          @p_causa, 
          @p_juzgado,
          @p_secretaria,
          @p_cuit_actor,
          @p_actor,
          @p_actor2,
          @p_observaciones
  END
  CLOSE causas
  DEALLOCATE causas
  
------- Central de deudores
  set @dtEnd = dateadd(month, (-3), getdate())
  set @aniomes = convert(varchar(4), datepart(year,@dtEnd)) + right('0' + rtrim(datepart(MONTH,@dtEnd)),2)

  set @p_Cod_Alerta = '88012'
  delete from @p_deudas
  insert into @p_deudas 
  EXEC backend_central_deudores @p_cuit, '60', '1'

  DECLARE deudas CURSOR
  FOR
  select periodo_deuda, fec_inf, EntidadesBancarias_nombre, total, situacion, created, modified from @p_deudas

  OPEN deudas
  Fetch next from deudas INTO     @p_periodo_deuda,
          @p_fec_inf,
          @p_EntidadesBancarias_nombre,
          @p_total, 
          @p_situacion,
          @p_created,
          @p_modified       

  WHILE (@@FETCH_STATUS <> -1 )
  BEGIN
      -- Solo muestra las deudas en situacion > 1, de mas de 15000 y que sean de los ultimos 3 meses 
   set @p_total = @p_total * 1000
      if ((convert(date, @p_modified) >= convert(date,getdate() - 7)  or convert(date,@p_created) >= convert(date,getdate() - 7)) 
   and (convert(int, @p_situacion) > 1) 
      and (@p_total >= 15000)  
   and (@p_fec_inf >= @aniomes)) 
   begin
    set @monto = format(@p_total, '##,###.00')
    set @p_periodo_deuda = SUBSTRING(@p_periodo_deuda,4,4) + '-' + SUBSTRING(@p_periodo_deuda,1,2)
    set @p_registro = @p_cod_alerta   + 
                                  'Entidad: '     + rtrim(@p_EntidadesBancarias_nombre) + '; ' + 
                                  'Mes: '         + rtrim(@p_periodo_deuda)             + '; ' +
                                  'Situacion: '   + rtrim(@p_situacion)                 + '; ' +
                                  'Monto Deuda: ' + rtrim(convert(char,@monto))

    set @p_cant = (SELECT COUNT(*)
         FROM Insur_Historico
           WHERE Cuit = @p_cuit
            AND rtrim(Detalle) = rtrim(@p_registro))
    IF (@p_cant = 0) begin
     set @p_hay = 'D'
     INSERT INTO Insur_Historico     VALUES (@p_cuit, @p_registro, getdate())
     INSERT INTO insur_procesamiento VALUES (@p_cuit, @p_nombre, @p_registro)
    end    
    ELSE
    begin 
     INSERT INTO Insur_Ignorados VALUES (@p_cuit, @p_registro, getdate())
    end 

   end

   Fetch next from deudas INTO @p_periodo_deuda,
          @p_fec_inf,
          @p_EntidadesBancarias_nombre,
          @p_total, 
          @p_situacion,
          @p_created,
          @p_modified 
  END
  CLOSE deudas
  DEALLOCATE deudas
  
------- Cheques Rechazados 
  set @p_Cod_Alerta = '88011'
  SELECT @p_cant_cheq = count(*), 
      @p_importe_cheq = sum(isnull(importe,0))
     FROM ChequesBCRA
    WHERE cuit_firmante = @p_Cuit
      and (isnull(cuit_relacionado,'') = '')
      and id_causa = 2 
      and fecha_levantamiento is null 
      and visible = '1'
      and fecha_rechazo >= @p_1anio
      and not exists (
         select * from chequesBCRA b 
       where b.cuit_firmante = chequesBCRA.cuit_firmante 
         and b.nro_cheque = chequesBCRA.nro_cheque 
         and b.visible = 1
         and id_cheque < ChequesBCRA.id_cheque)
      and (convert(date, modified) >= convert(date,getdate() - 7)  or convert(date,created) >= convert(date,getdate() - 7))
      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) 
      )

     )

  if (isnull(@p_importe_cheq,0) >= 15000 and isnull(@p_cant,0) != 0) begin
      set @cant  = format(@p_cant_cheq, '#########')
   set @monto = format(@p_importe_cheq, '##,###.00')
   set @p_registro = @p_cod_Alerta + 
        'Cant S/Fondos: ' + rtrim(convert(char,@cant)) + 
        '; Monto Total: ' + rtrim(convert(char,@monto))
 
   set @p_cant = (SELECT COUNT(*)
        FROM Insur_Historico
       WHERE Cuit = @p_cuit
          AND rtrim(Detalle) = rtrim(@p_registro))
   IF (@p_cant = 0) begin
    set @p_hay = 'H'
    INSERT INTO Insur_Historico     VALUES (@p_cuit, @p_registro, getdate())
    INSERT INTO insur_procesamiento VALUES (@p_cuit, @p_nombre, @p_registro)
   end    
   ELSE
   begin 
    INSERT INTO Insur_Ignorados VALUES (@p_cuit, @p_registro, getdate())
   end 
    
  end

  IF (@p_hay = 'N') begin
     set @p_registro = '00000NO REGISTRA NOVEDADES'
     INSERT INTO insur_procesamiento VALUES (@p_cuit, @p_nombre, @p_registro)
  end  
 end  
 update insur_totales set cantidad_procesada = cantidad_procesada + 1 
  where fecha = convert(date, getdate())
 Fetch next from recorre1 INTO @p_cuit
END 
CLOSE recorre1
DEALLOCATE recorre1

--select cuit, nombre, detalle from insur_procesamiento order by cuit
update insur_totales set Hora_Fin = getdate() where fecha = convert(date,getdate())
delete from proceso_insur
END 

GO