dbo.LN_obtener_score_led

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <19/03/2019>
-- Description: Cálculo de Score de cosas relacionadas con Documentos de LED, personalizada
-- =============================================

Si la contraparte está en carga inicial, y aún no están todos los documentos verificados: NO CALCULA EL SCORE.
Si se calcula:
Si verificar_documento = 1 
Si el documento no fue solicitado no lo toma en cuenta en el score
Si el documento aún está en plazo de entrega no lo toma en cuenta en el score.
El documento fue entregado suma el valor del grupo
El documento no entregado no suma el valor de ese grupo o lo manda a killer
*/
/*
exec LN_obtener_score_led 5546348, 29, 716 
*/
/* Prueba general: 
declare @familia int, @contraparte int 
declare @scores table (cliente int, contraparte int, famila int, maximo_posible decimal(7,3), puntos_obtenidos decimal(7,3), score decimal(7,3), descripcion_killer varchar(max),total_doc int, no_pedidos int, en_carga int, si int, no int)

DECLARE db_cursor1 CURSOR FOR
select b.contraparte, b.familia 
 from LN_contrapartes a
 inner join ln_contraparte_familias b on b.contraparte = a.contraparte 
 where a.cliente = 5546348 
OPEN db_cursor1
FETCH NEXT FROM db_cursor1 INTO @contraparte, @familia
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @scores 
exec LN_obtener_score_led 5546348, @familia, @contraparte
    FETCH NEXT FROM db_cursor1 INTO @contraparte, @familia
END
CLOSE db_cursor1
DEALLOCATE db_cursor1
select * from @scores
*/

CREATE OR ALTER PROCEDURE dbo.LN_obtener_score_led
  @cliente int,
  @familia int,
  @contraparte int
AS
BEGIN
 SET NOCOUNT ON;
 
 declare @tipo_doc int, 
   @verificacion_documento bit, 
   @referencia varchar(50), 
   @verificacion_contenido_campo bit, 
   @valor_maximo decimal(6,3),
   @killer bit, 
   @entregado char(2),
   @total decimal(7,3) = null,
   @puntos decimal(7,3) = null,
   @suma decimal(7,3) = null,
   @descripcion_killer varchar(max) = null,
   @item varchar(max)
 DECLARE @pasos table (cliente int, contraparte int, familia int, item int, estado varchar(100), puntos decimal(7,3))
 
 DECLARE @carga_inicial   tinyint = null
 DECLARE @limite_carga_inicial datetime = null 
 declare @total_documentos  int = 0, 
   @total_verificados int = 0,
   @calcular_score int = 0
  
 SELECT @carga_inicial = ISNULL(carga_inicial, 0),
     @limite_carga_inicial = limite_carga_inicial
   FROM LN_contrapartes
  WHERE contraparte = @contraparte
    
 set @total_documentos = (select COUNT(*) from LN_docs where contraparte = @contraparte and aplica = 1 and estado_doc  != 'X')
 set @total_verificados =(select COUNT(*) from LN_docs where contraparte = @contraparte and aplica = 1 and estado_doc = 'V')  
     
 set @calcular_score = iif(@carga_inicial = 1, iif(@total_documentos = @total_verificados, 1, 0), 1)

if (@calcular_score = 1) 
begin 
 DECLARE db_cursor CURSOR FOR
  select tipo_doc,
      verificacion_documento,
      referencia, 
      verificacion_contenido_campo,
      valor_maximo,
      killer 
    from LN_score_led_referencias
    where cliente = @cliente 
     and familia = @familia

 OPEN db_cursor
 FETCH NEXT FROM db_cursor INTO  @tipo_doc,
         @verificacion_documento,
         @referencia, 
         @verificacion_contenido_campo,
         @valor_maximo,
         @killer 
 WHILE @@FETCH_STATUS = 0
 BEGIN
  
 if (@verificacion_documento = 1)
 begin 


 -- 1) Si no se lo pidieron, saltea este documento
            if ((select count(*) 
                   from ln_docs 
                  where contraparte = @contraparte
        and aplica = 1        
        and estado_doc not in ('X')
                    and tipo_doc = @tipo_doc 
                    ) = 0)
            begin
    insert into @pasos 
    select @cliente, @contraparte, @familia, @tipo_doc, 'no se lo pidieron' as estado, null as puntos
    FETCH NEXT FROM db_cursor INTO  @tipo_doc,
         @verificacion_documento,
         @referencia, 
         @verificacion_contenido_campo,
         @valor_maximo,
         @killer 

    continue
   end 
 -- 2) Si se lo pidieron, pero aun no se le vencio el periodo de carga -> saltea este documento
            if ((select count(*) 
                   from ln_docs 
                  where contraparte = @contraparte
        and aplica = 1   
     and vencimiento_carga > getdate()     
        and estado_doc in ('I','N','R')
                    and tipo_doc = @tipo_doc 
                    ) != 0)
            begin
    insert into @pasos 
    select @cliente, @contraparte, @familia, @tipo_doc, 'en periodo carga' as estado, null as puntos
    FETCH NEXT FROM db_cursor INTO  @tipo_doc,
            @verificacion_documento,
            @referencia, 
            @verificacion_contenido_campo,
            @valor_maximo,
            @killer 
    continue
   end 
 -- 3) Se lo pidieron, y ya se le venció el periodo de carga
          -- Todavia no está verificado ==> entregado = NO
            if ((select count(*) 
                    from ln_docs 
                   where contraparte = @contraparte and aplica = 1 
                     and tipo_doc    = @tipo_doc
                     and estado_doc in ('I','N','R')) != 0)
                begin
                    set @entregado = 'NO' 
                end 
                else
                begin
                    set @entregado = 'SI' 
                end  
    set @total = isnull(@total,0) + @valor_maximo
    if (@entregado = 'SI' and ISNULL(@puntos,0) >= 0) 
        -- Si por alguno dio puntos < 0 --> ya es killer, no sigue sumando
        begin
        set @puntos = isnull(@puntos,0) + @valor_maximo
        set @suma = @valor_maximo
        end
    if (@entregado = 'NO' and @killer = 0) 
        set @suma = 0 
    if (@entregado = 'NO' and @killer = 1) 
        begin
     set @puntos = -100
     set @suma = -100
     if (@descripcion_killer is not null) 
        begin
           set @descripcion_killer = @descripcion_killer + '; '
        end 
        set @item = (select nombre_tipo_doc from  ln_tipos_doc where tipo_doc = @tipo_doc) 
        set @descripcion_killer = isnull(@descripcion_killer,'') + @item 
     end 

    insert into @pasos 
    select @cliente, @contraparte, @familia, @tipo_doc, @entregado as estado, @suma as puntos
  end -- del if (@verificacion_documento = 1)
  FETCH NEXT FROM db_cursor INTO  @tipo_doc,
          @verificacion_documento,
          @referencia, 
          @verificacion_contenido_campo,
          @valor_maximo,
          @killer 

 END
 CLOSE db_cursor
 DEALLOCATE db_cursor

 declare @score decimal(10,3)
 set @score = @puntos 
 if (isnull(@puntos,0) >= 0) set @score = (100 * @puntos) / @total 

 declare @total_doc     int = (select count(*) from @pasos) 
 declare @no_pedidos    int = (select count(*) from @pasos where estado = 'no se lo pidieron')
 declare @en_carga      int = (select count(*) from @pasos where estado = 'en periodo carga')
 declare @entregados    int = (select count(*) from @pasos where estado = 'SI')
 declare @no_entregados int = (select count(*) from @pasos where estado = 'NO')
 if (@total_doc = 0 or @total_doc = @no_pedidos) set @score = 100  
 select  @cliente as cliente, @contraparte as contraparte, @familia as famila, 
         @total as maximo_posible, @puntos as puntos_obtenidos, 
      @score as score, @descripcion_killer as descripcion_killer, 
   @total_doc     as 'total_documentos',  
   @no_pedidos    as 'no_pedidos',
   @en_carga      as 'en_carga',
   @entregados    as 'SI_entregados',
   @no_entregados as 'NO_entregados'
 /* para el debug
  select * from @pasos order by estado, item
  */
end 
END
GO