dbo.Imperium_vista_Historial_Def

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.Imperium_vista_Historial_Def 
 -- Add the parameters for the stored procedure here

 /* Parámetro contratista 1 no puede ser nulo */
 @p_contratista_1           int,
 @p_status_doc            varchar(20),
 @p_contratista            int,
 @p_periodo_desde           int,
 @p_periodo_hasta           int,
 @p_led_seccion            varchar(1),
 @p_acceso             tinyint


 /*
 
  @p_contratista : para pedir documentación de un subcontratista
 
  @p_status_doc es la lista separada por "," de estados de documentos a seleccionar, 
  representada cada opción por 1 letra:
 
  E = vencidos
  N = pendientes de carga
  R = rechazados
  P = verificados parciales
  I = ingresados pendientes de verificar
  V = verificados 
 
  @p_acceso, si > 0 filtra por documentos killer de control de acceso
 
  */

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

 SET @cliente = (select led_contratistas.cliente from led_contratistas where led_contratistas.contratista = @p_contratista_1)

    -- Insert statements for procedure here
 IF (@p_contratista_1 > 0 AND @p_status_doc IS NOT NULL) 
 BEGIN
  /* Cota máxima 12 meses */
  IF (@p_periodo_desde IS NULL) 
  BEGIN
   SET @p_periodo_desde = dbo.PeriodoAnual()
  END


  DECLARE @acceso varchar(5) = '0,1'
  IF (@p_acceso > 0) 
  begin
   SET @acceso = '1'
  end


         SELECT led_docs.doc_id, led_docs.cliente, led_docs.contratista_1, led_docs.contratista,
           led_docs.led_seccion, led_docs.periodo, led_docs.fecha_tolerancia, 
           led_docs.fecha_vigencia, led_docs.aprobacion_parcial, led_docs.status_doc,
           led_docs.tipo_doc, led_tipos_doc.nombre_tipo_doc,
           padrones.nombre_persona as empresa, padrones.identificador, padrones.numero,
           padrones_1.nombre_persona as nombre_contratista,
         CASE WHEN led_docs.status_doc = 'R' THEN 1
           WHEN led_docs.status_doc = 'E' THEN 2
           WHEN led_docs.status_doc = 'P' THEN 3
           WHEN led_docs.status_doc = 'N' THEN 4
           WHEN led_docs.status_doc = 'I' THEN 5
           WHEN led_docs.status_doc = 'V' THEN 6 END AS orden_status
         FROM led_docs
         INNER JOIN led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
         INNER JOIN dbo.DelimitedSplit8K(@p_status_doc, ',') estados ON led_docs.status_doc = estados.Item
         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_cliente_contratista ON (led_contratistas.tipo_contratista = led_cliente_contratista.tipo_contratista
                    AND led_docs.tipo_doc = led_cliente_contratista.tipo_doc
                    AND led_cliente_contratista.cliente = @cliente
                    AND led_cliente_contratista.ci_killer IN (select item from dbo.DelimitedSplit8K(@acceso, ',')))
         
         INNER JOIN led_contratista_periodo ON (led_contratistas_1.contratista = led_contratista_periodo.contratista 
                   AND led_docs.periodo = led_contratista_periodo.periodo)

         WHERE led_docs.contratista_1 = @p_contratista_1
         AND led_contratistas.contratista_1 = led_contratistas.contratista
         AND led_docs.periodo >= @p_periodo_desde
         AND led_docs.led_seccion = 'C'

         UNION

         SELECT led_docs.doc_id, led_docs.cliente, led_docs.contratista_1, led_docs.contratista,
           led_docs.led_seccion, led_docs.periodo, led_docs.fecha_tolerancia, 
           led_docs.fecha_vigencia, led_docs.aprobacion_parcial, led_docs.status_doc,
           led_docs.tipo_doc, led_tipos_doc.nombre_tipo_doc,
           padrones.nombre_persona as empresa, padrones.identificador, padrones.numero,
           padrones_1.nombre_persona as nombre_contratista,
         CASE WHEN led_docs.status_doc = 'R' THEN 1
           WHEN led_docs.status_doc = 'E' THEN 2
           WHEN led_docs.status_doc = 'P' THEN 3
           WHEN led_docs.status_doc = 'N' THEN 4
           WHEN led_docs.status_doc = 'I' THEN 5
           WHEN led_docs.status_doc = 'V' THEN 6 END AS orden_status
         FROM led_docs
         INNER JOIN led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
         INNER JOIN dbo.DelimitedSplit8K(@p_status_doc, ',') estados ON led_docs.status_doc = estados.Item
         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_cliente_contratista ON (led_contratistas.tipo_contratista = led_cliente_contratista.tipo_contratista
                    AND led_docs.tipo_doc = led_cliente_contratista.tipo_doc
                    AND led_cliente_contratista.cliente = @cliente
                    AND led_cliente_contratista.ci_killer IN (select item from dbo.DelimitedSplit8K(@acceso, ',')))
         
         INNER JOIN led_subcontratista_periodo ON (led_contratistas.contratista = led_subcontratista_periodo.subcontratista 
                   AND led_docs.periodo = led_subcontratista_periodo.periodo)

         WHERE led_docs.contratista_1 = @p_contratista_1
         AND led_contratistas.contratista_1 <> led_contratistas.contratista
         AND led_docs.periodo >= @p_periodo_desde
         AND led_docs.led_seccion = 'C'



         UNION

         SELECT NULL AS doc_id, led_contratistas_1.cliente, led_contratistas.contratista_1,
            led_contratistas.contratista, EMPLEADOS.led_seccion, EMPLEADOS.periodo,
            NULL AS fecha_tolerancia, NULL AS fecha_vigencia, NULL AS aprobacion_parcial, 
            EMPLEADOS.status_doc, EMPLEADOS.tipo_doc, led_tipos_doc.nombre_tipo_doc,
            padrones.nombre_persona as empresa, padrones.identificador, padrones.numero,
            padrones_1.nombre_persona as nombre_contratista,
          CASE WHEN EMPLEADOS.status_doc = 'R' THEN 1
            WHEN EMPLEADOS.status_doc = 'E' THEN 2
            WHEN EMPLEADOS.status_doc = 'P' THEN 3
            WHEN EMPLEADOS.status_doc = 'N' THEN 4
            WHEN EMPLEADOS.status_doc = 'I' THEN 5
            WHEN EMPLEADOS.status_doc = 'V' THEN 6 END AS orden_status

         FROM
           (SELECT DOC_EMPLEADOS.tipo_doc, DOC_EMPLEADOS.led_seccion, DOC_EMPLEADOS.contratista_1,
           DOC_EMPLEADOS.contratista, DOC_EMPLEADOS.periodo, MAX(DOC_EMPLEADOS.estado_value) AS max_estado,
           CASE
            WHEN MAX(DOC_EMPLEADOS.estado_value) = 10 THEN 'R'
            WHEN MAX(DOC_EMPLEADOS.estado_value) = 7 THEN 'E'
            WHEN MAX(DOC_EMPLEADOS.estado_value) = 5 THEN 'N'
            WHEN MAX(DOC_EMPLEADOS.estado_value) = 3 THEN 'I'
            WHEN MAX(DOC_EMPLEADOS.estado_value) = 1 THEN 'V'
           END AS status_doc   
           FROM
            (select led_docs.doc_id, led_docs.tipo_doc, led_docs.status_doc, led_docs.led_seccion,
               led_docs.contratista_1, led_docs.contratista, led_docs.periodo,
             CASE 
              WHEN led_docs.status_doc = 'R' THEN 10
              WHEN led_docs.status_doc = 'E' THEN 7
              WHEN led_docs.status_doc = 'N' THEN 5
              WHEN led_docs.status_doc = 'I' THEN 3
              WHEN led_docs.status_doc = 'V' THEN 1
             END AS estado_value
            from led_docs
            inner join dbo.DelimitedSplit8K(@p_status_doc, ',') estados ON led_docs.status_doc = estados.Item

            --inner join dbo.DelimitedSplit8K(@lista_tipos_doc, ',') tipos ON led_docs.tipo_doc = tipos.Item
            INNER JOIN led_empleados ON led_docs.empleado = led_empleados.empleado
            INNER JOIN led_cliente_empleado ON (led_empleados.tipo_empleado = led_cliente_empleado.tipo_empleado
                       AND led_docs.tipo_doc = led_cliente_empleado.tipo_doc
                       AND led_cliente_empleado.cliente = @cliente
                       AND led_cliente_empleado.ci_killer IN (select item from dbo.DelimitedSplit8K(@acceso, ',')))

            where led_docs.contratista_1 = @p_contratista_1
            and led_docs.periodo >= @p_periodo_desde
            and led_docs.led_seccion = 'E') DOC_EMPLEADOS
           GROUP BY DOC_EMPLEADOS.tipo_doc, DOC_EMPLEADOS.led_seccion, DOC_EMPLEADOS.contratista_1,
             DOC_EMPLEADOS.contratista, DOC_EMPLEADOS.periodo) EMPLEADOS

         INNER JOIN led_tipos_doc ON EMPLEADOS.tipo_doc = led_tipos_doc.tipo_doc
         INNER JOIN led_contratistas AS led_contratistas_1 ON EMPLEADOS.contratista_1 = led_contratistas_1.contratista
         INNER JOIN padrones AS padrones_1 ON led_contratistas_1.id_padron = padrones_1.id_padron
         INNER JOIN led_contratistas ON EMPLEADOS.contratista = led_contratistas.contratista
         INNER JOIN padrones ON led_contratistas.id_padron = padrones.id_padron

         UNION

         SELECT NULL AS doc_id, led_contratistas_1.cliente, led_contratistas.contratista_1,
            led_contratistas.contratista, VEHICULOS.led_seccion, VEHICULOS.periodo,
            NULL AS fecha_tolerancia, NULL AS fecha_vigencia, NULL AS aprobacion_parcial, 
            VEHICULOS.status_doc, VEHICULOS.tipo_doc, led_tipos_doc.nombre_tipo_doc,
            padrones.nombre_persona as empresa, padrones.identificador, padrones.numero,
            padrones_1.nombre_persona as nombre_contratista,
          CASE WHEN VEHICULOS.status_doc = 'R' THEN 1
            WHEN VEHICULOS.status_doc = 'E' THEN 2
            WHEN VEHICULOS.status_doc = 'P' THEN 3
            WHEN VEHICULOS.status_doc = 'N' THEN 4
            WHEN VEHICULOS.status_doc = 'I' THEN 5
            WHEN VEHICULOS.status_doc = 'V' THEN 6 END AS orden_status

         FROM
           (SELECT DOC_VEHICULOS.tipo_doc, DOC_VEHICULOS.led_seccion, DOC_VEHICULOS.contratista_1,
           DOC_VEHICULOS.contratista, DOC_VEHICULOS.periodo, MAX(DOC_VEHICULOS.estado_value) AS max_estado,
           CASE
            WHEN MAX(DOC_VEHICULOS.estado_value) = 10 THEN 'R'
            WHEN MAX(DOC_VEHICULOS.estado_value) = 7 THEN 'E'
            WHEN MAX(DOC_VEHICULOS.estado_value) = 5 THEN 'N'
            WHEN MAX(DOC_VEHICULOS.estado_value) = 3 THEN 'I'
            WHEN MAX(DOC_VEHICULOS.estado_value) = 1 THEN 'V'
           END AS status_doc   
           FROM
            (select led_docs.doc_id, led_docs.tipo_doc, led_docs.status_doc, led_docs.led_seccion,
               led_docs.contratista_1, led_docs.contratista, led_docs.periodo,
             CASE 
              WHEN led_docs.status_doc = 'R' THEN 10
              WHEN led_docs.status_doc = 'E' THEN 7
              WHEN led_docs.status_doc = 'N' THEN 5
              WHEN led_docs.status_doc = 'I' THEN 3
              WHEN led_docs.status_doc = 'V' THEN 1
             END AS estado_value
            from led_docs
            inner join dbo.DelimitedSplit8K(@p_status_doc, ',') estados ON led_docs.status_doc = estados.Item
            
            --inner join dbo.DelimitedSplit8K(@lista_tipos_doc, ',') tipos ON led_docs.tipo_doc = tipos.Item
            INNER JOIN led_vehiculos ON led_docs.vehiculo = led_vehiculos.vehiculo
            INNER JOIN led_cliente_vehiculo ON (led_vehiculos.tipo_vehiculo = led_cliente_vehiculo.tipo_vehiculo
                       AND led_docs.tipo_doc = led_cliente_vehiculo.tipo_doc
                       AND led_cliente_vehiculo.cliente = @cliente
                       AND led_cliente_vehiculo.ci_killer IN (select item from dbo.DelimitedSplit8K(@acceso, ',')))
            

            where led_docs.contratista_1 = @p_contratista_1
            and led_docs.periodo >= @p_periodo_desde
            and led_docs.led_seccion = 'V') DOC_VEHICULOS
           GROUP BY DOC_VEHICULOS.tipo_doc, DOC_VEHICULOS.led_seccion, DOC_VEHICULOS.contratista_1,
             DOC_VEHICULOS.contratista, DOC_VEHICULOS.periodo) VEHICULOS

         INNER JOIN led_tipos_doc ON VEHICULOS.tipo_doc = led_tipos_doc.tipo_doc
         INNER JOIN led_contratistas AS led_contratistas_1 ON VEHICULOS.contratista_1 = led_contratistas_1.contratista
         INNER JOIN padrones AS padrones_1 ON led_contratistas_1.id_padron = padrones_1.id_padron
         INNER JOIN led_contratistas ON VEHICULOS.contratista = led_contratistas.contratista
         INNER JOIN padrones ON led_contratistas.id_padron = padrones.id_padron
           
         ORDER BY orden_status, periodo desc, nombre_tipo_doc



 END

 
END

GO