dbo.agb_UNICO_buscador_padron

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: MIS
-- Create date: 2025-01-03
-- Description: Este es el ÚNICO Stored que se tiene que usar para Búsquedas en Padrón
/* 
- Para la búsqueda libre, en los que en un solo campo se puede completar con tax-id o nombre, 
  se puede mandar en cualquira de estos 2 parátros: @p_taxid o @p_razon_social
  (Si se mandan sólo números separados con '.','-','/' o espacio, se los elimina)
- Para las búsquedas avanzadas, va a tener identificado el primer parámetro @p_tipo_persona (f o J), 
  y se puede pedir de alguna provincia en particular, con rango de edad, etc... 
- En el parámetro @p_todos_los_paises: Se puede pedir sólo de Argentina, del resto del mundo (menos argentina) o de todos los países: 
  1 = todos los paises, 0 = solo argentina, 2 = cualquier pais menos argentina 
- En el parámetro @max_rows, se puede indicar el máximo de registros a recibir, por default, 300 
  (no se recomienda poner como max_row un numero muy grande, porque va a dar time-out)
- Cuando se pide rango de edad en Personas Físicas, trae también a los que en la BD no tiene fecha de nacimiento
- Las validaciones en el busador abanzado, como x ej. que se mande siempre el tipo de persona, 
  se deben manejar desde el php, por eso lo sacamos de acá... 
*/
-- =============================================
/*
exec [UNICO_buscador_padron] 'F', null,null,'SARALEGUI FERNANDEZ','MANUEL','M',null,30,40,30
exec [UNICO_buscador_padron] null, '20.892.565',null,null,null,null,null,null,null,30
exec [UNICO_buscador_padron] null,null, NULL,'di maria',null,null,null,18,100,30
exec [UNICO_buscador_padron] NULL,null,'EL IDD'
exec [UNICO_buscador_padron] null,null,'burgol',null,null,null,null,null,null,300,1
exec [UNICO_buscador_padron] null,'ARCOS DORADOS',null,null,null,null,null,null,null,300,2
exec [UNICO_buscador_padron] null,'ARCOS DORADOS',null,null,null,null,null,null,null,300,0
exec [UNICO_buscador_padron] null,'ARCOS DORADOS',null,null,null,null,1,null,null,300,0

exec [UNICO_buscador_padron] null,'el idd',null,null,null,null,1,null,null,300,0


[busqueda_padron] '"ARCOS" near "DORADOS"',1
exec [UNICO_buscador_padron] null,'59144'

EXEC busqueda_padron_por_igualdad 'TOMAS BUSTOS', 1
EXEC busqueda_padron '"soria" NEAR "maria" NEAR "ines"', 1
EXEC [unico_buscador_padron] null,'TOMAS BUSTOS',null,null,null,null,null,null,null,300,1
EXEC [UNICO_buscador_padron] null,'soria maria ines',null,null,null,''
select * from padrones 
where CONTAINS(padrones.*,'"soria" near "maria" near "ines"') and habilitado = 1 and sexo is null

exec [UNICO_buscador_padron] 'J',null,'pop it',null,null,null,0

exec [UNICO_buscador_padron] null,null,'burgol'
exec [UNICO_buscador_padron] 'J','30708316985',null,null
exec [UNICO_buscador_padron] null,null,null,'soria','maria ines'
exec [UNICO_buscador_padron] null,null,null,'di tulio'
exec [UNICO_buscador_padron] 'J',null,'di tulio'
exec [UNICO_buscador_padron] 'J',null,'de luca'
exec [UNICO_buscador_padron] null,'27'
exec [UNICO_buscador_padron] 'F','florentina',null,null
exec [UNICO_buscador_padron] 'F',null,'burgol'
exec [UNICO_buscador_padron] 'F',null,'abrales juan esteban',null,null,null,null,60,100

*/
CREATE OR ALTER PROCEDURE dbo.agb_UNICO_buscador_padron
@p_tipo_persona char(1), -- F o J o null (mandamos null en el buscador libre, porque no sabemos el tipo de persona)
@p_taxid varchar(100),                 --| en el caso del buscador libre no sabemos si ese tax-id o nombre, lo podemos mandar en el 2do o 3er parámetro
@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 = 300, 
@p_todos_los_paises tinyint = 1 --> 1 = todos los paises, 0 = solo argentina, 2 = cualquier pais menos argentina 

AS
BEGIN
 SET NOCOUNT ON;
 declare @busqueda varchar(500) = '---'
 declare @busqueda_omitidos 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_1 varchar(1),
  tipo_persona varchar(20),
  sexo char(1),
  fecha_nacimiento date,
  fecha_defuncion date, 
  pre_clave varchar(50),
  cuerpo_clave varchar(50),
  suf_clave varchar(50),
  country_code varchar(50),
  identificador varchar(50), 
  nombre_identificador varchar(50), 
  ismodified datetime,
  apellidos varchar(500),
  nombres varchar(500), 
  domicilios varchar(max), 
  codigo_provincia varchar(10)) 

  -- Si recibe como parametro provincia, solo va a buscar en argentina
  if (@p_provincia is not null) 
  set @p_todos_los_paises = 0

 -- 
 if (isnull(@p_tipo_persona,'') not in ('F','J')) 
 begin
  set @p_tipo_persona = null 
 end
 
 -- En la búsqueda libre, no sabemos si lo que escribieron es tax-id o nombre.
 -- Lo guardamos como tax-id y después hacemos las validaciones si es númerico o no 
 -- (en el caso de tax-id con letras y números va a buscar por ese tax-id)
 if (isnull(@p_tipo_persona,'') = '' and isnull(@p_taxid,'') = '' and ISNULL(@p_razon_social,'') != '')
  set @p_taxid = @p_razon_social

 -- Si estamos mandado sólo números separados con '.','-','/' o espacio, se los elimina 
 select @p_taxid = ltrim(rtrim(@p_taxid))

 if (@p_taxid not like '%[a-z]%' and @p_taxid not like '%[A-Z]%' and @p_taxid like '%.%') 
 begin
  select @p_taxid =  REPLACE(@p_taxid, '.','')
 end 

 if (@p_taxid not like '%[a-z]%' and @p_taxid not like '%[A-Z]%' and @p_taxid like '%-%') 
 begin
  select @p_taxid =  REPLACE(@p_taxid, '-','')
 end 

 if (@p_taxid not like '%[a-z]%' and @p_taxid not like '%[A-Z]%' and @p_taxid like '% %') 
 begin
  select @p_taxid =  REPLACE(@p_taxid, ' ','')
 end
 
 if (@p_taxid not like '%[a-z]%' and @p_taxid not like '%[A-Z]%' and @p_taxid like '%/%') 
 begin
  select @p_taxid =  REPLACE(@p_taxid, '/','')
 end
 -- 

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

    /*SELECT @p_taxid, @p_razon_social,@p_nombre,@p_apellido*/


 /* Las validaciones en el busador abanzado, se deben manejar desde el php, por eso lo sacamos de acá... 
  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_razon_social,'') != '')
 begin
  select @busqueda = @p_razon_social 
 end

 IF (isnull(@p_taxid,'') != '')
 begin
  select @busqueda = @p_taxid 
 end
 
 /*IF (@p_tipo_persona = 'F')
  begin 
  IF (isnull(@p_apellido,'') != '' or isnull(@p_nombre,'') != '')
  begin
  select @busqueda = isnull(@p_apellido,'') + ' ' + isnull(@p_nombre,'')
  end
  end*/
 -- Si se manda apellido y/o nombre, entonces va a buscar sólo personas físicas
 IF (isnull(@p_apellido,'') != '' or isnull(@p_nombre,'') != '')
 begin
  set @p_tipo_persona = 'F'
  select @busqueda = isnull(@p_apellido,'') + ' ' + isnull(@p_nombre,'')
 end
 


 /*select @busqueda, @busqueda_apellido, @busqueda_nombre*/

 if (isnumeric(@busqueda) != 1 and @busqueda != '---')
 begin

  /* 20/02/2025: si hay varios espacios, los convierte a uno solo */ 
  while @busqueda like '%  %'
  begin
   select @busqueda = REPLACE(@busqueda, '  ',' ')
  end

  /* 20/02/2025: saco las palabras de menos de 2 letras */ 
  --SELECT @busqueda = STUFF((SELECT ' ' + item FROM dbo.DelimitedSplit8K(@busqueda, ' ') where len(item) > 2 FOR XML PATH ('')), 1, 1, '')
  -- 25/02/2025: lo sacamos porque cambiando el 'transform noise words' a 1, ya funciona por ejemplo BANCO DE GALICIA (con 'transform noise words' en 0, no traia nada, habia que buscar como BANCO GALICIA)
  
  /* 25/02/2025: Pero si eliminamos las palabras que tenemos en la tabla omitidos
   Por ejemplo si buscamos 'FIDELITAS SOCIEDAD ANONIMA' no va a traer nada pero al eliminar el 'SOCIEDAD ANONIMA' si
   */
  if isnull(@p_tipo_persona,'') != 'F'
  begin
   select @busqueda_omitidos = dbo.Eliminar_omitidos(@busqueda)
  end 
  /**/

  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 "') + '"' 
  
  if isnull(@p_tipo_persona,'') != 'F'
  begin
  if @busqueda_omitidos not like '% near %'
   select @busqueda_omitidos =  '"' + REPLACE(@busqueda_omitidos,' ','" near "') + '"' 
  end
 end 

 if (isnumeric(@busqueda) = 1 and @p_taxid is null )
 begin
  set @p_taxid = @busqueda 
 end
 if (isnumeric(@busqueda) = 0 and @p_taxid is not null )
 begin
  set @p_taxid = null  
 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 

 declare @max_rows_2 int = @max_rows * 1.2 --> buscamos un poco más, para que cuando elimine por distinct me queden max_rows

-- select @busqueda busqueda, @busqueda_omitidos busqueda_omitidos, @p_apellido apellido, @busqueda_apellido busqueda_apellido, @p_nombre nombre , @busqueda_nombre busqueda_nombre

 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_2) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,tipo_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,
  sexo = 
  case 
   when padrones.tipo_persona = 'J' then null 
   when padrones.tipo_persona = 'F' then 
    iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
     iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
       iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
   else null 
  end 
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,pre_clave,cuerpo_clave,suf_clave,
  identificadores.country_code,
        identificadores.abreviatura as identificador,
  identificadores.nombre_identificador as nombre_identificador,
  isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios, dd.prv_codigo
  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 */
     CONTAINS((padrones.identificacion,padrones.cuerpo_clave),@p_taxid) 
              -- CONTAINS((padrones.*),@p_taxid)  
              and habilitado = 1
        -- exec [agb_UNICO_buscador_padron] 'J','30708316985',null,null
 end  

 if (@p_taxid is null 
     and isnull(@busqueda,'---') != '---')
  begin 

  insert into @resultado

  SELECT distinct top(@max_rows_2) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,tipo_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,  
  sexo = 
  case 
   when padrones.tipo_persona = 'J' then null 
   when padrones.tipo_persona = 'F' then 
    iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
     iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
       iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
   else null 
  end 
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,pre_clave,cuerpo_clave,suf_clave,
  identificadores.country_code,
        identificadores.abreviatura as identificador,
  identificadores.nombre_identificador as nombre_identificador,
  isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios, dd.prv_codigo
  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 
   ( CONTAINS(padrones.*,@busqueda) OR CONTAINS(padrones.*,@busqueda_omitidos))
    and tipo_persona = iif(@p_tipo_persona is not null, @p_tipo_persona, tipo_persona) 
   
  --and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo or isnull(padrones.sexo,'P') in ('P','',' '),1,0)) = 1 
  
  and 
   (
   @p_genero is null
   or  
   (
   @p_genero is not null and padrones.tipo_persona = 'F' and
       iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
        iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
      = @p_genero
   )
  )
  


   and iif(@p_todos_los_paises = 1, 1, 
            iif(@p_todos_los_paises = 2 and identificadores.country_code not in ('AR'),1,   
            iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is not null and dd.domicilios is not null,1,
           iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is 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, isnull(datos_personas.c02_fecha_nac, DATEADD(YEAR,@p_edad_min*(-1),GETDATE())), 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   

  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_2) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,tipo_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,
  sexo = 
  case 
   when padrones.tipo_persona = 'J' then null 
   when padrones.tipo_persona = 'F' then 
    iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
     iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
       iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
   else null 
  end 
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,pre_clave,cuerpo_clave,suf_clave,
  identificadores.country_code,
        identificadores.abreviatura as identificador,
  identificadores.nombre_identificador as nombre_identificador,
  isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios, dd.prv_codigo
  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 
    CONTAINS(padrones.apellidos,@busqueda_apellido_exacto)
    and padrones.apellidos = @p_apellido
    and tipo_persona = iif(@p_tipo_persona is not null, @p_tipo_persona, tipo_persona) 
    
  --and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo or isnull(padrones.sexo,'P') in ('P','',' '),1,0)) = 1 
   
  and 
   (
   @p_genero is null
   or  
   (
   @p_genero is not null and padrones.tipo_persona = 'F' and
       iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
        iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
      = @p_genero
   )
  )
  


   and iif(@p_todos_los_paises = 1, 1, 
            iif(@p_todos_los_paises = 2 and identificadores.country_code not in ('AR'),1,   
            iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is not null and dd.domicilios is not null,1,
           iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is null,1,0)))) = 1 
       
  and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, isnull(datos_personas.c02_fecha_nac, DATEADD(YEAR,@p_edad_min*(-1),GETDATE())), 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_2) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,tipo_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,
  sexo = 
  case 
   when padrones.tipo_persona = 'J' then null 
   when padrones.tipo_persona = 'F' then 
    iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
     iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
   else null 
  end 
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,pre_clave,cuerpo_clave,suf_clave,
  identificadores.country_code,
        identificadores.abreviatura as identificador,
  identificadores.nombre_identificador as nombre_identificador,
  isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios, dd.prv_codigo
  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 CONTAINS(padrones.apellidos,@busqueda_apellido)
    and tipo_persona = iif(@p_tipo_persona is not null, @p_tipo_persona, tipo_persona) 
    
  --and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo or isnull(padrones.sexo,'P') in ('P','',' '),1,0)) = 1 
   
  and 
   (
   @p_genero is null
   or  
   (
   @p_genero is not null and padrones.tipo_persona = 'F' and
       iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
        iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
      = @p_genero
   )
  )
   


   and iif(@p_todos_los_paises = 1, 1, 
            iif(@p_todos_los_paises = 2 and identificadores.country_code not in ('AR'),1,   
            iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is not null and dd.domicilios is not null,1,
           iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is null,1,0)))) = 1  

   and iif(@p_edad_min is null and @p_edad_max is null, 1, 
        iif((DATEDIFF(DAY, isnull(datos_personas.c02_fecha_nac, DATEADD(YEAR,@p_edad_min*(-1),GETDATE())), 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
  
  if (@p_taxid is null 
      and isnull(@p_apellido,'') != '' 
      and isnull(@busqueda_nombre,'---') != '---')
  begin 
  insert into @resultado

  SELECT distinct top(@max_rows_2) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,tipo_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,
  sexo = 
  case 
   when padrones.tipo_persona = 'J' then null 
   when padrones.tipo_persona = 'F' then 
    iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
     iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
       iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
   else null 
  end 
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,pre_clave,cuerpo_clave,suf_clave,
  identificadores.country_code,
        identificadores.abreviatura as identificador,
  identificadores.nombre_identificador as nombre_identificador,
  isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios, dd.prv_codigo
  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 = iif(@p_tipo_persona is not null, @p_tipo_persona, tipo_persona) 

  --and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo or isnull(padrones.sexo,'P') in ('P','',' '),1,0)) = 1 
   
  and 
   (
   @p_genero is null
   or  
   (
   @p_genero is not null and padrones.tipo_persona = 'F' and
       iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
        iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
      = @p_genero
   )
  )
  

   and iif(@p_todos_los_paises = 1, 1, 
            iif(@p_todos_los_paises = 2 and identificadores.country_code not in ('AR'),1,   
            iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is not null and dd.domicilios is not null,1,
           iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is 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, isnull(datos_personas.c02_fecha_nac, DATEADD(YEAR,@p_edad_min*(-1),GETDATE())), 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_2) 'ok' as mensaje, 0 as error, PADRONES.id_padron, identificacion,nombre_persona,tipo_persona,iif(tipo_persona='F','FISICA','JURIDICA') AS tipo_persona,
  sexo = 
  case 
   when padrones.tipo_persona = 'J' then null 
   when padrones.tipo_persona = 'F' then 
    iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
     iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
   else null 
  end 
  ,c02_fecha_nac as fecha_nacimiento,fecha_defuncion,pre_clave,cuerpo_clave,suf_clave,
  identificadores.country_code,
        identificadores.abreviatura as identificador,
  identificadores.nombre_identificador as nombre_identificador,
  isnull(padrones.ismodified,'1901-01-01') as ismodified,
  padrones.apellidos,padrones.nombres, dd.domicilios, dd.prv_codigo
  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 = iif(@p_tipo_persona is not null, @p_tipo_persona, tipo_persona) 

  --and iif(@p_genero is null, 1, iif(@p_genero = padrones.sexo or isnull(padrones.sexo,'P') in ('P','',' '),1,0)) = 1 
  
  and 
   (
   @p_genero is null
   or  
   (
   @p_genero is not null and padrones.tipo_persona = 'F' and
       iif(isnull(padrones.sexo,'P') not in ('', ' ','P'), padrones.sexo,
        iif(identificadores.country_code = 'AR' and padrones.identificacion like '20%','M',
          iif(identificadores.country_code = 'AR' and padrones.identificacion like '27%','F',null))) 
      = @p_genero
   )
  )
  

   and iif(@p_todos_los_paises = 1, 1, 
            iif(@p_todos_los_paises = 2 and identificadores.country_code not in ('AR'),1,   
            iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is not null and dd.domicilios is not null,1,
           iif(@p_todos_los_paises = 0 and identificadores.country_code in ('AR') and @p_provincia is 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, isnull(datos_personas.c02_fecha_nac, DATEADD(YEAR,@p_edad_min*(-1),GETDATE())), 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


/*
-- 17/02/2025: 
-- Que solo muestre una provincia, cuando no se filtra por provincia
-- Se definio para personas fisicas se toma como valida la de Renaper, para Juridicas la de AFIP
-- (en el caso de Felix El Idd tiene domicilio en Prov Bs As para Renaper y CABA para AFIP)
*/
  declare @id_padron int, @cuit varchar(20), @cantidad int, @tipo_persona char(1), @provincia_elegida char(1)  
  declare wc_prov cursor for
     select id_padron, identificacion, tipo_persona, count(*) 
      from @resultado res
     group by id_padron,  identificacion, tipo_persona
     having count(*) > 1 
  open wc_prov
  Fetch next from wc_prov INTO @id_padron, @cuit, @tipo_persona, @cantidad
  WHILE @@fetch_status = 0
  BEGIN 
   set @provincia_elegida = null
   if @tipo_persona = 'F' 
   begin
    select @provincia_elegida = min(prv_codigo) from domicilios_todos where cuit = @cuit and nombre_fuente in ('RENAPER')
   end 
   else
   begin
    select @provincia_elegida = min(prv_codigo) from domicilios_todos where cuit = @cuit and nombre_fuente in ('AFIP')
   end 
   if @provincia_elegida is null
   begin
    select @provincia_elegida = min(codigo_provincia) from @resultado res where id_padron = @id_padron and codigo_provincia is not null 
   end 
   if @provincia_elegida is not null
   begin
    delete from @resultado where id_padron = @id_padron and codigo_provincia != @provincia_elegida 
   end 
   Fetch next from wc_prov INTO @id_padron, @cuit, @tipo_persona, @cantidad
  END 
  CLOSE wc_prov
  DEALLOCATE wc_prov 


  select distinct top(@max_rows) 
    res.id_padron,
    res.identificacion,
    res.nombre_persona,
    res.tipo_persona_1,
    res.tipo_persona,
    res.sexo, 
    res.fecha_nacimiento,
    res.fecha_defuncion,
    res.pre_clave,
    res.cuerpo_clave,
    res.suf_clave,
    res.country_code,
    res.identificador,
    res.nombre_identificador,
    res.ismodified,
    res.apellidos,
    res.nombres,
    res.domicilios,
    res.codigo_provincia,
    provincias.prv_nombre as provincia,
    (select MAX(periodo) from mis_aportes where cuit_empleador = res.identificacion or cuit = res.identificacion) as mis_aportes,
    (select MAX(convert(date,created)) from padron_constancia where id_padron = res.id_padron) as constancia_afip
    
  from @resultado res

  left join provincias on provincias.country_code= 'AR' and provincias.codigo_afip = res.codigo_provincia 
  /* si lo sacamos en cada uno de los insert, volver a poner esto
   where 
    (
   @p_genero is null
   or 
   (
   @p_genero is not null and res.tipo_persona_1 = 'F' and
   iif(isnull(res.sexo,'P') not in ('', ' ','P'), res.sexo,
   iif(res.country_code = 'AR' and res.identificacion like '20%','M',
    iif(res.country_code = 'AR' and res.identificacion like '27%','F',null))) 
   = @p_genero
   )
   )
  
   */


END

GO