dbo.LN_status_impe_24012022

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
-- =============================================
-- Author: <María Inés Soria>
-- Create date: <30/09/2019>
-- Description: Cálculo de Status imperium 
-- =============================================

Se necesita generar un esquema default del status de los contratistas de imperium para todos los clientes que no tengan personalización. El esquema de valores default de imperium en Led debe ser el siguiente

Último mes de control (dbo.Periodovencido()) todo presentado y verificado (V) al 100% -- VERDE
Último mes de control (dbo.Periodovencido()) documentación pendiente de ingresar (N) o pendiente de verificar (I) o RechazaDa (R) -- GRIS en tolerancia
Documentación pendiente de ingresar o rechazada de los períodos dbo.Periodovencido() y dbo.PeriodoMenosDos() (o sea atrasos entre 30 y 60 días de control y no del la fecha del reloj) -- AMARILLO
Documentación pendiente de ingresar o rechazada de los menor en número al dbo.PeriodoMenosDos() (o sea atrasos mayores 60 días de control y no del la fecha del reloj) -- ROJO


DECLARE @valor decimal(10,2)
EXEC [LN_status_impe_24012022]  159680, @valor output
select @valor as valor

*/
CREATE OR ALTER PROCEDURE dbo.LN_status_impe_24012022 
  @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, aniomes int, 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, 
   SUBSTRING(CONVERT(VARCHAR,DATA.PERIODO),1,4) + SUBSTRING(CONVERT(VARCHAR,DATA.PERIODO),5,2) AS aniomes,
   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

 declare @periodo_vencido date = convert(date,SUBSTRING(CONVERT(VARCHAR,dbo.PeriodoVencido()),1,4) + '-' + SUBSTRING(CONVERT(VARCHAR,dbo.PeriodoVencido()),5,2) + '-01' )

 --update @datos set dias = DATEDIFF(day,hasta,convert(date, getdate()))
 update @datos set dias = DATEDIFF(MONTH,desde,@periodo_vencido) * 30

select * from @datos 
/*
select dbo.PeriodoVencido()
select dbo.PeriodoAnual()
select dbo.PeriodoMenosDos() 

select * from LN_dias_alertas where tabla like 'impe_statu%'
*/
 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
 -- Todo verificado ultimo mes 
  insert into @ln_dias_alertas values (0,100) -- entre 0 y 30 
 -- Pendientes de verificar 
  insert into @ln_dias_alertas values (1,3) -- menos de 30 
  insert into @ln_dias_alertas values (30,2) -- entre 30 y 60
  insert into @ln_dias_alertas values (60,1) -- mas de 60 
 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
 set @valor_final = null 
 
 if not exists (select * from @datos)
 begin 
  if not exists (select * from led_docs WHERE led_docs.contratista_1 = @p_contratista and estado_doc != 'X') -- No se lo solicita documentacion 
  begin 
   set @valor_final = null 
  end 
  else 
  begin 
   set @valor_final = 100
  end 
 end 

 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