dbo.Imperium_Notificacion_96H_Contratistas

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.Imperium_Notificacion_96H_Contratistas 
 -- Add the parameters for the stored procedure here
 --@periodo_desde int

/*

exec Imperium_Notificacion_96H_Contratistas

*/

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

    -- Insert statements for procedure here

 --AND led_contratistas.contratista_1 NOT IN (1315,1531,1269,1368,1430,1023,1015,1729,91,215,1153)
/*

DECLARE @Cliente int =  7048064 /* rpb > 7048064 */ /* 9335603*/ /*3915936*/
DECLARE @Cliente2 int = 3915936 /*3915936*/
*/

 /*DECLARE @periodo_desde int = CONVERT(int, CONVERT(VARCHAR(4),Year(GETDATE())) + RIGHT('00'+ CONVERT(VARCHAR,Month(GETDATE())), 2) )*/


 DECLARE @periodo_desde int = dbo.PeriodoAdelantado()

 DECLARE @fhd datetime = DATETIMEFROMPARTS(Year(GETDATE()),Month(GETDATE()),Day(GETDATE()),0,0,0,0)
 DECLARE @fhh datetime = DATEADD(second, -1, DATEADD(DAY, 4, @fhd))


 /* Contratistas principales */

 select led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1,
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia,
 padrones_1.identificador AS identificador_1, 
 padrones_1.numero AS numero_1,
 padrones_1.nombre_persona AS nombre_persona_1,
 identificadores_1.abreviatura AS abreviatura_1, 
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,

 NULL AS nombre_persona_e,
 NULL AS identificador_e,
 NULL AS numero_e,
 NULL AS abreviatura_e,
 NULL AS dominio,
 NULL AS nombre_vehiculo,

 /*padrones_e.nombre_persona AS nombre_persona_e,
  padrones_e.identificador AS identificador_e,
  padrones_e.numero AS numero_e,
  identificadores_e.abreviatura AS abreviatura_e,
  led_vehiculos.dominio, led_vehiculos.nombre_vehiculo,*/

 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email
 from led_contratistas 
 inner join led_contratistas AS led_contratistas_1 on led_contratistas.contratista_1 = led_contratistas_1.contratista
 inner join clientes on led_contratistas.cliente = clientes.cliente

 inner join padrones on led_contratistas.id_padron = padrones.id_padron
 inner join identificadores on padrones.identificador = identificadores.identificador

 inner join padrones padrones_1 on led_contratistas_1.id_padron = padrones_1.id_padron
 inner join identificadores identificadores_1 on padrones_1.identificador = identificadores_1.identificador

 inner join led_docs on led_contratistas.contratista = led_docs.contratista
 inner join led_tipos_doc on led_docs.tipo_doc = led_tipos_doc.tipo_doc

 inner join users on led_contratistas_1.user_id = users.user_id

 inner join led_contratista_multi on led_contratistas.contratista = led_contratista_multi.contratista
 inner join 
   (select lcc.cliente, lcc.tipo_contratista, lcc.tipo_doc
   from led_cliente_contratista lcc
   where lcc.afecta_compliance = 1
   /* and (lcc.cliente = @Cliente  or lcc.cliente = @Cliente2 )  */
   and lcc.cliente in (select cliente from clientes where ACCESO_ci = 1 and estado_cliente='A')
   AND lcc.ci_killer = 1
   and lcc.requiere_upload = 1
   and lcc.activo = 1) CONFIG_TC
    ON (led_docs.cliente = CONFIG_TC.cliente 
     AND led_contratista_multi.tipo_contratista = CONFIG_TC.tipo_contratista
     AND led_docs.tipo_doc = CONFIG_TC.tipo_doc)

 inner join led_contratista_periodo on (led_docs.contratista = led_contratista_periodo.contratista
           and led_docs.periodo = led_contratista_periodo.periodo)

 /*left outer join led_empleados on led_docs.empleado = led_empleados.empleado
  left outer join padrones padrones_e on led_empleados.id_padron = padrones_e.id_padron
  left outer join identificadores identificadores_e on padrones_e.identificador = identificadores_e.identificador
 
  left outer join led_vehiculos on led_docs.vehiculo = led_vehiculos.vehiculo*/

 where led_docs.estado_doc IN ('V')
 and led_docs.periodo = @periodo_desde
 and led_contratistas.estado_contratista = 'A'
 and led_contratistas_1.estado_contratista = 'A'
 and led_docs.doc_id_lista IS NULL
 and led_tipos_doc.tipo_doc_lista IS NULL
 and clientes.estado_cliente = 'A' 
 and led_docs.fecha_vigencia BETWEEN @fhd AND @fhh

 and led_contratista_multi.vigencia_desde <= GETDATE()
 and (led_contratista_multi.vigencia_hasta IS NULL OR led_contratista_multi.vigencia_hasta >= GETDATE())
 and led_contratista_multi.activo = 1

 AND led_contratistas.contratista = led_contratistas.contratista_1

 and led_contratistas.contratista_1 <> 1293

 /*and led_contratistas.contratista_1=2141*/


 group by led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1, 
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia, 
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 padrones_1.identificador, padrones_1.numero, padrones_1.nombre_persona, identificadores_1.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,

 /*padrones_e.nombre_persona,
  padrones_e.identificador,
  padrones_e.numero,
  identificadores_e.abreviatura,
 
  led_vehiculos.dominio, led_vehiculos.nombre_vehiculo,*/

 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email



 UNION ALL

 /* Empleados */

 select led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1,
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia,
 padrones_1.identificador AS identificador_1, 
 padrones_1.numero AS numero_1,
 padrones_1.nombre_persona AS nombre_persona_1,
 identificadores_1.abreviatura AS abreviatura_1, 
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,

 padrones_e.nombre_persona AS nombre_persona_e,
 padrones_e.identificador AS identificador_e,
 padrones_e.numero AS numero_e,
 identificadores_e.abreviatura AS abreviatura_e,

 NULL AS dominio,
 NULL AS nombre_vehiculo,

 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email

 from led_contratistas 
 inner join led_contratistas AS led_contratistas_1 on led_contratistas.contratista_1 = led_contratistas_1.contratista
 inner join clientes on led_contratistas.cliente = clientes.cliente

 inner join padrones on led_contratistas.id_padron = padrones.id_padron
 inner join identificadores on padrones.identificador = identificadores.identificador

 inner join padrones padrones_1 on led_contratistas_1.id_padron = padrones_1.id_padron
 inner join identificadores identificadores_1 on padrones_1.identificador = identificadores_1.identificador

 inner join led_docs on led_contratistas.contratista = led_docs.contratista
 inner join led_tipos_doc on led_docs.tipo_doc = led_tipos_doc.tipo_doc

 inner join users on led_contratistas_1.user_id = users.user_id

 inner join led_empleados on (led_docs.empleado = led_empleados.empleado AND led_docs.contratista = led_empleados.contratista)

 inner join padrones padrones_e on led_empleados.id_padron = padrones_e.id_padron
 inner join identificadores identificadores_e on padrones_e.identificador = identificadores_e.identificador


 inner join led_empleado_multi on led_empleados.empleado = led_empleado_multi.empleado
 inner join 
   (select lce.cliente, lce.tipo_empleado, lce.tipo_doc
   from led_cliente_empleado lce
   where lce.afecta_compliance = 1
   /* and (lce.cliente = @Cliente  or lce.cliente = @Cliente2 )  */
   and lce.cliente in (select cliente from clientes where ACCESO_ci = 1 and estado_cliente='A')
   AND lce.ci_killer = 1
   and lce.requiere_upload = 1
   and lce.activo = 1) CONFIG_TE
    ON (led_docs.cliente = CONFIG_TE.cliente 
     AND led_empleado_multi.tipo_empleado = CONFIG_TE.tipo_empleado
     AND led_docs.tipo_doc = CONFIG_TE.tipo_doc)

 inner join led_empleados_periodo on (led_docs.empleado = led_empleados_periodo.empleado
           and led_docs.periodo = led_empleados_periodo.periodo)


 where led_docs.estado_doc IN ('V')
 and led_docs.periodo = @periodo_desde
 and led_contratistas.estado_contratista = 'A'
 and led_contratistas_1.estado_contratista = 'A'
 and led_docs.doc_id_lista IS NULL
 and led_tipos_doc.tipo_doc_lista IS NULL

 and led_docs.fecha_vigencia BETWEEN @fhd AND @fhh
 and clientes.estado_cliente = 'A' 
 and led_empleado_multi.vigencia_desde <= GETDATE()
 and (led_empleado_multi.vigencia_hasta IS NULL OR led_empleado_multi.vigencia_hasta >= GETDATE())
 and led_empleado_multi.activo = 1

 and led_contratistas.contratista_1 <> 1293

 /*and led_contratistas.contratista_1=2141*/



 group by led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1, 
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia,
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 padrones_1.identificador, padrones_1.numero, padrones_1.nombre_persona, identificadores_1.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,
 padrones_e.nombre_persona,
 padrones_e.identificador,
 padrones_e.numero,
 identificadores_e.abreviatura,
 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email


 UNION ALL


 /* Vehículos */

 select led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1,
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia, 
 padrones_1.identificador AS identificador_1, 
 padrones_1.numero AS numero_1,
 padrones_1.nombre_persona AS nombre_persona_1,
 identificadores_1.abreviatura AS abreviatura_1, 
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,

 NULL AS nombre_persona_e,
 NULL AS identificador_e,
 NULL AS numero_e,
 NULL AS abreviatura_e,
 led_vehiculos.dominio, led_vehiculos.nombre_vehiculo,

 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email

 from led_contratistas 
 inner join led_contratistas AS led_contratistas_1 on led_contratistas.contratista_1 = led_contratistas_1.contratista
 inner join clientes on led_contratistas.cliente = clientes.cliente

 inner join padrones on led_contratistas.id_padron = padrones.id_padron
 inner join identificadores on padrones.identificador = identificadores.identificador

 inner join padrones padrones_1 on led_contratistas_1.id_padron = padrones_1.id_padron
 inner join identificadores identificadores_1 on padrones_1.identificador = identificadores_1.identificador

 inner join led_docs on led_contratistas.contratista = led_docs.contratista
 inner join led_tipos_doc on led_docs.tipo_doc = led_tipos_doc.tipo_doc

 inner join users on led_contratistas_1.user_id = users.user_id

 inner join led_vehiculos on (led_docs.vehiculo = led_vehiculos.vehiculo AND led_docs.contratista = led_vehiculos.contratista)

 inner join 
   (select lcv.cliente, lcv.tipo_vehiculo, lcv.tipo_doc
   from led_cliente_vehiculo lcv
   where lcv.afecta_compliance = 1
   /* and (lcv.cliente = @Cliente  or lcv.cliente = @Cliente2 )  */
   and lcv.cliente in (select cliente from clientes where ACCESO_ci = 1 and estado_cliente='A')
   AND lcv.ci_killer = 1
   and lcv.requiere_upload = 1
   and lcv.activo = 1) CONFIG_TV
    ON (led_docs.cliente = CONFIG_TV.cliente 
     AND led_vehiculos.tipo_vehiculo = CONFIG_TV.tipo_vehiculo
     AND led_docs.tipo_doc = CONFIG_TV.tipo_doc)

 inner join led_vehiculos_periodo on (led_docs.vehiculo = led_vehiculos_periodo.vehiculo
           and led_docs.periodo = led_vehiculos_periodo.periodo)


 where led_docs.estado_doc IN ('V')
 and led_docs.periodo = @periodo_desde
 and led_contratistas.estado_contratista = 'A'
 and led_contratistas_1.estado_contratista = 'A'
 and led_docs.doc_id_lista IS NULL
 and led_tipos_doc.tipo_doc_lista IS NULL
 and clientes.estado_cliente = 'A' 
 and led_docs.fecha_vigencia BETWEEN @fhd AND @fhh

 and led_contratistas.contratista_1 <> 1293

 /*and led_contratistas.contratista_1=2141*/

 group by led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1, 
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia,
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 padrones_1.identificador, padrones_1.numero, padrones_1.nombre_persona, identificadores_1.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,
 led_vehiculos.dominio, led_vehiculos.nombre_vehiculo,
 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email



 UNION ALL

 
 /* Subcontratistas */

 select led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1,
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia,
 padrones_1.identificador AS identificador_1, 
 padrones_1.numero AS numero_1,
 padrones_1.nombre_persona AS nombre_persona_1,
 identificadores_1.abreviatura AS abreviatura_1, 
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,

 NULL AS nombre_persona_e,
 NULL AS identificador_e,
 NULL AS numero_e,
 NULL AS abreviatura_e,
 NULL AS dominio,
 NULL AS nombre_vehiculo,

 /*padrones_e.nombre_persona AS nombre_persona_e,
  padrones_e.identificador AS identificador_e,
  padrones_e.numero AS numero_e,
  identificadores_e.abreviatura AS abreviatura_e,
  led_vehiculos.dominio, led_vehiculos.nombre_vehiculo,*/

 clientes.nombre_cliente, 

 /*NULL AS user_id, 
  NULL AS first_name, 
  NULL AS last_name, 
  NULL AS username, 
  NULL AS email*/

 users.user_id, users.first_name, users.last_name, users.username, users.email

 from led_contratistas 
 inner join led_contratistas AS led_contratistas_1 on led_contratistas.contratista_1 = led_contratistas_1.contratista
 inner join clientes on led_contratistas.cliente = clientes.cliente

 inner join padrones on led_contratistas.id_padron = padrones.id_padron
 inner join identificadores on padrones.identificador = identificadores.identificador

 inner join padrones padrones_1 on led_contratistas_1.id_padron = padrones_1.id_padron
 inner join identificadores identificadores_1 on padrones_1.identificador = identificadores_1.identificador

 inner join led_docs on led_contratistas.contratista = led_docs.contratista
 inner join led_tipos_doc on led_docs.tipo_doc = led_tipos_doc.tipo_doc

 inner join users on led_contratistas_1.user_id = users.user_id

 inner join led_contratista_multi on led_contratistas.contratista = led_contratista_multi.contratista
 inner join 
   (select lcc.cliente, lcc.tipo_contratista, lcc.tipo_doc
   from led_cliente_contratista lcc
   where lcc.afecta_compliance = 1
   /* and (lcc.cliente = @Cliente  or lcc.cliente = @Cliente2 )  */
   and lcc.cliente in (select cliente from clientes where ACCESO_ci = 1 and estado_cliente='A')
   AND lcc.ci_killer = 1
   and lcc.requiere_upload = 1
   and lcc.activo = 1) CONFIG_TC
    ON (led_docs.cliente = CONFIG_TC.cliente 
     AND led_contratista_multi.tipo_contratista = CONFIG_TC.tipo_contratista
     AND led_docs.tipo_doc = CONFIG_TC.tipo_doc)

 inner join led_subcontratista_periodo on (led_docs.contratista = led_subcontratista_periodo.subcontratista
           and led_docs.periodo = led_subcontratista_periodo.periodo)


 /*left outer join led_empleados on led_docs.empleado = led_empleados.empleado
  left outer join padrones padrones_e on led_empleados.id_padron = padrones_e.id_padron
  left outer join identificadores identificadores_e on padrones_e.identificador = identificadores_e.identificador
 
  left outer join led_vehiculos on led_docs.vehiculo = led_vehiculos.vehiculo*/

 where led_docs.estado_doc IN ('V')
 and led_docs.periodo = @periodo_desde
 and led_contratistas.estado_contratista = 'A'
 and led_contratistas_1.estado_contratista = 'A'
 and led_docs.doc_id_lista IS NULL
 and led_tipos_doc.tipo_doc_lista IS NULL
 and clientes.estado_cliente = 'A' 
 and led_docs.fecha_vigencia BETWEEN @fhd AND @fhh

 and led_contratista_multi.vigencia_desde <= GETDATE()
 and (led_contratista_multi.vigencia_hasta IS NULL OR led_contratista_multi.vigencia_hasta >= GETDATE())
 and led_contratista_multi.activo = 1

 AND led_contratistas.contratista <> led_contratistas.contratista_1

 and led_contratistas.contratista_1 <> 1293

 /*and led_contratistas.contratista_1=2141*/


 group by led_docs.doc_id, led_docs.led_seccion, led_contratistas.contratista_1, 
 led_contratistas.contratista, led_contratistas.cliente, led_docs.fecha_vigencia, 
 padrones.identificador, padrones.numero, padrones.nombre_persona, identificadores.abreviatura, 
 padrones_1.identificador, padrones_1.numero, padrones_1.nombre_persona, identificadores_1.abreviatura, 
 led_docs.periodo, led_tipos_doc.nombre_tipo_doc, led_docs.estado_doc, led_docs.fecha_tolerancia,
 led_docs.led_seccion, led_docs.empleado, led_docs.vehiculo,

 /*padrones_e.nombre_persona,
  padrones_e.identificador,
  padrones_e.numero,
  identificadores_e.abreviatura,
 
  led_vehiculos.dominio, led_vehiculos.nombre_vehiculo,*/

 clientes.nombre_cliente, users.user_id, users.first_name, users.last_name, users.username, users.email





 ORDER BY clientes.nombre_cliente, led_contratistas.contratista_1, led_contratistas.contratista,
 led_docs.periodo, led_docs.led_seccion, 
 nombre_persona, 
 dominio,
 led_tipos_doc.nombre_tipo_doc
END

GO