dbo.api_padrones_busqueda_avanzada_30012025

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: MIS
-- Create date: 2025-01-03
-- Description: Búsqueda de Padrón avanzado: Tiene en cuenta tipo de persona, y provincia
/* Hay que armar un store de busqueda de padron, que reciba los siguientes parametros para realizar una busqueda con:
Tipo de persona (física o jurídica)
razon_social (aka: nombre_persona)
nombre
apellido
identificacion
provincia (para comparar con el domicilio)
*/
-- =============================================
/*
[api_padrones_busqueda_avanzada] 'F', null,null,'SARALEGUI FERNANDEZ','MANUEL','M',null,30,40,30
[api_padrones_busqueda_avanzada] 'F',null, 'SORIA MARIA INES',null,null,null,null,null,null,30
[api_padrones_busqueda_avanzada] 'F',null, NULL,'SORIA','INES MARIA','F',null,55,56,30
[api_padrones_busqueda_avanzada] 'F',null, NULL,'FLORES','NELLY EDITH','F',null,NULL,NULL,30
[api_padrones_busqueda_avanzada_30012025] 'J',null, 'BANCO DE GALICIA'

select * from provincias where country_code = 'AR' and codigo_afip is not null order by codigo_afip
*/
CREATE OR ALTER PROCEDURE dbo.api_padrones_busqueda_avanzada_30012025
@p_tipo_persona char(1), -- F o J
@p_taxid varchar(50),
@p_razon_social varchar(100) = null,
@p_apellido varchar(100) = null,
@p_nombre varchar(100) = null,
@p_genero char(1) = null,
@p_provincia char(2) = null,
@p_edad_min int = null, 
@p_edad_max int = null,
@max_rows int = 30
AS
BEGIN
 SET NOCOUNT ON;
 declare @busqueda varchar(500) = '---'
 declare @busqueda_nombre varchar(500) = '---'
 declare @busqueda_apellido varchar(500) = '---'
 declare @busqueda_apellido_exacto varchar(500) = '---'


 declare @resultado table 
 (mensaje varchar(200), 
  error int, 
  id_padron int, 
  identificacion varchar(50),
  nombre_persona varchar(500), 
  tipo_persona varchar(20),
  sexo char(1),
  fecha_nacimiento date,
  fecha_defuncion date, 
  cuerpo_clave varchar(50),
  country_code varchar(50),
  identificador varchar(50), 
  ismodified datetime,
  apellidos varchar(500),
  nombres varchar(500), 
  domicilios varchar(max)) 

 select @p_taxid = ltrim(rtrim(@p_taxid))
 select @p_razon_social = ltrim(rtrim(@p_razon_social))
 select @p_nombre = ltrim(rtrim(@p_nombre))
 select @p_apellido = ltrim(rtrim(@p_apellido))

 if (@p_tipo_persona is null or @p_tipo_persona not in ('F','J'))
 begin
  select 'Debe mandar Tipo de persona (F o J)' as mensaje, 1 as error
  return
 end 

 if (@p_tipo_persona = 'F' and @p_taxid is null and @p_razon_social is null)
  --and((@p_nombre is null and @p_apellido is not null) or 
   --(@p_nombre is not null and @p_apellido is null))
  and @p_apellido is null
 begin
  select 'Debe mandar apellido' as mensaje, 2 as error
  return
 end 

 if (@p_tipo_persona = 'F' and @p_taxid is null and @p_razon_social is null)
  and @p_apellido is not null
  and @p_nombre is null
  and @p_genero is null
  and @p_provincia is null
  and @p_edad_min is null 
  and @p_edad_max is null

 begin
  select 'Debe mandar apellido y algún parámetro más' as mensaje, 3 as error
  return
 end 



 if (@p_tipo_persona = 'J' and @p_taxid is null and @p_razon_social is null)
 begin
  select 'Debe mandar tax-id o razón social' as mensaje, 4 as error
  return
 end 
 /*
  IF (isnull(@p_taxid,'') != '') --> Prioriza busqueda por lo que viene en el campo taxid
  begin
  select @busqueda = @p_taxid 
  end 
  else 
  begin
  */
  IF (isnull(@p_razon_social,'') != '')
  begin
   select @busqueda = @p_razon_social 
  end
 
  IF (@p_tipo_persona = 'F')
  begin 
   IF (isnull(@p_apellido,'') != '' and isnull(@p_nombre,'') != '')
   begin
    select @busqueda = isnull(@p_apellido,'') + ' ' + isnull(@p_nombre,'')
   end
  end
 /*
  end
  */



 if (isnumeric(@busqueda) != 1 and @busqueda != '---')
 begin
  if @busqueda not like '% %' and len(@busqueda) > 2 and @busqueda not like '%*' 
   -- Si es una sola palabra (no tiene espacios), es de mas de 2 digitos y no termina es *, se lo agregamos 
   select @busqueda = @busqueda + '*'
  if @busqueda not like '% near %'
   select @busqueda =  '"' + REPLACE(@busqueda,' ','" near "') + '"' 
   --> si solo viene un numero, no tiene espacios asi que solo le agrega las comillas, pero no hace el replace
   --> siempre manda como primer parametro lo que completaron en el buscador de padron, que a veces es el nombre 
 end 


 if (@p_nombre is not null)
 begin
  set @busqueda_nombre = @p_nombre
  if @busqueda_nombre not like '% %' and len(@busqueda_nombre) > 2 and @busqueda_nombre not like '%*' 
   select @busqueda_nombre = @busqueda_nombre + '*'
  if @busqueda_nombre not like '% near %'
   select @busqueda_nombre =  '"' + REPLACE(@busqueda_nombre,' ','" near "') + '"' 
 end 

 if (@p_apellido is not null)
 begin
  set @busqueda_apellido_exacto = '"' + @p_apellido + '"'
  set @busqueda_apellido = @p_apellido
  if @busqueda_apellido not like '% %' and len(@busqueda_apellido) > 2 and @busqueda_apellido not like '%*' 
  begin
   select @busqueda_apellido = @busqueda_apellido + '*'
  end 
  if @busqueda_apellido not like '% near %'
  begin 
   select @busqueda_apellido =  '"' + REPLACE(@busqueda_apellido,' ','" near "') + '"' 
  end
 end 


 select @busqueda, @p_apellido, @busqueda_apellido, @p_nombre, @busqueda_nombre
 /*select top(@max_rows) * from
  (
  */
 if @p_taxid is not null 
 begin 
  insert into @resultado
  -- Para que en los casos que lo va a buscar en Afip, y lo da de alta en la BD
  -- Apenas lo damos de alta, no lo encuentra por fulltext, entonces lo buscamos por identificacion por las dudas....
  SELECT distinct top(@max_rows) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,sexo
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,cuerpo_clave,
  identificadores.country_code,
        identificadores.identificador as identificador,isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios
  from dbo.padrones
   inner join identificadores on identificadores.identificador = padrones.identificador
   left join datos_personas on datos_personas.c01_cuit = padrones.identificacion
   left join (SELECT cuit,prv_codigo,domicilios=STUFF(
         (SELECT distinct '; Domicilio:' + replace(domicilio,';',',') + 
      iif(isnull(codpos,'') != '', ' (CP ' + convert(varchar,codpos) + ')','') +
      iif(isnull(loc_nombre,'') != '', ' - ' + loc_nombre,'') + 
      iif(isnull(prv_nombre,'') != '', ' - ' + prv_nombre,'') 
      AS [text()]
       FROM domicilios_todos DT
       WHERE DT.cuit = T.cuit and DT.visible = 1 and DT.nombre_fuente in ('AFIP','RENAPER')
         and (@p_provincia is null or DT.prv_codigo = @p_provincia)
      FOR XML PATH('')), 1, 2, '') 
    FROM domicilios_todos T where T.visible = 1 
    and T.nombre_fuente in ('AFIP','RENAPER') and (@p_provincia is null or T.prv_codigo = @p_provincia)

    GROUP BY cuit,prv_codigo) dd 
    
    on (dd.cuit = padrones.identificacion)

   WHERE /*@p_taxid is not null 
      and*/ identificacion = @p_taxid 
     and habilitado = 1
 end  
  --union
 if (@p_taxid is null 
     and isnull(@busqueda,'---') != '---')
  begin 
  insert into @resultado

  SELECT distinct top(@max_rows) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,sexo
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,cuerpo_clave,
  identificadores.country_code,
        identificadores.identificador as identificador,isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,prv_codigo,domicilios=STUFF(
         (SELECT distinct '; Domicilio:' + replace(domicilio,';',',') + 
      iif(isnull(codpos,'') != '', ' (CP ' +  convert(varchar,codpos) + ')','') +
      iif(isnull(loc_nombre,'') != '', ' - ' + loc_nombre,'') + 
      iif(isnull(prv_nombre,'') != '', ' - ' + prv_nombre,'') 
      AS [text()]
       FROM domicilios_todos DT
       WHERE DT.cuit = T.cuit and DT.visible = 1 and DT.nombre_fuente in ('AFIP','RENAPER')
         and (@p_provincia is null or DT.prv_codigo = @p_provincia)
      FOR XML PATH('')), 1, 2, '') 
    FROM domicilios_todos T where T.visible = 1 
    and T.nombre_fuente in ('AFIP','RENAPER') and (@p_provincia is null or T.prv_codigo = @p_provincia)

    GROUP BY cuit,prv_codigo) dd 
    
    on (dd.cuit = padrones.identificacion)

  WHERE /* @p_taxid is null 
     and isnull(@busqueda,'---') != '---'  
     and */ 
    CONTAINS(padrones.*,@busqueda)
    and tipo_persona = @p_tipo_persona
   
   and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo,1,0)) = 1 

    and iif(@p_provincia is null, 1, iif(country_code in ('AR') and dd.domicilios is not null,1,0)) = 1 
    
    --and iif(@p_edad_min is null and @p_edad_max is null, 1, 
    --    iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max,1,0)) = 1 
    and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max 
          and datos_personas.fecha_defuncion is null 
          and isnull(datos_personas.defuncion,0) = 0,1,0)) = 1
    and habilitado = 1
  end   
  --union


  declare @con_apellido_exacto int = 0


  if (@p_taxid is null 
      and isnull(@p_apellido,'') != '' 
      and isnull(@busqueda_nombre,'---') = '---' )
  begin
  insert into @resultado
  
  SELECT distinct top(@max_rows) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,sexo
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,cuerpo_clave,
  identificadores.country_code,
        identificadores.identificador as identificador,isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   inner join (SELECT cuit,prv_codigo,domicilios=STUFF(
         (SELECT distinct '; Domicilio:' + replace(domicilio,';',',') + 
      iif(isnull(codpos,'') != '', ' (CP ' +  convert(varchar,codpos) + ')','') +
      iif(isnull(loc_nombre,'') != '', ' - ' + loc_nombre,'') + 
      iif(isnull(prv_nombre,'') != '', ' - ' + prv_nombre,'') 
      AS [text()]
       FROM domicilios_todos DT
       WHERE DT.cuit = T.cuit and DT.visible = 1 and DT.nombre_fuente in ('AFIP','RENAPER')
         and (@p_provincia is null or DT.prv_codigo = @p_provincia)
      FOR XML PATH('')), 1, 2, '') 
    FROM domicilios_todos T where T.visible = 1 
    and T.nombre_fuente in ('AFIP','RENAPER') and (@p_provincia is null or T.prv_codigo = @p_provincia)

    GROUP BY cuit,prv_codigo) dd 
    
    on (dd.cuit = padrones.identificacion)

  WHERE /*@p_taxid is null 
     and isnull(@busqueda,'---') = '---' 
     and isnull(@busqueda_apellido,'---') != '---' 
     and isnull(@busqueda_nombre,'---') = '---'  
     and */
    CONTAINS(padrones.apellidos,@busqueda_apellido_exacto)
    and padrones.apellidos = @p_apellido
    and tipo_persona = @p_tipo_persona
    
    and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo,1,0)) = 1 

    and iif(@p_provincia is null, 1, iif(country_code in ('AR') and dd.domicilios is not null,1,0)) = 1 
    and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max 
          and datos_personas.fecha_defuncion is null 
          and isnull(datos_personas.defuncion,0) = 0,1,0)) = 1 
    and habilitado = 1

  set @con_apellido_exacto = @@ROWCOUNT
  end

  if (@p_taxid is null 
      and isnull(@busqueda_apellido,'---') != '---' 
      and isnull(@busqueda_nombre,'---') = '---' 
   and @con_apellido_exacto = 0)
  begin 
  insert into @resultado
  
  SELECT distinct top(@max_rows) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,sexo
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,cuerpo_clave,
  identificadores.country_code,
        identificadores.identificador as identificador,isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   inner join (SELECT cuit,prv_codigo,domicilios=STUFF(
         (SELECT distinct '; Domicilio:' + replace(domicilio,';',',') + 
      iif(isnull(codpos,'') != '', ' (CP ' +  convert(varchar,codpos) + ')','') +
      iif(isnull(loc_nombre,'') != '', ' - ' + loc_nombre,'') + 
      iif(isnull(prv_nombre,'') != '', ' - ' + prv_nombre,'') 
      AS [text()]
       FROM domicilios_todos DT
       WHERE DT.cuit = T.cuit and DT.visible = 1 and DT.nombre_fuente in ('AFIP','RENAPER')
         and (@p_provincia is null or DT.prv_codigo = @p_provincia)
      FOR XML PATH('')), 1, 2, '') 
    FROM domicilios_todos T where T.visible = 1 
    and T.nombre_fuente in ('AFIP','RENAPER') and (@p_provincia is null or T.prv_codigo = @p_provincia)

    GROUP BY cuit,prv_codigo) dd 
    
    on (dd.cuit = padrones.identificacion)

  WHERE /*@p_taxid is null 
     and isnull(@busqueda,'---') = '---' 
     and isnull(@busqueda_apellido,'---') != '---' 
     and isnull(@busqueda_nombre,'---') = '---'  
     and */CONTAINS(padrones.apellidos,@busqueda_apellido)
    and tipo_persona = @p_tipo_persona
    
    and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo,1,0)) = 1 

    and iif(@p_provincia is null, 1, iif(country_code in ('AR') and dd.domicilios is not null,1,0)) = 1 
    and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max 
          and datos_personas.fecha_defuncion is null 
          and isnull(datos_personas.defuncion,0) = 0,1,0)) = 1 
    and habilitado = 1
  end
  --union
  if (@p_taxid is null 
      and isnull(@p_apellido,'') != '' 
      and isnull(@busqueda_nombre,'---') != '---')
  begin 
  insert into @resultado

  SELECT distinct top(@max_rows) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,sexo
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,cuerpo_clave,
  identificadores.country_code,
        identificadores.identificador as identificador,isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,prv_codigo,domicilios=STUFF(
         (SELECT distinct '; Domicilio:' + replace(domicilio,';',',') + 
      iif(isnull(codpos,'') != '', ' (CP ' +  convert(varchar,codpos) + ')','') +
      iif(isnull(loc_nombre,'') != '', ' - ' + loc_nombre,'') + 
      iif(isnull(prv_nombre,'') != '', ' - ' + prv_nombre,'') 
      AS [text()]
       FROM domicilios_todos DT
       WHERE DT.cuit = T.cuit and DT.visible = 1 and DT.nombre_fuente in ('AFIP','RENAPER')
         and (@p_provincia is null or DT.prv_codigo = @p_provincia)
      FOR XML PATH('')), 1, 2, '') 
    FROM domicilios_todos T where T.visible = 1 
    and T.nombre_fuente in ('AFIP','RENAPER') and (@p_provincia is null or T.prv_codigo = @p_provincia)

    GROUP BY cuit,prv_codigo) dd 
    
    on (dd.cuit = padrones.identificacion)

  WHERE @p_taxid is null 
    and isnull(@busqueda,'---') = '---' 
    and isnull(@busqueda_apellido,'---') != '---' 
    and isnull(@busqueda_nombre,'---') != '---' 
    and CONTAINS(padrones.apellidos,@busqueda_apellido_exacto)
    and padrones.apellidos = @p_apellido
    and CONTAINS(padrones.nombres,@busqueda_nombre)
    and tipo_persona = @p_tipo_persona

    and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo,1,0)) = 1 

    and iif(@p_provincia is null, 1, iif(country_code in ('AR') and dd.domicilios is not null,1,0)) = 1 
    --and iif(@p_edad_min is null and @p_edad_max is null, 1, 
    --    iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max,1,0)) = 1 
    and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max 
          and datos_personas.fecha_defuncion is null 
          and isnull(datos_personas.defuncion,0) = 0,1,0)) = 1
    and habilitado = 1

    end 
  --) sql
  set @con_apellido_exacto = @@ROWCOUNT

  if (@p_taxid is null 
      and isnull(@busqueda_apellido,'---') != '---' 
      and isnull(@busqueda_nombre,'---') != '---'
   and @con_apellido_exacto = 0)
  begin
  insert into @resultado

  SELECT distinct top(@max_rows) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,sexo
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,cuerpo_clave,
  identificadores.country_code,
        identificadores.identificador as identificador,isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,prv_codigo,domicilios=STUFF(
         (SELECT distinct '; Domicilio:' + replace(domicilio,';',',') + 
      iif(isnull(codpos,'') != '', ' (CP ' +  convert(varchar,codpos) + ')','') +
      iif(isnull(loc_nombre,'') != '', ' - ' + loc_nombre,'') + 
      iif(isnull(prv_nombre,'') != '', ' - ' + prv_nombre,'') 
      AS [text()]
       FROM domicilios_todos DT
       WHERE DT.cuit = T.cuit and DT.visible = 1 and DT.nombre_fuente in ('AFIP','RENAPER')
         and (@p_provincia is null or DT.prv_codigo = @p_provincia)
      FOR XML PATH('')), 1, 2, '') 
    FROM domicilios_todos T where T.visible = 1 
    and T.nombre_fuente in ('AFIP','RENAPER') and (@p_provincia is null or T.prv_codigo = @p_provincia)

    GROUP BY cuit,prv_codigo) dd 
    
    on (dd.cuit = padrones.identificacion)

  WHERE @p_taxid is null 
    and isnull(@busqueda,'---') = '---' 
    and isnull(@busqueda_apellido,'---') != '---' 
    and isnull(@busqueda_nombre,'---') != '---' 
    and CONTAINS(padrones.apellidos,@busqueda_apellido)
    and CONTAINS(padrones.nombres,@busqueda_nombre)
    and tipo_persona = @p_tipo_persona

    and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo,1,0)) = 1 

    and iif(@p_provincia is null, 1, iif(country_code in ('AR') and dd.domicilios is not null,1,0)) = 1 
    --and iif(@p_edad_min is null and @p_edad_max is null, 1, 
    --    iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max,1,0)) = 1 
    and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, datos_personas.c02_fecha_nac, GETDATE()) / 365.25) between @p_edad_min and @p_edad_max 
          and datos_personas.fecha_defuncion is null 
          and isnull(datos_personas.defuncion,0) = 0,1,0)) = 1
    and habilitado = 1


  --) sql
end



  select distinct top(@max_rows) * from @resultado
END
GO