dbo.api_padrones_busqueda_avanzada_29012025

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_29012025] '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] 'J',null, 'SAGRIN SA'
[api_padrones_busqueda_avanzada_29012025] 'F','27208925658',null,null,null,null,1

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_29012025
@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, 
-- en el SP api_padrones se llama con el pais, por eso lo agregamos y también el parámtro llamada_desde_otro_sp
@country_code varchar(2) = NULL,
@llamada_desde_otro_sp char(1) = 'N'
AS
BEGIN
 SET NOCOUNT ON;

 -- 29/01/2025
 -- Si no viene pais, no importa el pais
 -- Si pide de Argentina, solo devuelve de argentina
 -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
 if (@country_code is not null and @country_code != 'AR') set @country_code = '' 
 --

 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),
  -- provincia y localidad se usaban en el SP api_padrones 
  provincia varchar(100), 
  localidad varchar(100)) 

 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 (@llamada_desde_otro_sp = 'N')
 begin
  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 
 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
  */


 --select @busqueda, @busqueda_apellido, @busqueda_nombre

 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 

 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, prv_nombre as provincia, loc_nombre as localidad
  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,iif(@p_provincia is null,'',prv_codigo) as 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)
   
   left join  domicilios_todos on domicilios_todos.cuit = padrones.identificacion and tipo_domicilio = 'F'  and domicilios_todos.visible = 1

   WHERE identificacion = @p_taxid 
     and habilitado = 1
     -- 29/01/2025
     -- Si no viene pais, no importa en pais
     -- Si pide de Argentina, solo devuelve de argentina
     -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
     and (iif (@country_code is null, 1, 
      iif(@country_code = 'AR' and identificadores.country_code = 'AR',1,
       iif(@country_code = '' and identificadores.country_code != 'AR',1,0)))) = 1
 end  

 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, prv_nombre as provincia, loc_nombre as localidad
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion
  
  left join  domicilios_todos on domicilios_todos.cuit = padrones.identificacion and tipo_domicilio = 'F'  and domicilios_todos.visible = 1

   left join (SELECT cuit,iif(@p_provincia is null,'',prv_codigo) as 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 CONTAINS(padrones.*,@busqueda)

   and iif(@p_tipo_persona is null, 1, iif(@p_tipo_persona = padrones.tipo_persona,1,0)) = 1 
   
   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
    -- 29/01/2025
    -- Si no viene pais, no importa en pais
    -- Si pide de Argentina, solo devuelve de argentina
    -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
    and (iif (@country_code is null, 1, 
        iif(@country_code = 'AR' and identificadores.country_code = 'AR',1,
         iif(@country_code = '' and identificadores.country_code != 'AR',1,0)))) = 1
  end   

  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, prv_nombre as provincia, loc_nombre as localidad
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador
  left join  domicilios_todos on domicilios_todos.cuit = padrones.identificacion and tipo_domicilio = 'F'  and domicilios_todos.visible = 1

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,iif(@p_provincia is null,'',prv_codigo) as 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 CONTAINS(padrones.apellidos,@busqueda_apellido_exacto)
    and padrones.apellidos = @p_apellido
     and iif(@p_tipo_persona is null, 1, iif(@p_tipo_persona = padrones.tipo_persona,1,0)) = 1 

    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
    -- 29/01/2025
    -- Si no viene pais, no importa en pais
    -- Si pide de Argentina, solo devuelve de argentina
    -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
    and (iif (@country_code is null, 1, 
        iif(@country_code = 'AR' and identificadores.country_code = 'AR',1,
         iif(@country_code = '' and identificadores.country_code != 'AR',1,0)))) = 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, prv_nombre as provincia, loc_nombre as localidad
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador
  left join  domicilios_todos on domicilios_todos.cuit = padrones.identificacion and tipo_domicilio = 'F'  and domicilios_todos.visible = 1

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,iif(@p_provincia is null,'',prv_codigo) as 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 CONTAINS(padrones.apellidos,@busqueda_apellido)
    and iif(@p_tipo_persona is null, 1, iif(@p_tipo_persona = padrones.tipo_persona,1,0)) = 1 

    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
    -- 29/01/2025
    -- Si no viene pais, no importa en pais
    -- Si pide de Argentina, solo devuelve de argentina
    -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
    and (iif (@country_code is null, 1, 
        iif(@country_code = 'AR' and identificadores.country_code = 'AR',1,
         iif(@country_code = '' and identificadores.country_code != 'AR',1,0)))) = 1
  
  end

  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, prv_nombre as provincia, loc_nombre as localidad
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador
  left join  domicilios_todos on domicilios_todos.cuit = padrones.identificacion and tipo_domicilio = 'F'  and domicilios_todos.visible = 1

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,iif(@p_provincia is null,'',prv_codigo) as 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_tipo_persona is null, 1, iif(@p_tipo_persona = padrones.tipo_persona,1,0)) = 1 

    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
    -- 29/01/2025
    -- Si no viene pais, no importa en pais
    -- Si pide de Argentina, solo devuelve de argentina
    -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
    and (iif (@country_code is null, 1, 
        iif(@country_code = 'AR' and identificadores.country_code = 'AR',1,
         iif(@country_code = '' and identificadores.country_code != 'AR',1,0)))) = 1

  end 

  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, prv_nombre as provincia, loc_nombre as localidad
  from dbo.padrones
  inner join identificadores on identificadores.identificador = padrones.identificador
  left join  domicilios_todos on domicilios_todos.cuit = padrones.identificacion and tipo_domicilio = 'F'  and domicilios_todos.visible = 1

  left join datos_personas on datos_personas.c01_cuit = padrones.identificacion

   left join (SELECT cuit,iif(@p_provincia is null,'',prv_codigo) as 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_tipo_persona is null, 1, iif(@p_tipo_persona = padrones.tipo_persona,1,0)) = 1 

    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
    -- 29/01/2025
    -- Si no viene pais, no importa en pais
    -- Si pide de Argentina, solo devuelve de argentina
    -- Si pode de otro pais (@country_code = ''), solo devuelve los que no son de argentina
    and (iif (@country_code is null, 1, 
        iif(@country_code = 'AR' and identificadores.country_code = 'AR',1,
         iif(@country_code = '' and identificadores.country_code != 'AR',1,0)))) = 1


  end

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