dbo.monitoreos_trazabilidad

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec [monitoreos_trazabilidad] '2020-04-15', 1
*/
CREATE OR ALTER PROCEDURE dbo.monitoreos_trazabilidad
@yesterday date = null,
@solo_score bit = 1, 
@r_cliente int = null 

AS
BEGIN
 
 declare @id_trazabilidad int,
   @texto varchar(1000) ,
   @cliente int ,
   @accion varchar(100) ,
   @id_plataforma varchar(10) ,
   @contraparte int ,
   @user_id int ,
   @created datetime ,
   @fecha_old datetime ,
   @valor_numerico_old float ,
   @valor_texto_old varchar(100) ,
   @valor_memo_old varchar(max) ,
   @valor_fecha_old datetime,
   @detalle_killer_old varchar(max),
   @detalle_puntos_old varchar(max),
   @fecha_new datetime ,
   @valor_numerico_new float ,
   @valor_texto_new varchar(100),
   @valor_memo_new varchar(max),
   @valor_fecha_new datetime ,
   @detalle_killer_new varchar(max),
   @detalle_puntos_new varchar(max)


  declare @r_id_producto int = (select producto from productos where sigla = 'MIN')
  if (@yesterday is null) begin set @yesterday = GETDATE() - 1 end 
 
        declare @respuesta table (cliente int, nombre_cliente varchar(200), email varchar(200), tiene char(2), 
  contraparte int, nombre_contraparte varchar(200), cuit varchar(20), familia varchar(100), fecha date, 
  novedades varchar(max),
  antes varchar(max), 
  motivos_antes varchar(max),
  ahora varchar(max),
  motivos_ahora varchar(max))

       --declare @r_cliente int
  declare @r_user_id int
  declare @r_nombre_cliente varchar(200)
  declare @r_mail varchar(200)
  declare @novedades varchar(max) = '' 
  declare @antes varchar(max) = '' 
  declare @motivos_antes varchar(max) = '' 
  declare @ahora varchar(max) = '' 
  declare @motivos_ahora varchar(max) = '' 
  declare @valor_old varchar(50) = '' 
  declare @valor_new varchar(50) = '' 


  declare @cant_cliente int = 0

  declare @nombre_contraparte varchar(200), @cuit varchar(20), @familia varchar(100)

  if (@r_cliente is null)
   DECLARE bn CURSOR FOR
    select distinct cliente
      from clientes_productos 
      where id_producto = @r_id_producto
     and activo = 1
      order by cliente

   else 
    DECLARE bn CURSOR FOR
    select @r_cliente
    

  OPEN bn 
  
  Fetch next from bn INTO @r_cliente 

  WHILE (@@FETCH_STATUS <> -1 )
  
  BEGIN
      --select @r_cliente
   set @r_nombre_cliente = (select nombre_cliente from clientes where cliente = @r_cliente) 

   set @cant_cliente = 0  
   declare cnov CURSOR FOR
        select  id_trazabilidad,
       texto,
       cliente,
       accion,
       id_plataforma,
       contraparte,
       user_id,
       created,
       fecha_old,
       valor_numerico_old,
       valor_texto_old,
       convert(varchar(max),valor_memo_old),
       valor_fecha_old,
       detalle_killer_old,
       detalle_puntos_old,
       fecha_new,
       valor_numerico_new,
       valor_texto_new,
       convert(varchar(max),valor_memo_new),
       valor_fecha_new,
       detalle_killer_new,
       detalle_puntos_old
        from trazabilidad
       where trazabilidad.contraparte in  
         (select ln_contrapartes.contraparte from ln_contrapartes 
         inner join ln_gestiones on ln_gestiones.contraparte = ln_contrapartes.contraparte
           where ln_contrapartes.cliente = @r_cliente
          and ln_contrapartes.activo = 1
         and ln_gestiones.desde <= getdate() and ln_gestiones.hasta >= getdate())
        and trazabilidad.created >= @yesterday
        and trazabilidad.id_plataforma = 'INDICADOR'
        --and (@solo_score = 0 or trazabilidad.accion like 'SCORE%')
        order by contraparte, created desc 
   OPEN cnov
   Fetch next from cnov INTO  @id_trazabilidad,
          @texto,
          @cliente,
          @accion,
          @id_plataforma,
          @contraparte,
          @user_id,
          @created,
          @fecha_old,
          @valor_numerico_old,
          @valor_texto_old,
          @valor_memo_old,
          @valor_fecha_old,
          @detalle_killer_old,
          @detalle_puntos_old,
          @fecha_new,
          @valor_numerico_new,
          @valor_texto_new,
          @valor_memo_new,
          @valor_fecha_new,
          @detalle_killer_new,
          @detalle_puntos_new
          
   WHILE (@@FETCH_STATUS <> -1 )
   BEGIN  
       declare @ok varchar(2) = 'ok'
    if (@solo_score = 1 and @accion not like 'SCORE%') 
    --> Si solo_score = 1 solo muestro cambios de score
    begin
      set @ok = 'no'
    end

    if (@ok = 'ok' and @solo_score = 1 and @accion != 'SCORE_FINAL')
    --> Si solo_score = 1 muestro el SCORE_FINAL 
    --> y de los otros scores, solo muestro los que juegan en el score Final, 
    --> o sea ponderacion > 0
    begin
     declare @ponderacion float = 0
     select @ponderacion = LN_cliente_productos.ponderacion 
      from LN_contraparte_indicadores
     inner join ln_gestiones on ln_gestiones.contraparte = ln_contraparte_indicadores.contraparte
     inner join LN_indicadores on ln_indicadores.indicador = LN_contraparte_indicadores.indicador
     inner join ln_cliente_productos on (ln_cliente_productos.producto = ln_indicadores.producto) 
     where LN_contraparte_indicadores.contraparte = @contraparte 
       and LN_contraparte_indicadores.visible = 1
       and LN_contraparte_indicadores.gestion = ln_gestiones.gestion
       and LN_indicadores.clave_indicador = @accion
       and ln_cliente_productos.cliente = @cliente
       and ln_gestiones.desde <= getdate() 
       and ln_gestiones.hasta >= getdate() 
     if (@ponderacion = 0) set @ok = 'no'
    end

    if (@ok = 'ok')
    begin
    select @nombre_contraparte = nombre_persona, @cuit = identificacion, @familia = nombre_familia
           from ln_contrapartes 
        inner join padrones on padrones.id_padron = ln_contrapartes.id_padron
           inner join ln_contraparte_familias famc on famc.contraparte = ln_contrapartes.contraparte 
        inner join ln_familias fam on fam.familia = famc.familia
     where ln_contrapartes.contraparte = @contraparte
    
    set @cant_cliente = @cant_cliente + 1
    set @ahora = '' 
    set @motivos_ahora = '' 
    set @antes = '' 
    set @motivos_antes = '' 

    set @novedades = ''

    if (@accion = 'SCORE_FINAL') 
    begin
     set @valor_memo_old = '' -- porque tiene el color (que no es valor del indicador)
     set @valor_memo_new = '' 
    end 
    
    if (@valor_numerico_old is null) set @valor_old = ''  
    if (@valor_numerico_old < -100)  set @valor_old = '' 
    if (@valor_numerico_old = -100)  set @valor_old = 'N/A'
    if (@valor_numerico_old > -100)  set @valor_old = format(@valor_numerico_old, '#############.##')

    if (@valor_numerico_new is null) set @valor_new = ''  
    if (@valor_numerico_new < -100)  set @valor_new = '' 
    if (@valor_numerico_new = -100)  set @valor_new = 'N/A'
    if (@valor_numerico_new > -100)  set @valor_new = format(@valor_numerico_new, '#############.##')

    set @novedades = @texto
    if (@valor_numerico_old is not null or 
     @valor_texto_old is not null or 
     @valor_memo_old is not null or 
     @valor_fecha_old is not null) 
     set @antes = @valor_old + ' ' +
     isnull(@valor_texto_old,'') + ' ' +
     isnull(@valor_memo_old,'') +  ' ' +
     isnull(convert(varchar(max),@valor_fecha_old,103),'') 
       if (@valor_numerico_new is not null or 
     @valor_texto_new is not null or 
     @valor_memo_new is not null or 
     @valor_fecha_new is not null) 
     set @ahora =  @valor_new + ' ' +
     isnull(@valor_texto_new,'') + ' ' +
     isnull(@valor_memo_new,'') +  ' ' +
     isnull(convert(varchar(max),@valor_fecha_new,103),'')  

    if (@detalle_killer_old is not null)
      set @motivos_antes =  'Motivos Rechazo: ' + @detalle_killer_old 
      
    if (@detalle_killer_new is not null)
      set @motivos_ahora = 'Motivos Rechazo: ' + @detalle_killer_new

          if (@detalle_puntos_old is not null)
           if (@motivos_antes = '') 
        set @motivos_antes = 'Motivos Puntaje: ' + @detalle_puntos_old 
        else
        set @motivos_antes = @antes + '; Motivos Puntaje: ' + @detalle_puntos_old 

       
    if (@detalle_puntos_old is not null)
           if (@motivos_ahora = '') 
           set @motivos_ahora = 'Motivos Puntaje: ' + @detalle_puntos_new
        else
           set @motivos_ahora = @motivos_ahora + '; Motivos Puntaje: ' + @detalle_puntos_new

    
    set @antes = REPLACE(@antes,'    ',' ')
    set @antes = REPLACE(@antes,'   ',' ')
    set @antes = REPLACE(@antes,'  ',' ')

    set @ahora = REPLACE(@ahora,'    ',' ')
    set @ahora = REPLACE(@ahora,'   ',' ')
    set @ahora = REPLACE(@ahora,'  ',' ')
       
    set @ahora = ltrim(rtrim(@ahora))
    set @antes = ltrim(rtrim(@antes)) 
    set @novedades = ltrim(rtrim(@novedades))

    insert into @respuesta values (@r_cliente, @r_nombre_cliente, null, 'SI',
                                         @contraparte, @nombre_contraparte, @cuit, @familia,
              convert(date, @created), @novedades, @antes, @motivos_antes, 
              @ahora, @motivos_ahora)
    end 
    Fetch next from cnov INTO @id_trazabilidad,
          @texto,
          @cliente,
          @accion,
          @id_plataforma,
          @contraparte,
          @user_id,
          @created,
          @fecha_old,
          @valor_numerico_old,
          @valor_texto_old,
          @valor_memo_old,
          @valor_fecha_old,
          @detalle_killer_old,
          @detalle_puntos_old,
          @fecha_new,
          @valor_numerico_new,
          @valor_texto_new,
          @valor_memo_new,
          @valor_fecha_new,
          @detalle_killer_new,
          @detalle_puntos_new

   END 
   CLOSE cnov
   DEALLOCATE cnov  
  
   DECLARE cmail CURSOR FOR
   select distinct email from users 
    where user_id in (select user_id from usuarios_productos 
                       where cliente = @r_cliente 
           and id_producto = @r_id_producto
           and activo = 1)
    and email is not null
    and email like '%@%'
   
   declare @tiene char(2)
   declare @mails varchar(max) = ''
   
   OPEN cmail
   Fetch next from cmail INTO  @r_mail
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@mails = '') 
         set @mails = @r_mail
     else
         set @mails = @mails + ';' + @r_mail
     
    
     FETCH next from cmail INTO @r_mail
    END 
    CLOSE cmail
    DEALLOCATE cmail
    if (@mails = '')
    begin
     set @mails = 'marita.soria@gmail.com'
    end 
    set @mails = @mails + ';' + 'marita.soria@gmail.com'
             if (@cant_cliente = 0) 
       begin 
      insert into @respuesta values (@r_cliente, @r_nombre_cliente, @mails,'NO',
                                     null,null,null,null,null,null,null,null,null,null)
       end 
        else 
       begin
      update @respuesta 
         set email = @mails
         where cliente = @r_cliente     
          end 
   

   Fetch next from bn INTO @r_cliente

  END            
  CLOSE bn
  DEALLOCATE bn 

  select * from @respuesta
END

GO