dbo.LN_status_impe_25012022

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <30/09/2019>
-- Description: Cálculo de Score de datos aportados por el cliente
-- =============================================
DECLARE @valor decimal(10,2)
EXEC [LN_status_impe]  107154, @valor output
select @valor as valor

*/
CREATE OR ALTER PROCEDURE dbo.LN_status_impe_25012022 
  @contraparte int,
  @valor decimal(10,2) = null output
AS
BEGIN
 SET NOCOUNT ON;

 declare @cliente int = (select cliente from ln_contrapartes where contraparte = @contraparte)
 declare @p_contratista int = (select id_imperium from ln_contrapartes where contraparte = @contraparte)

 declare @valor_final decimal(10,2) = null 

 declare @datos table (desde date, hasta date, dias int, cantidad int) 
 insert into @datos 
 SELECT SUBSTRING(CONVERT(VARCHAR,DATA.PERIODO),1,4) + '-' + SUBSTRING(CONVERT(VARCHAR,DATA.PERIODO),5,2) + '-01' AS PERIODO, 
     DATEADD(day,-1,
     (DATEADD(month,+1,CONVERT(DATE,SUBSTRING(CONVERT(VARCHAR,DATA.PERIODO),1,4) + '-' + SUBSTRING(CONVERT(VARCHAR,DATA.PERIODO),5,2) + '-01'))))
      AS FECHA_LIMITE, 
   null, 
        SUM(DATA.total_docs) AS cantidad
 FROM (
   SELECT led_docs.periodo, COUNT(*) AS total_docs

   FROM led_docs INNER JOIN led_contratista_periodo ON
    (led_docs.contratista = led_contratista_periodo.contratista
     AND led_docs.periodo = led_contratista_periodo.periodo)

   INNER JOIN

    (SELECT led_cliente_contratista.tipo_doc
    FROM led_cliente_contratista INNER JOIN led_contratistas
     ON led_cliente_contratista.cliente = led_contratistas.cliente
    INNER JOIN led_contratista_multi ON
     (led_contratistas.contratista = led_contratista_multi.contratista
     AND led_cliente_contratista.tipo_contratista = led_contratista_multi.tipo_contratista)
    WHERE led_contratistas.contratista = @p_contratista
    AND led_cliente_contratista.requiere_upload = 1
    AND led_cliente_contratista.activo = 1
    AND led_contratista_multi.activo = 1
    AND led_contratista_multi.vigencia_desde <= GETDATE()
    AND (led_contratista_multi.vigencia_hasta IS NULL OR led_contratista_multi.vigencia_hasta > GETDATE())
    GROUP BY led_cliente_contratista.tipo_doc) CONFIG_TC ON led_docs.tipo_doc = CONFIG_TC.tipo_doc

   WHERE led_docs.contratista_1 = @p_contratista
   AND led_docs.status_doc != 'V'
   AND led_docs.periodo BETWEEN dbo.PeriodoAnual() AND dbo.PeriodoVencido()
   AND led_docs.led_seccion = 'C'
   AND led_docs.doc_id_lista IS NULL
   GROUP BY led_docs.periodo


   UNION ALL


   SELECT led_docs.periodo, COUNT(*) AS total_docs
   FROM led_docs 
   INNER JOIN led_empleados_periodo ON
    (led_docs.empleado = led_empleados_periodo.empleado
     AND led_docs.periodo = led_empleados_periodo.periodo)
   INNER JOIN

    (SELECT led_cliente_empleado.tipo_doc
    FROM led_cliente_empleado INNER JOIN led_empleados
     ON led_cliente_empleado.cliente = led_empleados.cliente
    INNER JOIN led_empleado_multi ON
     (led_empleados.empleado = led_empleado_multi.empleado
     AND led_cliente_empleado.tipo_empleado = led_empleado_multi.tipo_empleado)
    WHERE led_empleados.contratista_1 = @p_contratista
    AND led_cliente_empleado.requiere_upload = 1
    AND led_cliente_empleado.activo = 1
    AND led_empleado_multi.activo = 1
    AND led_empleado_multi.vigencia_desde <= GETDATE()
    AND (led_empleado_multi.vigencia_hasta IS NULL OR led_empleado_multi.vigencia_hasta > GETDATE())
    GROUP BY led_cliente_empleado.tipo_doc) CONFIG_TE ON led_docs.tipo_doc = CONFIG_TE.tipo_doc

   WHERE led_docs.contratista_1 = @p_contratista
   AND led_docs.status_doc != 'V'
   AND led_docs.periodo BETWEEN dbo.PeriodoAnual() AND dbo.PeriodoVencido()
   AND led_docs.led_seccion = 'E'
   AND led_docs.doc_id_lista IS NULL
   GROUP BY led_docs.periodo


   UNION ALL


   SELECT led_docs.periodo, COUNT(*) AS total_docs
   FROM led_docs 
   INNER JOIN led_vehiculos_periodo ON
    (led_docs.vehiculo = led_vehiculos_periodo.vehiculo
     AND led_docs.periodo = led_vehiculos_periodo.periodo)
   INNER JOIN

    (SELECT led_cliente_vehiculo.tipo_doc
    FROM led_cliente_vehiculo 
    INNER JOIN led_vehiculos ON 
      (led_cliente_vehiculo.cliente = led_vehiculos.cliente
      AND led_cliente_vehiculo.tipo_vehiculo = led_vehiculos.tipo_vehiculo)
    WHERE led_vehiculos.contratista_1 = @p_contratista
    AND led_cliente_vehiculo.requiere_upload = 1
    AND led_cliente_vehiculo.activo = 1
    GROUP BY led_cliente_vehiculo.tipo_doc) CONFIG_TV ON led_docs.tipo_doc = CONFIG_TV.tipo_doc

   WHERE led_docs.contratista_1 = @p_contratista
   AND led_docs.status_doc != 'V'
   AND led_docs.periodo BETWEEN dbo.PeriodoAnual() AND dbo.PeriodoVencido()
   AND led_docs.led_seccion = 'V'
   AND led_docs.doc_id_lista IS NULL
   GROUP BY led_docs.periodo

   
   UNION ALL


   SELECT led_docs.periodo, COUNT(*) AS total_docs
   FROM led_docs 
   INNER JOIN led_subcontratista_periodo ON
    (led_docs.contratista = led_subcontratista_periodo.subcontratista
     AND led_docs.periodo = led_subcontratista_periodo.periodo)
   INNER JOIN

    (SELECT led_cliente_contratista.tipo_doc
    FROM led_cliente_contratista 
    INNER JOIN led_contratistas ON 
      (led_cliente_contratista.cliente = led_contratistas.cliente
      AND led_cliente_contratista.tipo_contratista = led_contratistas.tipo_contratista)
    WHERE led_contratistas.contratista_1 = @p_contratista
    AND led_contratistas.contratista_1 <> led_contratistas.contratista
    AND led_cliente_contratista.requiere_upload = 1
    AND led_cliente_contratista.activo = 1
    GROUP BY led_cliente_contratista.tipo_doc) CONFIG_TS ON led_docs.tipo_doc = CONFIG_TS.tipo_doc

   WHERE led_docs.contratista_1 = @p_contratista
   AND led_docs.status_doc != 'V'
   AND led_docs.periodo BETWEEN dbo.PeriodoAnual() AND dbo.PeriodoVencido()
   AND led_docs.led_seccion = 'C'
   AND led_docs.doc_id_lista IS NULL
   GROUP BY led_docs.periodo

 ) DATA

 GROUP BY DATA.periodo
 ORDER BY DATA.periodo

 update @datos set dias = DATEDIFF(day,hasta,convert(date, getdate()))


--select * from @datos 

 declare @ln_dias_alertas table (dias int, puntos int) 

 if (not exists (select * from LN_dias_alertas where cliente = @cliente and tabla = 'impe_status')) 
 begin
  insert into @ln_dias_alertas values (0,4)
  insert into @ln_dias_alertas values (90,3)
  insert into @ln_dias_alertas values (150,2)
  insert into @ln_dias_alertas values (180,1)
 end 
 else 
 begin 
  insert into @ln_dias_alertas 
  select dias, puntos from LN_dias_alertas where cliente = @cliente and tabla = 'impe_status' order by dias desc 
 end 
  
 DECLARE C_cursor CURSOR FOR
  select dias, puntos 
    from @ln_dias_alertas 
   order by dias desc 

 OPEN C_cursor
 declare @total int, @dias int, @puntos int
 if not exists (select * from @datos)
 set @valor_final = null 
 else set @valor_final = 1


 FETCH NEXT FROM C_cursor INTO @dias, @puntos 
 WHILE @@fetch_status = 0
 BEGIN 
  set @total = (select SUM(cantidad) from @datos where dias >= @dias) 
  if (@total > 0)
  begin
   set @valor_final = @puntos 
   break; 
  end 
  FETCH NEXT FROM C_cursor INTO @dias, @puntos 
 END 
 CLOSE C_cursor
 DEALLOCATE C_cursor 
 set @valor = @valor_final
END 
GO