dbo.LN_Impe_buscar_empleado_accesoprueba

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

exec [LN_Impe_buscar_empleado_acceso] 9936068,null,'20328088399',1013
exec [LN_Impe_buscar_empleado_acceso] 9936068,'ABENTE',NULL,1013
exec [LN_Impe_buscar_empleado_acceso] 9936068,'ACOSTA',NULL,1013

exec [LN_Impe_buscar_empleado_acceso] 9936068,NULL,'20336165009',1013
exec [LN_Impe_buscar_empleado_acceso] 9936068,NULL,'27309532355',1013


exec [LN_Impe_buscar_empleado_acceso] 9936068,'GARCIA','2722',1013

exec [LN_Impe_buscar_empleado_acceso] 9936068,'garc', null,1013
update led_blacklist set activo = 1 where cliente = 9936068 and id_padron = 27272983
select * from  led_blacklist where cliente = 9936068 and id_padron = 27272983


select * from padrones where identificacion = '20336165009' 



select * from padrones where charindex(
        '2722' collate Latin1_General_BIN, 
        SUBSTRING(identificacion, 1, 4) collate Latin1_General_BIN
      )>0


*/

/*
exec [LN_Impe_buscar_empleado_acceso] '3920694',null,'202313338','1040'
*/
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_Impe_buscar_empleado_accesoprueba
 -- Add the parameters for the stored procedure here

 @p_cliente           int = NULL,
 @p_nombre           varchar(50) = NULL,
 @p_numero           varchar(20) = NULL,
 @p_locacion           int = NULL
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 declare @largonumero int = len(@p_numero)
 declare @largonombre int = len(@p_nombre)
 declare @vp_numero varchar(20) = @p_numero
 declare @vp_cliente int = @p_cliente
 declare @vp_nombre varchar(50) = upper(@p_nombre)
 declare @vp_locacion int = @p_locacion

 /* SI MANDAN A BUSCAR POR NUMERO DE TAX ID IGNORO EL NOMBRE */
-- SELECT @vp_numero as pepe,@vp_nombre as pipo

 if (@p_numero is not null)
  begin
   --select 'BORRANDO'
   set @vp_nombre = null
  end
 
 --SELECT @vp_numero as pepe2,@vp_nombre as pipo2

 -- Insert statements for procedure here

 if (@vp_cliente > 0 AND (@vp_nombre is not null OR @vp_numero is not null) AND @vp_locacion > 0) begin
 
  if (@vp_nombre is not null)

   select padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,led_blacklist.tipo_lista AS tipo_reg
   from padrones
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   inner join led_blacklist ON padrones.id_padron = led_blacklist.id_padron
   where led_blacklist.activo = 1
   and led_blacklist.cliente = @vp_cliente 
   and led_blacklist.tipo_lista = 'W' --(lista blanca)
   /* 28/03/2023: No este en la lista negra*/
   and padrones.id_padron not in (select id_padron from led_blacklist where activo = 1 and cliente = @vp_cliente and tipo_lista = 'B')
   
-- and contains(padrones.*, @vp_nombre)
   and charindex(upper(@vp_nombre) collate Latin1_General_BIN,SUBSTRING(upper(padrones.nombre_persona), 1, @largonombre) collate Latin1_General_BIN)>0
   group by padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,led_blacklist.tipo_lista

   
   UNION ALL


   select padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,'E' AS tipo_reg
   from padrones
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   
   inner join led_empleados ON padrones.id_padron = led_empleados.id_padron
   left join led_docs ON led_empleados.empleado = led_docs.empleado
   inner join led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
   inner join led_contratistas ON led_empleados.contratista = led_contratistas.contratista
   inner join led_contratistas contratistas_1 on (led_contratistas.contratista_1 = contratistas_1.contratista and contratistas_1.estado_contratista = 'A')

   inner join led_contratista_locaciones on led_contratistas.contratista = led_contratista_locaciones.contratista
   inner join led_empleado_locaciones on led_empleados.empleado = led_empleado_locaciones.empleado
   
   where led_tipos_doc.modalidad = 'A'
   and led_contratistas.estado_contratista = 'A'
   and led_docs.periodo = dbo.PeriodoAdelantado()
   and led_empleados.cliente = @vp_cliente
   and led_empleados.estado_empleado = 'A' 
   --and contains(padrones.*, @vp_nombre)
   and charindex(upper(@vp_nombre) collate Latin1_General_BIN,SUBSTRING(upper(padrones.nombre_persona), 1, @largonombre) collate Latin1_General_BIN)>0   
   and led_contratista_locaciones.locacion = @vp_locacion
   and led_contratista_locaciones.activo = 1
   and led_contratista_locaciones.vigencia_desde <= GETDATE()
   and (led_contratista_locaciones.vigencia_hasta > GETDATE() OR led_contratista_locaciones.vigencia_hasta IS NULL)
   and led_empleado_locaciones.locacion = @vp_locacion
   and led_empleado_locaciones.activo = 1
   and led_empleado_locaciones.vigencia_desde <= GETDATE()
   and (led_empleado_locaciones.vigencia_hasta > GETDATE() OR led_empleado_locaciones.vigencia_hasta IS NULL)
   and padrones.id_padron not in (select id_padron from led_blacklist where activo = 1 and cliente = @vp_cliente) --Ni blanca, ni negra
      
   group by padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name
   
   UNION ALL

   select padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,'C' AS tipo_reg
   from padrones
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   
   inner join led_contratistas ON padrones.id_padron = led_contratistas.id_padron

   inner join led_contratistas contratistas_1 on (led_contratistas.contratista_1 = contratistas_1.contratista and contratistas_1.estado_contratista = 'A')

   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 led_contratista_locaciones on led_contratistas.contratista = led_contratista_locaciones.contratista
   
   left outer join
   /*inner join */
      (select led_contratistas.contratista, count(*) AS total_empleados
      from led_contratistas inner join led_empleados
      on led_contratistas.contratista = led_empleados.contratista
      inner join led_empleados_periodo
      on led_empleados.empleado = led_empleados_periodo.empleado
      where led_empleados_periodo.periodo = dbo.PeriodoAdelantado()
      and led_empleados.cliente = @vp_cliente
      and led_empleados.estado_empleado = 'A'
      and led_contratistas.estado_contratista = 'A'
      group by led_contratistas.contratista) empleados
   on led_contratistas.contratista = empleados.contratista
   
   where led_tipos_doc.modalidad = 'A'
   and led_docs.periodo = dbo.PeriodoAdelantado()
   and ISNULL(empleados.total_empleados,0) = 0
   and led_contratistas.cliente = @vp_cliente
   and led_contratistas.estado_contratista = 'A'
   and led_contratista_locaciones.locacion = @vp_locacion
   and led_contratista_locaciones.activo = 1
   and led_contratista_locaciones.vigencia_desde <= GETDATE()
   and (led_contratista_locaciones.vigencia_hasta > GETDATE() OR led_contratista_locaciones.vigencia_hasta IS NULL)
   --and contains(padrones.*, @vp_nombre)
   and charindex(upper(@vp_nombre) collate Latin1_General_BIN,SUBSTRING(upper(padrones.nombre_persona), 1, @largonombre) collate Latin1_General_BIN)>0
   and padrones.id_padron not in (select id_padron from led_blacklist where activo = 1 and cliente = @vp_cliente) --Ni blanca, ni negra

   group by empleados.total_empleados, padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name


  else
   /** consulta por número de documento **/


   select padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,led_blacklist.tipo_lista AS tipo_reg
   from padrones
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   inner join led_blacklist ON padrones.id_padron = led_blacklist.id_padron
   where led_blacklist.activo = 1
   and led_blacklist.cliente = @vp_cliente 
   and led_blacklist.tipo_lista = 'W'
   /* 28/03/2023: No este en la lista negra*/
   and padrones.id_padron not in (select id_padron from led_blacklist where activo = 1 and cliente = @vp_cliente and tipo_lista = 'B')
   
   -- and padrones.identificacion = @vp_numero
   --and contains(padrones.identificacion,@vp_numero) 
   and 
   
   (
   charindex(upper(@vp_numero) collate Latin1_General_BIN,SUBSTRING(upper(padrones.identificacion), 1, @largonumero) collate Latin1_General_BIN)>0
   or
   charindex(upper(@vp_numero) collate Latin1_General_BIN,SUBSTRING(upper(padrones.cuerpo_clave), 1, @largonumero) collate Latin1_General_BIN)>0
   )
   group by padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,led_blacklist.tipo_lista

   
   UNION


   select padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,'E' AS tipo_reg
   from padrones
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   
   inner join led_empleados ON padrones.id_padron = led_empleados.id_padron
   left join led_docs ON led_empleados.empleado = led_docs.empleado
   inner join led_tipos_doc ON led_docs.tipo_doc = led_tipos_doc.tipo_doc
   inner join led_contratistas ON led_empleados.contratista = led_contratistas.contratista
   inner join led_contratistas contratistas_1 on (led_contratistas.contratista_1 = contratistas_1.contratista and contratistas_1.estado_contratista = 'A')
   inner join led_contratista_locaciones on led_contratistas.contratista = led_contratista_locaciones.contratista
   inner join led_empleado_locaciones on led_empleados.empleado = led_empleado_locaciones.empleado
   
   where led_tipos_doc.modalidad = 'A'
   and led_contratistas.estado_contratista = 'A'
   and led_docs.periodo = dbo.PeriodoAdelantado()
   and led_empleados.cliente = @vp_cliente
   and led_empleados.estado_empleado = 'A' 
   --and padrones.identificacion = @vp_numero
   --and contains(padrones.identificacion,@vp_numero) 
   and 
   (
   charindex(upper(@vp_numero) collate Latin1_General_BIN,SUBSTRING(upper(padrones.identificacion), 1, @largonumero) collate Latin1_General_BIN)>0
   or 
   charindex(upper(@vp_numero) collate Latin1_General_BIN,SUBSTRING(upper(padrones.cuerpo_clave), 1, @largonumero) collate Latin1_General_BIN)>0
   )
   and led_contratista_locaciones.locacion = @vp_locacion
   and led_contratista_locaciones.activo = 1
   and led_contratista_locaciones.vigencia_desde <= GETDATE()
   and (led_contratista_locaciones.vigencia_hasta > GETDATE() OR led_contratista_locaciones.vigencia_hasta IS NULL)
   and led_empleado_locaciones.locacion = @vp_locacion
   and led_empleado_locaciones.activo = 1
   and led_empleado_locaciones.vigencia_desde <= GETDATE()
   and (led_empleado_locaciones.vigencia_hasta > GETDATE() OR led_empleado_locaciones.vigencia_hasta IS NULL)
   and padrones.id_padron not in (select id_padron from led_blacklist where activo = 1 and cliente = @vp_cliente) --Ni blanca, ni negra
   
   group by padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name
   
   UNION

   select padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name,'C' AS tipo_reg
   from padrones
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   
   inner join led_contratistas ON padrones.id_padron = led_contratistas.id_padron
   inner join led_contratistas contratistas_1 on (led_contratistas.contratista_1 = contratistas_1.contratista and contratistas_1.estado_contratista = 'A')

   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 led_contratista_locaciones on led_contratistas.contratista = led_contratista_locaciones.contratista
   
   left outer join
   /*inner join */
      (select led_contratistas.contratista, count(*) AS total_empleados
      from led_contratistas inner join led_empleados
      on led_contratistas.contratista = led_empleados.contratista
      inner join led_empleados_periodo
      on led_empleados.empleado = led_empleados_periodo.empleado
      where led_empleados_periodo.periodo = dbo.PeriodoAdelantado()
      and led_empleados.cliente = @vp_cliente
      and led_empleados.estado_empleado = 'A'
      and led_contratistas.estado_contratista = 'A'
      group by led_contratistas.contratista) empleados
   on led_contratistas.contratista = empleados.contratista
   
   where led_tipos_doc.modalidad = 'A'
   and led_docs.periodo = dbo.PeriodoAdelantado()
   and ISNULL(empleados.total_empleados,0) = 0
   and led_contratistas.cliente = @vp_cliente
   and led_contratistas.estado_contratista = 'A'
   and led_contratista_locaciones.locacion = @vp_locacion
   and led_contratista_locaciones.activo = 1
   and led_contratista_locaciones.vigencia_desde <= GETDATE()
   and (led_contratista_locaciones.vigencia_hasta > GETDATE() OR led_contratista_locaciones.vigencia_hasta IS NULL)
   
   --and padrones.identificacion = @vp_numero
   --and contains(padrones.identificacion,@vp_numero) 
   
   and 
   (
   charindex(upper(@vp_numero) collate Latin1_General_BIN,SUBSTRING(upper(padrones.identificacion), 1, @largonumero) collate Latin1_General_BIN)>0
   or 
   charindex(upper(@vp_numero) collate Latin1_General_BIN,SUBSTRING(upper(padrones.cuerpo_clave), 1, @largonumero) collate Latin1_General_BIN)>0
   )
   and padrones.id_padron not in (select id_padron from led_blacklist where activo = 1 and cliente = @vp_cliente) --Ni blanca, ni negra
   group by empleados.total_empleados, padrones.id_padron, padrones.nombre_persona, padrones.identificador, 
   padrones.numero, padrones.tipo_persona, padrones.sexo,
   padrones.pre_clave, padrones.cuerpo_clave, padrones.suf_clave,
   identificadores.country_code, identificadores.abreviatura, country.country_name


 end

END
GO