dbo.monitoreos

CREATE TABLE dbo.monitoreos (
  id_mantenimiento int IDENTITY,
  cliente int NOT NULL,
  user_id int NOT NULL,
  seccion_mantenimiento varchar(50) NULL,
  nro_cheque varchar(20) NULL,
  importe decimal(15, 2) NULL,
  id_banco int NULL,
  fecha_cheque datetime NULL,
  fecha_vigencia_mantenimiento datetime NULL,
  visible bit NULL CONSTRAINT DF_monitoreos_activo DEFAULT (1),
  fecha_modificacion datetime NULL,
  modificado_por int NULL,
  cuenta_banco varchar(20) NULL,
  cuit varchar(13) NULL,
  novedad bit NULL,
  fecha_ultimo_procesamiento smalldatetime NULL,
  fecha_desde smalldatetime NULL CONSTRAINT DF_monitoreos_fecha_desde DEFAULT (getdate()),
  observaciones_cliente varchar(200) NULL,
  id_referencia_externa varchar(20) NULL,
  score_cuit decimal(3, 1) NULL,
  created smalldatetime NULL DEFAULT (getdate())
)
ON [PRIMARY]
GO

CREATE INDEX idx_monitoreos_cliente_visible
  ON dbo.monitoreos (cliente, visible)
  INCLUDE (id_mantenimiento, user_id, seccion_mantenimiento, fecha_vigencia_mantenimiento, fecha_modificacion, cuit)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

CREATE INDEX idx_monitoreos_cuit
  ON dbo.monitoreos (cuit)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

CREATE INDEX idx_monitoreos_seccion_mantenimiento_nro_cheque_cuit
  ON dbo.monitoreos (visible, fecha_vigencia_mantenimiento)
  INCLUDE (seccion_mantenimiento, nro_cheque, cuit)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

CREATE INDEX idx_monitoreos_visible_fecha_vigencia_referencia_externa
  ON dbo.monitoreos (visible, fecha_vigencia_mantenimiento, id_referencia_externa)
  INCLUDE (cuit)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

CREATE INDEX idx_monitoreos_visible_fechavig_fechadesde
  ON dbo.monitoreos (visible, fecha_vigencia_mantenimiento, fecha_desde)
  INCLUDE (seccion_mantenimiento, cuit, nro_cheque)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

CREATE INDEX idxMonitoreoCliente
  ON dbo.monitoreos (user_id, visible, fecha_vigencia_mantenimiento)
  INCLUDE (cliente, cuit, fecha_desde)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

CREATE INDEX idxMonitoreosVisibles
  ON dbo.monitoreos (cliente, visible)
  INCLUDE (id_mantenimiento, user_id, seccion_mantenimiento, nro_cheque, importe, id_banco, fecha_cheque, fecha_vigencia_mantenimiento, fecha_modificacion, modificado_por, cuenta_banco, cuit, novedad, fecha_ultimo_procesamiento, fecha_desde, observaciones_cliente, id_referencia_externa, score_cuit)
  WITH (FILLFACTOR = 80)
  ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE OR ALTER trigger dbo.TD_monitoreos on dbo.monitoreos 
FOR DELETE as
BEGIN
 DECLARE @cliente int = null,  
         @cuit      varchar(50) = NULL,
   @seccion   varchar(50) = NULL, 
   @fecha_desde    datetime = NULL, 
   @fecha_vigencia datetime = NULL, 
   @visible   bit = NULL,
   @id_padron int = NULL,
   @id_tabla  int = NULL,
   @detalle   varchar(max) = NULL,
   @user_id   int = NULL --> pero no tenemos el usuario cuando se hace un delete 

 
 DECLARE C_DELETE CURSOR FOR
   select  deleted.cliente, deleted.cuit,
     deleted.seccion_mantenimiento,
     deleted.fecha_desde, 
     deleted.fecha_vigencia_mantenimiento, 
     deleted.visible,
     null as user_id, --> no tenemos que usuario hizo el delete
     deleted.id_mantenimiento 
     from  deleted 
 OPEN C_DELETE
 FETCH NEXT FROM C_DELETE INTO  @cliente, @cuit,
         @seccion,
         @fecha_desde, 
         @fecha_vigencia, 
         @visible, 
         @user_id, 
         @id_tabla
 WHILE @@fetch_status = 0
 BEGIN 

  if (select count(*) from padrones where identificacion = @cuit and habilitado = 1) = 1
   select @id_padron = id_padron
     from padrones 
    where identificacion = @cuit
         and habilitado = 1 
    and identificador in (select identificador from identificadores where country_code = 'AR') 
   
  set @detalle = 'BAJA DE MONITOREO: Cuit: ' + @cuit + 
                 ' - Seccion: ' + 
        case @seccion
             when null then 'Todas'
       when '0'  then 'Todas'
       when '2'  then 'Formales'
       when '6'  then 'Fiscales'
       when '7'  then 'Contables'
       when '8'  then 'Relaciones'
       when '9'  then 'Legales'
       when '10' then 'Bancos'
       when '11' then 'Comex'
       when '18' then 'Cheques'
       else @seccion 
       end + 
        ' - Vigencia desde: ' + isnull(CONVERT(varchar,@fecha_desde,103),'-') + 
        ' - Vigencia hasta: ' + isnull(CONVERT(varchar,@fecha_vigencia,103),'-') 

  
  -- Guarda la trazabilidad de la baja
  insert into trazabilidad_monitoreos 
   ( 
   [tabla],
   [id_registro_tabla],
   [created],
   [accion],
   [detalle],
   cliente, 
   [cuit],
   [id_padron],
   seccion_old,
   [visible_old],
   [fecha_desde_old], 
   [fecha_vigencia_old])
  values 
   (
      'monitoreos',
   @id_tabla, 
   getdate(), 
   'DELETE',
   @detalle, 
   @cliente,
   @cuit,
   @id_padron,
   @seccion,
   @visible, 
   @fecha_desde, 
   @fecha_vigencia) 
   
   

  FETCH NEXT FROM C_DELETE INTO  @cliente, @cuit,
          @seccion,
          @fecha_desde, 
          @fecha_vigencia,
          @visible, 
          @user_id, 
          @id_tabla


 END
 CLOSE C_DELETE
 DEALLOCATE C_DELETE 
END 
GO
CREATE OR ALTER trigger dbo.TI_monitoreos on dbo.monitoreos 
FOR insert as
BEGIN
 DECLARE @cliente int = null, 
         @cuit      varchar(50) = NULL,
   @seccion   varchar(50) = NULL, 
   @fecha_desde    datetime = NULL, 
   @fecha_vigencia datetime = NULL, 
   @visible   bit = NULL,
   @id_padron int = NULL,
   @id_tabla  int = NULL,
   @detalle   varchar(max) = NULL, 
   @user_id   int = NULL 
 
 DECLARE C_alta CURSOR FOR
   select  inserted.cliente, inserted.cuit,
     inserted.seccion_mantenimiento,
     inserted.fecha_desde, 
     inserted.fecha_vigencia_mantenimiento, 
     inserted.visible, 
     inserted.user_id, 
     inserted.id_mantenimiento
     from  inserted 
 OPEN C_alta
 FETCH NEXT FROM C_alta INTO  @cliente, @cuit,
         @seccion,
         @fecha_desde, 
         @fecha_vigencia, 
         @visible, 
         @user_id, 
         @id_tabla
 WHILE @@fetch_status = 0
 BEGIN 

  if (select count(*) from padrones where identificacion = @cuit and habilitado = 1) = 1
   select @id_padron = id_padron
     from padrones 
    where identificacion = @cuit
         and habilitado = 1 
    and identificador in (select identificador from identificadores where country_code = 'AR') 
   
  set @detalle = 'ALTA DE MONITOREO: Cuit: ' + @cuit + 
                 ' - Seccion: ' + 
        case @seccion
             when null then 'Todas'
       when '0'  then 'Todas'
       when '2'  then 'Formales'
       when '6'  then 'Fiscales'
       when '7'  then 'Contables'
       when '8'  then 'Relaciones'
       when '9'  then 'Legales'
       when '10' then 'Bancos'
       when '11' then 'Comex'
       when '18' then 'Cheques'
       else @seccion 
       end + 
        ' - Vigencia desde: ' + isnull(CONVERT(varchar,@fecha_desde,103),'-') + 
        ' - Vigencia hasta: ' + isnull(CONVERT(varchar,@fecha_vigencia,103),'-') 
  
  -- Guarda la trazabilidad de la baja
  insert into trazabilidad_monitoreos 
   ( 
   [tabla],
   [id_registro_tabla],
   [created],
   [accion],
   [detalle],
   cliente, 
   [cuit],
   [id_padron],
   [seccion_new],
   [visible_new],
   [fecha_desde_new], 
   [fecha_vigencia_new],
   [user_id])
  values 
   (
      'monitoreos',
   @id_tabla, 
   getdate(), 
   'INSERT',
   @detalle,
   @cliente, 
   @cuit,
   @id_padron,
   @seccion,
   @visible, 
   @fecha_desde, 
   @fecha_vigencia, 
   @user_id) 
   

  FETCH NEXT FROM C_alta INTO  @cliente, @cuit,
          @seccion,
          @fecha_desde, 
          @fecha_vigencia, 
          @visible, 
          @user_id, 
          @id_tabla


 END
 CLOSE C_alta
 DEALLOCATE C_alta 
END 
GO
CREATE OR ALTER trigger dbo.TU_monitoreos on dbo.monitoreos 
FOR UPDATE as
BEGIN

 
 DECLARE @d_cliente int = null, @d_cuit      varchar(50) = NULL,
   @d_seccion   varchar(50) = NULL, 
   @d_fecha_desde    datetime = NULL, 
   @d_fecha_vigencia datetime = NULL, 
   @d_visible   bit = NULL,
   @d_user_id   int = NULL,
   @d_id_tabla  int = NULL
   

 DECLARE @i_cliente int = null,@i_cuit      varchar(50) = NULL,
   @i_seccion   varchar(50) = NULL, 
   @i_fecha_desde    datetime = NULL, 
   @i_fecha_vigencia datetime = NULL,
   @i_visible   bit = NULL,
   @i_user_id   int = NULL,
   @i_id_tabla  int = NULL

 DECLARE @id_padron int = NULL,
   @detalle   varchar(max) = NULL

 DECLARE C_Cursor CURSOR FOR
    select deleted.cliente, deleted.cuit,
     deleted.seccion_mantenimiento,
     deleted.fecha_desde, 
     deleted.fecha_vigencia_mantenimiento, 
     deleted.visible,
     deleted.modificado_por, 
     deleted.id_mantenimiento, 

     inserted.cliente, inserted.cuit,
     inserted.seccion_mantenimiento,
     inserted.fecha_desde, 
     inserted.fecha_vigencia_mantenimiento, 
     inserted.visible, 
     inserted.modificado_por, 
     inserted.id_mantenimiento
  from inserted 
  inner join deleted on inserted.id_mantenimiento = deleted.id_mantenimiento
 OPEN C_Cursor
 FETCH NEXT FROM C_Cursor INTO  @d_cliente, @d_cuit ,
         @d_seccion,
         @d_fecha_desde,
         @d_fecha_vigencia,
         @d_visible,
         @d_user_id, 
         @d_id_tabla,  

         @i_cliente, @i_cuit ,
         @i_seccion,
         @i_fecha_desde,
         @i_fecha_vigencia,
         @i_visible,
         @i_user_id, 
         @i_id_tabla


 WHILE @@fetch_status = 0
 BEGIN 

  -- Si no cambio el visible, ni las fechas desde/vigencia, ni la seccion no graba nada
  -- (porque no vamos a guardar trazabilidad, cada vez que se corre el monitoreo y se ve un cambio en la fecha de modificacion)
  if (isnull(@i_visible,1) = isnull(@d_visible,1) 
   and isnull(@i_fecha_desde,'1900-01-01') = isnull(@d_fecha_desde,'1900-01-01') 
   and isnull(@i_fecha_vigencia,'1900-01-01') = isnull(@d_fecha_vigencia,'1900-01-01')  
   and isnull(@i_seccion,'0') = isnull(@d_seccion,'0'))
  begin
   break 
   --return 
  end 
 
  if (select count(*) from padrones where identificacion = @d_cuit and habilitado = 1) = 1
  begin
   select @id_padron = id_padron
     from padrones 
    where identificacion = @i_cuit
         and habilitado = 1 
    and identificador in (select identificador from identificadores where country_code = 'AR') 
  end
  if (isnull(@i_visible,1)  = 0 and isnull(@d_visible,1)  = 1)
  begin  
   set @detalle = 'BAJA DE MONITOREO: Cuit: ' + @d_cuit + 
         ' - Seccion: ' + 
         case @d_seccion
        when null then 'Todas'
        when '0'  then 'Todas'
        when '2'  then 'Formales'
        when '6'  then 'Fiscales'
        when '7'  then 'Contables'
        when '8'  then 'Relaciones'
        when '9'  then 'Legales'
        when '10' then 'Bancos'
        when '11' then 'Comex'
        when '18' then 'Cheques'
        else @d_seccion 
       end
  end 
  if (isnull(@i_visible,1) = 1 and isnull(@d_visible,1) = 0)
  -- Supuestamente no pasa: las altas son altas, no update
  begin
   set @detalle = 'ALTA DE MONITOREO: Cuit: ' + @i_cuit + 
                 ' - Seccion: ' + 
        case @i_seccion
             when null then 'Todas'
       when '0'  then 'Todas'
       when '2'  then 'Formales'
       when '6'  then 'Fiscales'
       when '7'  then 'Contables'
       when '8'  then 'Relaciones'
       when '9'  then 'Legales'
       when '10' then 'Bancos'
       when '11' then 'Comex'
       when '18' then 'Cheques'
       else @i_seccion 
       end + 
        ' - Vigencia desde: ' + isnull(CONVERT(varchar,@i_fecha_desde,103),'-') + 
        ' - Vigencia hasta: ' + isnull(CONVERT(varchar,@i_fecha_vigencia,103),'-') 
  end 

  if (isnull(@i_visible,1) = isnull(@d_visible,1))
  begin
    set @detalle = 'MODIFICACION DE MONITOREO: Cuit: ' + @i_cuit
    if (@i_seccion != @d_seccion)
    begin
     set @detalle = @detalle + ' - Seccion: ' + 
         ' anterior: ' + 
         case @d_seccion
        when null then 'Todas'
        when '0'  then 'Todas'
        when '2'  then 'Formales'
        when '6'  then 'Fiscales'
        when '7'  then 'Contables'
        when '8'  then 'Relaciones'
        when '9'  then 'Legales'
        when '10' then 'Bancos'
        when '11' then 'Comex'
        when '18' then 'Cheques'
        else @d_seccion 
        end +
       ' nueva: ' + 
         case @i_seccion
        when null then 'Todas'
        when '0'  then 'Todas'
        when '2'  then 'Formales'
        when '6'  then 'Fiscales'
        when '7'  then 'Contables'
        when '8'  then 'Relaciones'
        when '9'  then 'Legales'
        when '10' then 'Bancos'
        when '11' then 'Comex'
        when '18' then 'Cheques'
        else @i_seccion 
        end   
    end 
    IF(isnull(CONVERT(varchar,@d_fecha_desde,103),'-') != isnull(CONVERT(varchar,@i_fecha_desde,103),'-'))
    begin
     set @detalle = @detalle + ' - Vigencia desde: ' +
         'anterior: ' + isnull(CONVERT(varchar,@d_fecha_desde,103),'-') + 
         ' nueva: ' + isnull(CONVERT(varchar,@i_fecha_desde,103),'-')
    end 
    IF(isnull(CONVERT(varchar,@d_fecha_vigencia,103),'-') != isnull(CONVERT(varchar,@i_fecha_vigencia,103),'-'))
    begin
     set @detalle = @detalle + ' - Vigencia hasta: ' + 
       'anterior: ' + isnull(CONVERT(varchar,@d_fecha_vigencia,103),'-') + 
       ' nueva: ' + isnull(CONVERT(varchar,@i_fecha_vigencia,103),'-')
    end
  end 
  -- Guarda la trazabilidad de la modificacion 
  if (select COUNT(*) from trazabilidad_monitoreos 
       where cliente = @i_cliente 
      and cuit = @i_cuit 
      and created >= GETDATE() - 1 
      and detalle = @detalle) = 0
  begin
   insert into trazabilidad_monitoreos 
    ( 
    [tabla],
    [id_registro_tabla],
    [created],
    [accion],
    [detalle],
    cliente, 
    [cuit],
    [id_padron],
    seccion_old,
    seccion_new,
    [visible_old],
    [visible_new],
    [fecha_desde_old],
    [fecha_desde_new], 
    [fecha_vigencia_old],
    [fecha_vigencia_new], 
    [user_id])

   values 
    (
    'monitoreos',
    @i_id_tabla, 
    getdate(), 
    'UPDATE',
    @detalle, 
    @i_cliente, 
    @i_cuit,
    @id_padron,
    @d_seccion,
    @i_seccion,
    @d_visible, 
    @i_visible, 
    @d_fecha_desde, 
    @i_fecha_desde, 
    @d_fecha_vigencia,
    @i_fecha_vigencia,
    @i_user_id)
  end
  else 
  begin
   update trazabilidad_monitoreos 
      set seccion_old = seccion_old + ', ' + @d_seccion, 
          seccion_new = seccion_new + ', ' + @i_seccion
    where cliente = @i_cliente 
      and cuit = @i_cuit 
      and created >= GETDATE() - 1 
      and detalle = @detalle
  end 
  FETCH NEXT FROM C_Cursor INTO @d_cliente, @d_cuit,
         @d_seccion,
         @d_fecha_desde,
         @d_fecha_vigencia,
         @d_visible,
         @d_user_id, 
         @d_id_tabla,  

         @i_cliente, @i_cuit ,
         @i_seccion,
         @i_fecha_desde,
         @i_fecha_vigencia,
         @i_visible,
         @i_user_id, 
         @i_id_tabla

 END
 CLOSE C_Cursor
 DEALLOCATE C_Cursor 
END 
GO