dbo.Imperium_Reporte_Pendientes_Complejidad_Plazo

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
/*

exec Imperium_Reporte_Pendientes_Complejidad_Plazo 'columnas'

*/
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.Imperium_Reporte_Pendientes_Complejidad_Plazo
 -- Add the parameters for the stored procedure here

 @action varchar(20) = null
 

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;


 declare @columna varchar(10)
 declare @codigo varchar(10)
 declare @plazo varchar(50)


 create table #cols (columna VARCHAR(10), plazo varchar(50))

 set @plazo = '>= ' + convert(varchar, dateadd(day,21,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL01', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,16,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,20,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL02', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,11,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,15,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL03', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,6,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,10,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL04', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,0,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,5,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL05', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,-5,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,-1,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL06', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,-10,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,-6,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL07', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,-15,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,-11,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL08', @plazo)

 set @plazo = 'Del ' + convert(varchar, dateadd(day,-20,getdate()), 103) + ' al ' + convert(varchar, dateadd(day,-16,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL09', @plazo)

 set @plazo = '< ' + convert(varchar, dateadd(day,-20,getdate()), 103)
 insert into #cols (columna, plazo) values ('COL10', @plazo)



    -- Insert statements for procedure here
 if (@action is null) begin

  create table #columnas (columna VARCHAR(10), plazo varchar(50), codigo_complejidad varchar(10))

  declare Compl cursor for 
 
  select codigo_complejidad from led_complejidad_doc
  open Compl
  fetch next from Compl into @codigo
  while @@FETCH_STATUS = 0 begin

   declare Columnas cursor for 
   select columna, plazo from #cols
   Open Columnas
   Fetch next from Columnas into @columna, @plazo
   while @@FETCH_STATUS = 0 begin
    insert into #columnas (columna, plazo, codigo_complejidad)
    values (@columna, @plazo, @codigo)
    Fetch next from Columnas into @columna, @plazo
   end
   Close Columnas
   Deallocate Columnas


   fetch next from Compl into @codigo
  end
  close Compl
  deallocate Compl

 
  declare @suma_parcial int = 999
  declare @str_acceso varchar(3) = '0,1'
  declare @str_estado varchar(1) = 'I'
  declare @periodo_desde int = dbo.PeriodoAnual()


  select #columnas.columna, #columnas.codigo_complejidad, #columnas.plazo, ISNULL(DATA.documentos, 0) as total
  from #columnas
 
  left outer join

  (

  SELECT TOTALES.codigo_complejidad, TOTALES.columna, SUM(TOTALES.total_docs) as documentos
  FROM

   (SELECT DOCS.codigo_complejidad, DOCS.tolerancia_verif as plazo_verif, SUM(DOCS.total) AS total_docs, 
     DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) AS diferencia,


     --- SI LA DIFERENCIA ENTRE TOLERANCIA Y LA FECHA DE HOY ES POSITIVA ES ATRASADO 
     --- SI LA DIFERENCIA ENTRE TOLERANCIA Y LA FECHA DE HOY ES NEGATIVA ES EN PLAZO DE VERIFICACION

     CASE
      --WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) <= 0 THEN 'COL1'

      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) < -20 THEN 'COL01'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) < -15 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) >= -20 THEN 'COL02'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) < -10 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) >= -15 THEN 'COL03'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) < -5 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) >= -10 THEN 'COL04'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) <= 0 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) >= -5 THEN 'COL05'      
     
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) > 0 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) <= 5 THEN 'COL06'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) > 5 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) <= 10 THEN 'COL07'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) > 10 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) <= 15 THEN 'COL08'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) > 15 AND DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) <= 20 THEN 'COL09'
      WHEN DATEDIFF(DAY,DOCS.tolerancia_verif,CONVERT(DATE,GETDATE())) > 20 THEN 'COL10'

     END AS columna
   FROM (
      SELECT led_complejidad_doc.codigo_complejidad, DATEADD(DAY, ISNULL(led_contratos.sla_dias, 3), convert(date,led_docs.ingresado)) AS tolerancia_verif, count(*) as total
      FROM led_docs
      INNER JOIN clientes ON led_docs.cliente = clientes.cliente
      INNER JOIN led_contratos ON clientes.cliente = led_contratos.cliente
      INNER JOIN led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
      INNER JOIN led_complejidad_doc on led_tipos_doc.complejidad = led_complejidad_doc.complejidad

      INNER JOIN led_contratistas ON led_docs.contratista = led_contratistas.contratista
      INNER JOIN padrones ON led_contratistas.id_padron = padrones.id_padron
      INNER JOIN led_contratistas led_contratistas_1 ON led_docs.contratista_1 = led_contratistas_1.contratista
      INNER JOIN padrones padrones_1 ON led_contratistas_1.id_padron = padrones_1.id_padron

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

      INNER JOIN led_contratista_multi ON led_docs.contratista = led_contratista_multi.contratista

      INNER JOIN led_cliente_contratista ON
       (led_docs.cliente = led_cliente_contratista.cliente
       AND led_docs.tipo_doc = led_cliente_contratista.tipo_doc
       AND led_contratista_multi.tipo_contratista = led_cliente_contratista.tipo_contratista
       AND led_cliente_contratista.activo = 1)

      INNER JOIN dbo.DelimitedSplit8K(@str_acceso, ',') acceso ON ISNULL(led_cliente_contratista.ci_killer, 0) = acceso.Item
      LEFT OUTER JOIN led_docs_aux aux on (led_docs.doc_id = aux.doc_id AND led_docs.doc_version = aux.doc_version)
   
      WHERE led_docs.led_seccion = 'C'
      AND led_docs.estado_doc = @str_estado
      AND led_docs.doc_id_lista IS NULL
      AND led_docs.periodo >= @periodo_desde
      AND ISNULL(led_docs.doc_version, 0) >= 0
      AND led_cliente_contratista.verificacion = 'F'
      AND (ISNULL(led_docs.aprobacion_parcial, 0) + ISNULL(led_docs.reapertura_por_alta, 0) + ISNULL(led_docs.parcial_actualizado, 0)) <> @suma_parcial
      AND led_docs.contratista = led_docs.contratista_1
      and led_docs.tipo_doc != 734
      GROUP BY led_complejidad_doc.codigo_complejidad, led_contratos.sla_dias, led_docs.ingresado



      UNION ALL

      /* empleados */
      SELECT led_complejidad_doc.codigo_complejidad, DATEADD(DAY, ISNULL(led_contratos.sla_dias, 3), convert(date,led_docs.ingresado)) AS tolerancia_verif, count(*) as total

      FROM led_docs
      INNER JOIN clientes ON led_docs.cliente = clientes.cliente
      INNER JOIN led_contratos ON clientes.cliente = led_contratos.cliente
      INNER JOIN led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
      INNER JOIN led_complejidad_doc on led_tipos_doc.complejidad = led_complejidad_doc.complejidad

      INNER JOIN led_contratistas ON led_docs.contratista = led_contratistas.contratista
      INNER JOIN padrones ON led_contratistas.id_padron = padrones.id_padron
      INNER JOIN led_contratistas led_contratistas_1 ON led_docs.contratista_1 = led_contratistas_1.contratista
      INNER JOIN padrones padrones_1 ON led_contratistas_1.id_padron = padrones_1.id_padron

      INNER JOIN led_empleados ON led_docs.empleado = led_empleados.empleado
      INNER JOIN padrones padrones_e ON led_empleados.id_padron = padrones_e.id_padron

      INNER JOIN led_empleados_periodo ON (led_docs.empleado = led_empleados_periodo.empleado
                AND led_docs.periodo = led_empleados_periodo.periodo)

      INNER JOIN led_cliente_empleado ON
       (led_docs.cliente = led_cliente_empleado.cliente
       AND led_docs.tipo_doc = led_cliente_empleado.tipo_doc
       AND led_empleados.tipo_empleado = led_cliente_empleado.tipo_empleado
       AND led_cliente_empleado.activo = 1)

      INNER JOIN dbo.DelimitedSplit8K(@str_acceso, ',') acceso ON ISNULL(led_cliente_empleado.ci_killer, 0) = acceso.Item

      LEFT OUTER JOIN led_docs_aux aux on (led_docs.doc_id = aux.doc_id AND led_docs.doc_version = aux.doc_version)

      WHERE led_docs.led_seccion = 'E'
      AND led_docs.estado_doc = @str_estado
      AND led_docs.doc_id_lista IS NULL
      AND led_docs.periodo >= @periodo_desde
      AND ISNULL(led_docs.doc_version, 0) >= 0
      AND led_cliente_empleado.verificacion = 'F'
      GROUP BY led_complejidad_doc.codigo_complejidad, led_contratos.sla_dias, led_docs.ingresado



      UNION ALL

      /* vehiculos */
      SELECT led_complejidad_doc.codigo_complejidad, DATEADD(DAY, ISNULL(led_contratos.sla_dias, 3), convert(date,led_docs.ingresado)) AS tolerancia_verif, count(*) as total
      FROM led_docs
      INNER JOIN clientes ON led_docs.cliente = clientes.cliente
      INNER JOIN led_contratos ON clientes.cliente = led_contratos.cliente
      INNER JOIN led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
      INNER JOIN led_complejidad_doc on led_tipos_doc.complejidad = led_complejidad_doc.complejidad

      INNER JOIN led_contratistas ON led_docs.contratista = led_contratistas.contratista
      INNER JOIN padrones ON led_contratistas.id_padron = padrones.id_padron
      INNER JOIN led_contratistas led_contratistas_1 ON led_docs.contratista_1 = led_contratistas_1.contratista
      INNER JOIN padrones padrones_1 ON led_contratistas_1.id_padron = padrones_1.id_padron

      INNER JOIN led_vehiculos ON led_docs.vehiculo = led_vehiculos.vehiculo

      INNER JOIN led_vehiculos_periodo ON (led_docs.vehiculo = led_vehiculos_periodo.vehiculo
                AND led_docs.periodo = led_vehiculos_periodo.periodo)

      INNER JOIN led_cliente_vehiculo ON
       (led_docs.cliente = led_cliente_vehiculo.cliente
       AND led_docs.tipo_doc = led_cliente_vehiculo.tipo_doc
       AND led_vehiculos.tipo_vehiculo = led_cliente_vehiculo.tipo_vehiculo
       AND led_cliente_vehiculo.activo = 1)

      INNER JOIN dbo.DelimitedSplit8K(@str_acceso, ',') acceso ON ISNULL(led_cliente_vehiculo.ci_killer, 0) = acceso.Item
      LEFT OUTER JOIN led_docs_aux aux on (led_docs.doc_id = aux.doc_id AND led_docs.doc_version = aux.doc_version)

      WHERE led_docs.led_seccion = 'V'
      AND led_docs.estado_doc = @str_estado
      AND led_docs.doc_id_lista IS NULL
      AND led_docs.periodo >= @periodo_desde
      AND ISNULL(led_docs.doc_version, 0) >= 0
      AND led_cliente_vehiculo.verificacion = 'F'
      GROUP BY led_complejidad_doc.codigo_complejidad, led_contratos.sla_dias, led_docs.ingresado

      UNION ALL

   
      /* SUBcontratistas */ 
      SELECT led_complejidad_doc.codigo_complejidad, DATEADD(DAY, ISNULL(led_contratos.sla_dias, 3), convert(date,led_docs.ingresado)) AS tolerancia_verif, count(*) as total
      FROM led_docs
      INNER JOIN clientes ON led_docs.cliente = clientes.cliente
      INNER JOIN led_contratos ON clientes.cliente = led_contratos.cliente
      INNER JOIN led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
      INNER JOIN led_complejidad_doc on led_tipos_doc.complejidad = led_complejidad_doc.complejidad

      INNER JOIN led_contratistas ON led_docs.contratista = led_contratistas.contratista
      INNER JOIN padrones ON led_contratistas.id_padron = padrones.id_padron
      INNER JOIN led_contratistas led_contratistas_1 ON led_docs.contratista_1 = led_contratistas_1.contratista
      INNER JOIN padrones padrones_1 ON led_contratistas_1.id_padron = padrones_1.id_padron

      INNER JOIN led_subcontratista_periodo ON (led_docs.contratista = led_subcontratista_periodo.subcontratista
                AND led_docs.periodo = led_subcontratista_periodo.periodo)

      INNER JOIN led_contratista_multi ON led_docs.contratista = led_contratista_multi.contratista

      INNER JOIN led_cliente_contratista ON
       (led_docs.cliente = led_cliente_contratista.cliente
       AND led_docs.tipo_doc = led_cliente_contratista.tipo_doc
       AND led_contratista_multi.tipo_contratista = led_cliente_contratista.tipo_contratista
       AND led_cliente_contratista.activo = 1)

      INNER JOIN dbo.DelimitedSplit8K(@str_acceso, ',') acceso ON ISNULL(led_cliente_contratista.ci_killer, 0) = acceso.Item
      /*INNER JOIN dbo.DelimitedSplit8K(@str_parcial, ',') parciales ON ISNULL(led_docs.aprobacion_parcial, 0) = parciales.Item*/
      LEFT OUTER JOIN led_docs_aux aux on (led_docs.doc_id = aux.doc_id AND led_docs.doc_version = aux.doc_version)

      WHERE led_docs.led_seccion = 'C'
      AND led_docs.estado_doc = @str_estado
      AND led_docs.doc_id_lista IS NULL
      AND led_docs.periodo >= @periodo_desde
      AND ISNULL(led_docs.doc_version, 0) >= 0
      AND led_cliente_contratista.verificacion = 'F'
      AND (ISNULL(led_docs.aprobacion_parcial, 0) + ISNULL(led_docs.reapertura_por_alta, 0) + ISNULL(led_docs.parcial_actualizado, 0)) <> @suma_parcial
      AND led_docs.contratista <> led_docs.contratista_1
      GROUP BY led_complejidad_doc.codigo_complejidad, led_contratos.sla_dias, led_docs.ingresado
   ) DOCS
   GROUP BY DOCS.codigo_complejidad, DOCS.tolerancia_verif
   HAVING DOCS.tolerancia_verif IS NOT NULL) TOTALES
  GROUP BY TOTALES.codigo_complejidad, TOTALES.columna
  -- ORDER BY TOTALES.codigo_complejidad, TOTALES.columna

  ) DATA on (#columnas.codigo_complejidad = DATA.codigo_complejidad and #columnas.columna = DATA.columna)

  order by #columnas.codigo_complejidad, #columnas.columna


  drop table #columnas


 end else

 begin

  select * from #cols order by columna

 end


 drop table #cols
END
GO