dbo.socios_enriquecer

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*

@accion = 'select_TODOS' --> si se manda como parametro @cliente, solo las contrapartes de ese cliente
si no se manda el @cliente, o @accion = 'select' --> toma todos los clientes de la tabla clientes_enriquecer_socios.
Toma todas las contrapartes del @cliente que son empresas de Argentina. 

@accion = 'cliente_familia' -> solo las contrapartes de esa familia

@accion = 'cuits'
Se usa para procesos de Screening, se manda @cliente y @p_fecha fecha del screening 

@accion = 'subcontratistas'
Se usa para los Subcontratistas de AMX que hay que controlar todos los meses 

Filtros:
De todas esas contraparte / o cuits
1) Busco la fecha de última actualización de la tabla socios.
   Si los socios fueron actualizados en los ultimos @dias dias -> NO SE MUESTRA
Si la fecha es vieja:
2) Busco fecha de último Boletin con cuit asignado 
   Si la fecha es posterior a la de socios -> SE MUESTRA 
   Si la fecha del boletín es mas vieja que la de socios o está vacía, busco fechas de homónimos,
   3) Busco fecha de último Boletín Homónimos
      Si no tienen homónimos más nuevos y es de las principales pcias. -> NO SE MUESTRA
  (siempre busco la fecha para poder mostrarla en el panel)
  si no es de las principales pcias y tiene Homonimos -> SE MUESTRA
  si no tiene Homonimos, y la fecha de Boletin es vieja o no tiene -> SE MUESTRA

select * from clientes where cli_codigo = 2000
select * from ln_familias where cliente = 2046807993 and nombre_familia = 'LISTAS'

exec [socios_enriquecer] 'cliente_familia',2046807993,150,null, 348

exec socios_enriquecer 'cuits','1152121066',null,'2025-01-15'

select cliente, cuit from procesos_cuit
 where procesos_cuit.cliente = 1152121066 and procesos_cuit.fecha = '2025-01-15'  and len(cuit) = 11


*/
CREATE OR ALTER PROCEDURE dbo.socios_enriquecer
@accion varchar(50) = 'select',
@cliente int = null, 
@dias int = 150, 
@p_fecha date = null,
@familia int = null,
@todo_filtrado char(1) = 'F' -- No se usa mas
AS


BEGIN
 SET NOCOUNT ON;

 declare @resultado table (id int IDENTITY(1,1), COD_CLIENTE int, CLIENTE varchar(500), CLI_CODIGO int,  CUIT varchar(20), 
         CONTRAPARTE varchar(500),
                           numero_contraparte int,
         country_code char(3), pais varchar(100), 
         nombre_familia varchar(200), provincia varchar(200), 
         ultima_actualizacion date, boletin_ultima_fecha date, boletin_provincia varchar(200),
         homonimos_ultima_fecha date,leyenda_homonimos varchar(50))

 DECLARE @ID int = NULL 
 DECLARE @COD_CLIENTE int = NULL 
 DECLARE @CLI_NOMBRE varchar(500) = NULL 
 DECLARE @CLI_CODIGO int = NULL 
 DECLARE @CUIT varchar(20) = NULL 
 DECLARE @CONTRAPARTE int = NULL 
 DECLARE @ID_PADRON INT = NULL 
 DECLARE @NOMBRE_PERSONA varchar(500)
 DECLARE @country_code char(3) = NULL 
 DECLARE @pais varchar(100) = NULL 
 DECLARE @nombre_familia varchar(200) = NULL 
 DECLARE @provincia varchar(200) = NULL 
 DECLARE @ultima_actualizacion date = NULL 
 DECLARE @boletin_ultima_fecha date = NULL 
 DECLARE @boletin_provincia varchar(200) = NULL 

 if (@dias is null) set @dias = 150

 if (@accion = 'fechas') begin

  select procesos_cuit.cliente, clientes.nombre_cliente, fecha, count(distinct procesos_cuit.cuit) as cantidad 
   from procesos_cuit 
   inner join padrones on (padrones.identificacion = procesos_cuit.cuit and padrones.tipo_persona = 'J' and padrones.habilitado = 1) 
   left join identificadores on identificadores.identificador = padrones.identificador 
   left join clientes on clientes.cliente = procesos_cuit.cliente
   where procesos_cuit.cliente = @cliente and fecha > getdate() - @dias
   and identificadores.country_code = 'AR'


  group by procesos_cuit.cliente, clientes.nombre_cliente, fecha
  order by procesos_cuit.cliente, fecha desc

  return

 end 

 if (@accion = 'familias') begin
  select ln_familias.familia, ln_familias.nombre_familia
   from ln_familias
   where cliente = @cliente and servicio_contratado = 47
  order by nombre_familia
  return
 end 



 if (@accion = 'clientes_enriquecer_socios_disponibles') begin
 
   select c.cliente, c.nombre_cliente, c.cli_codigo 
   from clientes_productos p
   inner join clientes c on c.cliente = p.cliente 
   where p.id_producto = 47 and p.cliente = c.cliente and p.activo = 1
   and c.estado_cliente = 'A'
   and not exists (select * from clientes_enriquecer_socios a where a.cliente = c.cliente)  
   order by nombre_cliente

   return

 end

 if (@accion = 'clientes_enriquecer_socios_asignados') begin
   select c.cliente, c.nombre_cliente, c.cli_codigo 
   from clientes_productos p
   inner join clientes c on c.cliente = p.cliente 
   where p.id_producto = 47 and p.cliente = c.cliente and p.activo = 1
   and c.estado_cliente = 'A'
   and exists (select * from clientes_enriquecer_socios a where a.cliente = c.cliente)  
   order by nombre_cliente

   return
 end

 if (@accion = 'agregar') begin

  insert into clientes_enriquecer_socios 
   select cliente
   from clientes  
   where cliente = @cliente
   and not exists (select * from clientes_enriquecer_socios where cliente = @cliente) 

  return

 end

 if (@accion = 'sacar') begin
  delete from clientes_enriquecer_socios 
   where cliente = @cliente
  
  return
 end
 
--- Aca arrancamos a buscar datos 

 declare @fecha date = dateadd(day,@dias*(-1),getdate())
 declare @clientes table (cliente int)
 declare @tabla table (cliente int, contraparte int, cuit varchar(20), id_padron int)
 declare @procesos_cuit table (cliente int, cuit varchar(20))

 if (@accion = 'select_TODOS' or @accion = 'select') begin
  if (@cliente is not null) 
  begin
   insert into @clientes values (@cliente)
  end 
  else
  begin
   insert into @clientes select cliente from clientes_enriquecer_socios
  end 

  insert into @tabla 
  select distinct ln_contrapartes.cliente, ln_contrapartes.contraparte, padrones.identificacion, ln_contrapartes.id_padron
  from ln_contrapartes 
  inner join padrones on (padrones.id_padron = ln_contrapartes.id_padron and padrones.tipo_persona = 'J') 
  where ln_contrapartes.cliente in (select cliente from @clientes) 
  and ln_contrapartes.activo = 1 
  and ln_contrapartes.subcontratista = 0 
  and ln_contrapartes.servicio_contratado = 47
  and ln_contrapartes.country_code = 'AR'
 end 

 if (@accion = 'cliente_familia') begin
  insert into @tabla 
  select distinct ln_contrapartes.cliente, ln_contrapartes.contraparte, padrones.identificacion, ln_contrapartes.id_padron
  from ln_contrapartes 
  inner join padrones on (padrones.id_padron = ln_contrapartes.id_padron and padrones.tipo_persona = 'J') 
  inner join ln_contraparte_familias on LN_contraparte_familias.contraparte = ln_contrapartes.contraparte
  where ln_contrapartes.cliente = @cliente 
  and ln_contrapartes.activo = 1 
  and ln_contrapartes.subcontratista = 0 
  and ln_contrapartes.country_code = 'AR'
  and ln_contrapartes.servicio_contratado = 47
  and ln_contraparte_familias.familia = @familia
  --and ln_contrapartes.contraparte = 227183
 end 

 
 if (@accion = 'subcontratistas') 
 begin
  insert into @procesos_cuit 
   exec [Subcontratistas_enriquecer_] 'PROCESAR', @cliente 
  delete from @procesos_cuit where len(cuit) != 11
 end 
 if (@accion = 'cuits') 
 begin
  insert into @procesos_cuit 
   select cliente, cuit from procesos_cuit
    where procesos_cuit.cliente = @cliente and procesos_cuit.fecha = @p_fecha  and len(cuit) = 11
 end

 if (@accion = 'subcontratistas' or @accion = 'cuits') 
 begin
  insert into @tabla
  select cliente, null, cuit, id_padron 
   from @procesos_cuit pc
   inner join padrones p on p.identificacion = pc.cuit  
                            and p.identificador in (select identificador from identificadores where country_code = 'AR')
          and p.habilitado = 1
          and len(pc.cuit) = 11 
 end 
 

 declare @fecha_actualizacion_socios date
 declare @fecha_actualizacion_boletin date 
 declare @fecha_actualizacion_homonimos_1 date 
 declare @fecha_actualizacion_homonimos_2 date 
 declare @nombre varchar(500) = null 
 declare @leyenda_homonimos varchar(50) = null 
 DECLARE Procesar CURSOR FOR
  select cliente, contraparte, cuit, id_padron from @tabla 


 OPEN Procesar
 Fetch next from Procesar into @COD_CLIENTE, @CONTRAPARTE, @CUIT, @ID_PADRON
 WHILE @@FETCH_STATUS = 0 
 BEGIN
  set @leyenda_homonimos = null 
  set @NOMBRE_PERSONA = null 
  select @NOMBRE_PERSONA = nombre_persona from padrones where id_padron = @id_padron 
  set @provincia = null
  set @provincia = (select top 1 prv_nombre 
       from domicilios_todos 
         where cuit = @cuit 
        and visible = 1 
        and domicilio not like '-[0-9][0-9],%'
        and domicilio not like 'RESIDE EN' 
        and domicilio not like 'DOMICILIADA EN%'
        and domicilio not like 'SIN DOMICILIO%'
        and (tipo_domicilio = 'L' or tipo_domicilio = 'F') and len(prv_nombre) > 2
         order by tipo_domicilio, created desc) 

  -- 1) Si los socios fueron actualizados en los ultimos @dias dias -> no se va a mostrar 
  set @fecha_actualizacion_socios = null
  set @fecha_actualizacion_socios = 
   (select top 1 tabla_socios.fecha from 
   (select iif(max(socios.created) is null and max(socios.modified) is null, MAX(convert(date,c04_fecha_act)), 
      iif(MAX(isnull(socios.created, '1900-01-01')) > MAX(isnull(socios.modified, '1900-01-01'))
      ,convert(date,MAX(socios.created)),
      convert(date,MAX(socios.modified)))) as fecha 
   from socios where socios.c01_cuit = @CUIT
   union 
   select fecha from ultima_revision_socios where cuit = @CUIT) tabla_socios
   order by fecha desc) 
  -- Si la fecha es posterior -> no se va a mostrar, y va al siguiente CUIT 
  if @fecha_actualizacion_socios is not null and @fecha_actualizacion_socios > getdate() - @dias
  begin
   Fetch next from Procesar into @COD_CLIENTE, @CONTRAPARTE, @CUIT, @ID_PADRON
   continue
  end
  -- sino, sigue los controles 

  -- 2) Busco fecha de último Boletin
  --    Si la fecha del boletín es mas vieja o está vacía, busco fechas de homonimos,
  set @fecha_actualizacion_boletin = null
  set @fecha_actualizacion_boletin = 
  (select MAX(fecha_publicacion) 
    from sociedades_constituciones
   where (documento_soc = @CUIT or cuit_int = @CUIT)
   ) 

  set @boletin_provincia = null
  set @boletin_provincia =
  (select top 1 IIF(provincias.prv_nombre is null, sociedades_constituciones.provincia_sociedad, provincias.prv_nombre)
    from sociedades_constituciones
      left join provincias on provincias.prv_codigo = provincia_boletin and pai_codigo_ifx = 54
   where (documento_soc = @CUIT or cuit_int = @CUIT)
         and fuente is not null 
      and fecha_publicacion = (select MAX(fecha_publicacion) 
            from sociedades_constituciones
           where (documento_soc = @CUIT or cuit_int = @CUIT)
           )
  ) 

  -- Si el boletin es posterior a los socios -> no se sacan, sigue despues del if de abajo que no se cumple
  
  if (@fecha_actualizacion_boletin is null or @fecha_actualizacion_boletin < isnull(@fecha_actualizacion_socios,'1900-01-01'))
  begin
   -- 3) Busco fecha de último Boletín Homónimos
   --    Si no tienen homónimos mas nuevos y es de las principales pcias. -> lo saco de la lista
   -- (siempre busco la fecha para poder mostrarla en el panel)
    set @fecha_actualizacion_homonimos_1 = null
    set @fecha_actualizacion_homonimos_2 = null
    select @nombre = [dbo].[Eliminar_omitidos_sinonimos](@NOMBRE_PERSONA)
    if (@nombre is not null)
    begin
     set @nombre = '"' + LTRIM(rtrim(@nombre)) + '"'
     set @fecha_actualizacion_homonimos_1 =
      (select MAX(fecha_publicacion) 
      from sociedades_constituciones  
      where cast(contenido_archivo as varchar(max)) != ''
      and (isnull(documento_soc,'') != '') 
      and (contains(razon_social,@nombre)) and sociedades_constituciones.visible = 1) 
     
     --Busqueda dentro del texto del edicto, tarda mas... si da time out sacarlo
     /*
      set @fecha_actualizacion_homonimos_2 =
      (select MAX(fecha_publicacion)
      from sociedades_constituciones  
      where (isnull(documento_soc,'') != '') 
        and (contains(contenido_archivo,@nombre)) and sociedades_constituciones.visible = 1) 
      */
    end
    
    --select @fecha_actualizacion_socios as socios,@fecha_actualizacion_boletin as boletin,@fecha_actualizacion_homonimos_1 as hominimos
    
    if isnull(@fecha_actualizacion_homonimos_2,'1901-01-01') > isnull(@fecha_actualizacion_homonimos_1,'1901-01-01')
    begin
     set @fecha_actualizacion_homonimos_1 = @fecha_actualizacion_homonimos_2
    end

    -- Si no tienen homónimos mas nuevos y es de las principales pcias. -> no se va a mostrar, y va al siguiente CUIT 
    if (@provincia in ('CIUDAD AUTONOMA BUENOS AIRES','BUENOS AIRES','CORDOBA','MENDOZA','SANTA FE'/*,'CATAMARCA','RIO NEGRO','SALTA'*/))
       and
    not(isnull(@fecha_actualizacion_homonimos_1,'1901-01-01') > isnull(@fecha_actualizacion_boletin,'1901-01-01'))
    begin
     Fetch next from Procesar into @COD_CLIENTE, @CONTRAPARTE, @CUIT, @ID_PADRON
     continue
    end

    -- Si son de otras provincias tendrian que ir a buscar edictos en los boletines... 
    if (@provincia not in ('CIUDAD AUTONOMA BUENOS AIRES','BUENOS AIRES','CORDOBA','MENDOZA','SANTA FE'/*,'CATAMARCA','RIO NEGRO','SALTA'*/)
       and (isnull(@fecha_actualizacion_homonimos_1,'1901-01-01') < isnull(@fecha_actualizacion_boletin,'1901-01-01') or 
            isnull(@fecha_actualizacion_homonimos_1,'1901-01-01') < isnull(@fecha_actualizacion_socios,'1901-01-01'))) 
    begin
     --set @leyenda_homonimos = 'Buscar Boletines'
     set @leyenda_homonimos = 'SI'
    end 

  end

  -- Si nada lo sacó de la lista, los guarda para mostrarlos 

  set @CLI_NOMBRE     = null
  set @CLI_CODIGO     = null
  set @pais           = null 
  set @country_code   = null 
  set @nombre_familia = null

  select @CLI_NOMBRE = nombre_cliente, @CLI_CODIGO = cli_codigo from clientes where cliente = @COD_CLIENTE
  
  if (@CONTRAPARTE is not null) 
  begin
   select @country_code = ln_contrapartes.country_code, @pais = country_name, @nombre_familia = nombre_familia
   from ln_contrapartes
   inner join ln_contraparte_familias on ln_contraparte_familias.contraparte = ln_contrapartes.contraparte
   inner join ln_familias on ln_familias.familia = ln_contraparte_familias.familia 
   inner join country on country.country_code = ln_contrapartes.country_code
   where ln_contrapartes.contraparte = @CONTRAPARTE
  end 
  
  if @country_code is null set @country_code = 'AR'
  if @pais is null set @pais = 'ARGENTINA'

  insert into @resultado (COD_CLIENTE,CLIENTE,CLI_CODIGO,CUIT, 
                             CONTRAPARTE,numero_contraparte,
        country_code,pais,
           nombre_familia,provincia,
        ultima_actualizacion,
        boletin_ultima_fecha,boletin_provincia,
        homonimos_ultima_fecha,leyenda_homonimos)

         values (
           @COD_CLIENTE,@CLI_NOMBRE,@CLI_CODIGO,@CUIT,
        @NOMBRE_PERSONA,@CONTRAPARTE,
        @country_code,@pais,
           @nombre_familia,@provincia,
           @fecha_actualizacion_socios,
        @fecha_actualizacion_boletin,@boletin_provincia,
        @fecha_actualizacion_homonimos_1,@leyenda_homonimos
          )
  
  Fetch next from Procesar into @COD_CLIENTE, @CONTRAPARTE, @CUIT, @ID_PADRON
 
 END 
 CLOSE Procesar
 DEALLOCATE Procesar

 SELECT * FROM @resultado
 order by contraparte
END 

GO